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）
———————————————————-
