この記事について
| 環境 | Windows7、xampp(mysql/InnoDB)、670MBのCSV |
|---|---|
| 目的 | 8000万行(670MB)のCSVをload data infileで読み込む |
| 結果 | my.iniのinnodbバッファ関連(buffer_pool_size等)を増やしたら約1分で読み込めた |
つまずきポイント要約
- 放置するとERROR 2013 (Lost connection)。エラーログにはUnable to allocate memory
- 改行コードがLF+CRだと極端に遅くなったりエラーになる。LFにする
- 1レコードに8000万の改行を含めるとOut of memoryが出て未解決(保留)
事象
mysqlで、load data infileを使いデータを読み込もうとしたがぜんぜん完了しない。しまいには、エラーが出た。
エラー内容
ERROR 2013 (HY000): Lost connection to MySQL server during query４～６時間くらいか放っておいたら、切断がされたらしい。
実行環境
Windows7にインストールしたxamppのmysql読み込んだCSVファイル
'','だいこん[改行]
きゅうり[改行]
たくあん[改行]
レンコン[改行]
たまねぎ[改行]・・・・・' （←ここまで一件）
(こんなデータが1万2000件)２列でできたレコードが1万2000件ほど続くCSVファイル。行数にして8000万行以上（670MB）。
※１列目の空文字はプライマリキーなので読み込んだ後は一意の数字が入る。
※２列目は四文字くらいの単語の後に改行が入る。改行を数千回繰り返したものがひとつのレコード
xamppのコントロールパネルからエラーログを見ると、
2014-02-04 05:12:30 3748 [Note] c:\xampp\mysql\bin\mysqld.exe: ready for connections.
Version: '5.6.14' socket: '' port: 3306 MySQL Community Server (GPL) ←実行開始時間
2014-02-04 11:19:23 3748 [ERROR] InnoDB: InnoDB: Unable to allocate memory of size 702845016. ← エラー発生時間原因
「メモリが割り当てられない」みたいなことを言っている。
調べると、mysqlに使用するメモリは次の項目で設定できるらしい。
my.ini（旧my.cnf）の中身から抜粋（初期値）
## You can set .._buffer_pool_size up to 50 - 80 %
## of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
## Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50対処
青字がメモリに関する箇所。
そこで次のように変更を加える。
innodb_buffer_pool_size = 1024M
innodb_additional_mem_pool_size = 256M
innodb_log_file_size = 512M
innodb_log_buffer_size = 768M
mysqlをリブートして再び「load data infile」を実行。
するとできた。
Query OK, 11827 rows affected, 11828 warnings (1 min 5.00 sec)所要時間およそ１分で読み込み完了。
追記
ただひとつのレコードに8000万の改行を含むデータを入れると別のエラーが出た。
ERROR 5 (HY000): Out of memory (Needed 308285964 bytes)これについてはググっても設定ファイルいじっても解決せず。とりあえず保留。
ちなみに行数を減らしたら次のような結果になった。
設定変更前
300万行 44秒（改行LF + CRでエラー）
5秒 （LF 正常）↓↓↓
設定変更後
300万行 2.5秒設定変更前
1000万行 読み込めずエラー↓↓↓
設定変更後
1000万行 7分（改行LF + CRでエラー）
1000万行 8秒（改行LF）