巨大なテーブルから SELECT する際のメモ

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

【SQLite3】 ハイパフォーマンスなインデックスをつけるために覚えておくべきこと

(この記事は旧ブログからの転載です。)

「インデックスは付いているはずなのに検索速度が遅い!」

ということはありませんか?わたしは見事にハマりました。

私の場合、個人使いがほとんどでシビアな性能が要求される web アプリなどを作っているわけではありません。
そんなこともあって、あまり細かいことは気にせず

「データベース検索の際のキーとなるカラムには適当にインデックスをつけておけばいいよなー」

程度に考えて CREATE INDEX ~ をしていました。
ところが、インデックスのつけかたにはいくつかの抑えておくべき点があったのです。

ここではいろいろ実験してみてわかったことを書いてみようと思います。
“【SQLite3】 ハイパフォーマンスなインデックスをつけるために覚えておくべきこと”の続きを読む