作者別: flyio

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