python経由で MySQL, PostgreSQL のメモリが爆発するレベルの巨大なテーブルからデータを取得する際のメモ(一千万件以上のレコードがあるテーブルから select するようなケースを想定)
現象
fetchall() を使わずに fetchone() / fetchmany() を使っているのに out of memory 的なエラーでプロセスが落ちる。
この現象は python の MySQLdb ライブラリ、および psycopg2 ライブラリで確認した。なお MySQL の場合、import MySQLdb ではなく、公式の import mysql.connector の場合はこの現象は生じない(のでこちらを使うべき?)。
原因
デフォルトのカーソルがクライアントサイドカーソルになっている。なので cursor.execute(sql) した時点ですべてのデータをクライアントに持ってくるので out of memory が発生する(このケースでは fetchone, fetchmany は「一つ(複数)ずつデータを取得する」という挙動をエミュレートしているに過ぎない)。
対策
サーバサイドカーソルを使う。やり方は以下のとおり。
MySQLdb
まずは失敗するパターン。
import MySQLdb connection=MySQLdb.connect( host="host",user="user", passwd="passwd",db="db") cursor=connection.cursor() cursor.execute("select * from very_huge_table") # 失敗! row = cursor.fetchone() while row is not None: print row row = cursor.fetchone()
SSCursor を使えばうまくいく。
import MySQLdb import MySQLdb.cursors # 追加! connection=MySQLdb.connect( host="host",user="user", passwd="passwd",db="db", cursorclass = MySQLdb.cursors.SSCursor) # 追加! cursor=connection.cursor() cursor.execute("select * from very_huge_table") row = cursor.fetchone() while row is not None: print row row = cursor.fetchone()
psycopg2
psycopg2 の場合はカーソルオブジェクトを生成する際に name を指定するとサーバサイドカーソルになる、という記述があった。
参考URL : http://initd.org/psycopg/docs/connection.html#connection.cursor
If name is specified, the returned cursor will be a server side cursor (also known as named cursor). Otherwise it will be a regular client side cursor.
以下のコードは execute の時点でクライアントサイドにすべてのデータを引っ張ってこようとして失敗する。
import psycopg2 connection = psycopg2.connect("dbname=db host=host user=user") cursor=connection.cursor() cursor.execute("select * from very_huge_table") # 失敗! row = cursor.fetchone() while row is not None: print row row = cursor.fetchone()
named cursor を使えばOK。
import psycopg2 connection = psycopg2.connect("dbname=db host=host user=user") cursor=connection.cursor(name="thecursor") # 違いはここだけ。名前は PostgreSQL の非予約語ならなんでも cursor.execute("select * from very_huge_table") row = cursor.fetchone() while row is not None: print row row = cursor.fetchone()