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()