【mysql】大容量のCSVファイルをload data infileで読み込めなかった時の対処法

mysqlで、load data infileを使いデータを読み込もうとしたがぜんぜん完了しない。しまいには、エラーが出た。

エラー内容
———————————————————-
ERROR 2013 (HY000): Lost connection to MySQL server during query
———————————————————-

4~6時間くらいか放っておいたら、切断がされたらしい。

実行環境
———————————————————-
Windows7にインストールしたxamppのmysql
———————————————————-

読み込んだCSVファイル
———————————————————-
”,’だいこん[改行]
きゅうり[改行]
たくあん[改行]
レンコン[改行]
たまねぎ[改行]・・・・・’ (←ここまで一件)
(こんなデータが1万2000件)
———————————————————-
2列でできたレコードが1万2000件ほど続くCSVファイル。行数にして8000万行以上(670MB)。
※1列目の空文字はプライマリキーなので読み込んだ後は一意の数字が入る。
※2列目は四文字くらいの単語の後に改行が入る。改行を数千回繰り返したものがひとつのレコード

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

所要時間およそ1分で読み込み完了。

/////////////////////////////////////////////////////////////
追記:
ただひとつのレコードに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)
———————————————————-

タイトルとURLをコピーしました