巨大なCSVをMySQLで読み込みたい

###前提・実現したいこと
1億行のCSVファイルを扱うために、MySQLを用いてデータベースで処理しようとしています。
1億行という大規模なファイルであるために、そのまま読み込もうとしても非常に時間がかかってしまい、良い方法を見つけるのに苦労しています。

###発生している問題・エラーメッセージ

読み込みスピードが遅く、現実的な時間では終わらない。

###試したこと
1億行のCSVファイル（hogehoge.csvとする）を、読み込んでテーブルに入れようとしています。
はじめは、テーブルを作成した後に以下のコードを試みました。

SQL
1LOAD DATA LOCAL INFILE "hogehoge.csv"
2INTO TABLE table FIELDS TERMINATED BY X'09'

しかし、丸一日経過すれど処理が進まなかったため、1億行のファイルを、10000行×10000ファイルに分割して順番に読み込むことを試みました。
hogehoge.csvを、splitを用いてシェル上で分解し、table.00000.csv ~ table.09999.csvという10000個のファイルに分割した後、以下のコードを実行しました。

mysqlimport -L -u root -p database table.*

しかし、これでも1つのcsvを読み込むのに45秒程かかっているため、仮にそのペースが最後まで続いても125時間はかかってしまいます。
このような巨大なCSVを高速に読み込む良い方法はないでしょうか。

LOAD DATA INFILEではなく、INSERTやMultiple INSERTと呼ばれる手法もあるようですが、1億行を処理するにあたってどちらが優れているのか判断しかねています。
Multiple INSERTを用いる場合は、MySQLとは別に、複数のレコードを同時に挿入するべく、SQLのスクリプトを作成する作業が必要だと考えているのですが、その認識は正しいでしょうか。

###参考にしたサイトなど
http://naoberry.com/tech/mysqldata/
https://qiita.com/saoyagi2/items/f37ae0d32edd06ec8c4c
http://nekopuni.holy.jp/2014/03/mysqlpython8000%E4%B8%87%E3%83%AC%E3%82%B3%E3%83%BC%E3%83%89%E3%82%92insert%E3%81%99%E3%82%8B/

hihijiji

2017/12/23 09:15

分割したファイルを、LOAD DATA INFILE で取り込んだ場合は如何でしょう？カラム数にもよりますが1万件10秒以上かかるなら何がボトルネックになっているか調査してみるのが良いかもしれません。

退会済みユーザー

2017/12/24 20:07

ご回答ありがとうございます。カラム数は25列です。mysqlimport -L -u root -p database table.* は、LOAD DATA INFILEで分割したファイルを取り込んでいるのと同じことをしていると認識していたのですが、これは誤っているのでしょうか。マシンのスペックが十分ではない可能性も感じております。

hihijiji

2017/12/25 01:10

ほぼ同じは全く一緒とは限りません。情報は多いに越したことはありません。マシンのスペックを上げることは大概有効ですが、ネックになっている所が判らないと無駄な投資になる可能性が上がります。

行動規範の内容に同意します

回答3件

MySQLのCSV Engineを利用するのはいかがでしょうか？
その他にもCSVをインポートするときのみ、下記のようなパフォーマンスチューニングを行ってからインポートすると期待できます。もちろん終わったら元に戻しておかなければなりません。

下記の他にも、ファイルI/Oを軽減するために、ファイルシステムをXFSパーティションにしたり、アトミック書き込みを利用したりする方法があります。

conf
1#
2# CSV大規模用
3#
4[mysqld]
5#↓↓コメントアウト↓↓
6#log-bin = mysql-bin
7  
8innodb_log_file_size           = 512M
9innodb_log_buffer_size         = 32M
10innodb_autoextend_increment    = 64
11innodb_flush_log_at_trx_commit = 2
12innodb_data_file_path          = ibdata1:10G:autoextend:max:100G
13skip_innodb_doublewrite

上記設定変更と、MySQLの再起動が完了したら、いちおう設定を確認しておきます。

sql
1SELECT
2  @@GLOBAL.log_bin,
3  @@GLOBAL.innodb_log_file_size,
4  @@GLOBAL.innodb_log_buffer_size,
5  @@GLOBAL.innodb_autoextend_increment,
6  @@GLOBAL.innodb_flush_log_at_trx_commit,
7  @@GLOBAL.innodb_data_file_path,
8  @@GLOBAL.innodb_doublewrite \G

そして、CSV Engineを使ってテーブルを作成します。プライマリーキーや、インデックスなどはALTER TABLEで後から作成します。

sql
1CREATE TABLE テーブル名 (テーブル定義) ENGINE CSV;

上記SQLコマンドを実行すると、/var/lib/mysql/データーベース名/テーブル名.CSVというファイルが出来上がるので、これをインポートしたいCSVで上書きします。

bash
1sudo cp -f CSVファイル.csv /var/lib/mysql/データーベース名/テーブル名.CSV

完了したら、下記ALTER TABLEコマンドでプライマリーキーとインデックスなどを作成します。

sql
1ALTER TABLE テーブル名 ADD PRIMARY KEY(カラム), ADD INDEX(カラム),,, ENGINE INNODB;

投稿2017/12/23 10:54

Tomak

総合スコア1652

こんな記事があります。
3. 適切なサイズでCOMMITする。

Tomakさんの紹介されている方法と同じで、なぜ高速なのかというと
「ALTER TABLE時には1万行ごとにCOMMITしてくれるので、Undoログを大量に作成せず、I/O性能が低下しない」ということです。

既にファイルを分割して試されているので、結果COMMIT分割していることになり、ファイルを分割している時間が大きいなら効果は得られますが、そうでないならあまり変わらない結果になると思われます。
※先ずは、プライマリーを含めインデックスをDROPしてからのLOAD DATAによる処理の時間計測を確認された方が良いと思いますけれど。

で、試してはいないのですが、現状の処理実行時にCPU使用率が80%以下であれば、ファイルを分割すると共にLOAD DATAによる処理を並列実行させてはどうでしょうか。
LOAD DATAもUndoログを消費するということはトランザクション処理を行っているということなので、
並列実行も可能じゃないかと思います。

幾つに分割するかはCPU使用率が90%位になるまでで、分割すれば良いかと思います。
※試算の125時間は、８分割出来れば15時間程度には短縮できることになります。

因みに、1万行で45秒ということなので、ハードスペックはそんなに良くないのでしょうね。

ハードは処理時間に大いに影響しますので、変更可能なら検討されてみてはどうですか。
１番は高速なストレージ、２番はCPU、３番目はメモリ（実行時に空きが少ないならですが）
というところでしょうか。

変更できないということであれば、リスクは高くなりますがOC（オーバークロック）してみるとか。

投稿2017/12/23 17:29

編集2017/12/23 18:04

sazi

総合スコア25430

具体的なデータがどのようなものかわかりませんが
データ投入は整合性チェックなどを考えればそれなりに時間がかかるものです
適当な単位（例えば1億行のデータであれば100~200万行単位くらい？）で
グルーピングができるならパーティショニングすれば
全体テーブルの整合性チェックから開放されるかもしれません。
いずれにしても１億行となるとRDBで普通にデータ管理するには負荷が
かかりすぎて現実的ではないかもしれません。
ビッグデータに強いと言われるNoSQLなど別のアプローチを検討したほうが
よいかもしれません