8000万レコードをInsertする

概要

・とある物件で8000万弱のレコードを新規のDBに効率的にぶち込む方法を考える必要に
・MySQL(Amazon RDS) + Python(mysql.connector)を想定
・mysql.connector + pythonについては拙文ながら(http://nekopuni.holy.jp/?p=927)に書いております。
・コミットの位置には気をつけよう + Multiple Insert最強ねというお話。

方法その1

まずは最初にやった方法。1レコードごとにInsertしてコミットしていく方法。
今までDB関連でやったコードはレコード量も大したことなかったので以下の方法でも特に問題なかった。

具体的な環境としてはテキストデータ(csv)を読み込み、それをDBにInsertしていくというもの。
csvの中身は日付(DATE)とデータ値(VALUE)がカンマ区切りになっているものであり、エッセンスだけ抜き出すと以下の様なコードを書いていました。

1000レコード弱を処理するのに33秒ほど。8000万行だと30日かかる計算に( д) ゚ ゚
これはイカンザキ

方法その2

コミットするのに時間がかかるということを風のうわさで聞いたので、コミットに時間がかかるのなら最後にコミットすれば良いじゃないというマリーアントワネット作戦

1000レコード弱を処理するのに8秒ほど。たっだ1行のインデントを変えただけで25%ほどの速度で処理できるように。
ただこれでも8000万レコードだと7.5日かかる計算。。

方法その3

どうやら世の中にはMultiple Insertというのがあるらしい。これで1000レコードごとに束ねてInsertすればさらに速度が向上するのでは
以下の例では2レコードのMultiple Insertですが、実際には1000レコード続けて書くようにして実施。

で、出てきたエラー(!)がこちら

なんでやねん!と思ったらAmazon RDSで借りられるデフォルトのDBの容量5GBでは足りていない模様。
ということで急遽100GBまで増額して再トライ。
1000レコードあたりの速度が0.18秒まで高速化!方法その2の3%ほどの速度で処理できるように。

ということで最終的に8000万レコードをぶっ込んだ際にかかった時間が以下

当初30日かかるという無謀な計画が、無事2時間で終えることができました。
最初どんだけ効率悪いコード書いてたんだって話ですねorzorz
Multiple Insert最強ね。

参考

インサート(insert)の処理方式別のパフォーマンスを検証 : 株式会社インターオフィス
http://www.inter-office.co.jp/contents/194/

mysql.connectorが使えるようになるまで

概要

・地味にハマったのでPythonからMySQLサーバー(Amazon RDS)にアクセスするまでの俺様メモ
・実際にpythonからselect文とinsert文を実行する
・Windows版Python 2.7.6を想定(macはもっと簡単でしたorz)

PythonのWindows版をインストール

Releases
http://www.python.org/download/releases/

適当にこの辺りからインストール

コマンドプロンプトでPythonが使用できるようにする

このままではコマンドプロンプトでpythonを使用することが出来ないので環境変数をいじくる

3. Using Python on Windows — Python v2.7.6 documentation
http://docs.python.org/2/using/windows.html

環境変数PATHに
C:\Python27
といったパスを追加.

Mysql.connectorのインストール

PythonからSQLを叩くライブラリは色々あるようですが無難にmysql.connectorを選択
ライブラリ自体は下記からダウンロード

2. mysql-connector-python 1.0.12 : Python Package Index
https://pypi.python.org/pypi/mysql-connector-python/

ダウンロードされたzipを解凍し,下記サイトに書かれているように,コマンドプロンプトからpython setup.py installでインストール

3.MySQL :: MySQL Connector/Python :: 4.3 Installing Connector/Python Source Distribution on Microsoft Windows
http://dev.mysql.com/doc/connector-python/en/connector-python-installation-source-windows.html

select文の実行

これで一応使える準備は整ったはずなので以下ようなのコードでテスト
サーバー情報,テーブル情報は架空のものとしています.
例えば,DATEとVALUEというカラムが存在するテーブル(TABLE)を読みに行くとしたらこんな感じになるかと思います.

以下のサイトも参考になると思います.

MySQL :: MySQL Connector/Python :: 5.4 Querying Data Using Connector/Python
http://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-select.html

insert文の実行

データベースに新たなレコードを追加するにはinsert文を実行するかと思いますが,その際には
コミットのための1行が追加となります.

ご参考には以下のページ

MySQL :: MySQL 5.5 Reference Manual :: 22.6.4.3 Inserting Data Using Connector/Python
http://dev.mysql.com/doc/refman/5.5/en/connector-python-example-cursor-transaction.html

ということでハマりましたというお話でした.