MySQLの大量データインポートを速くするには

質問

山田と申します。
現在自宅PC上のMySQL5.1にWikipediaのSQLダンプデータをインポートしようとしているのですが、
データが大量なせいかどうしても時間がかかってしまっています。
多少ウェブ上で調べた中から、ダンプデータのテーブル定義からuniqueを外したり、InnoDBをMyISAMに変えたりしたのですが思った程効果が実感できませんでした。
そこで、以下の点についてご存知の方がいらっしゃいましたら教えて頂けないでしょうか？

・MySQLのチューニングに関する情報
・大量データをインポートする際の注意点やコツ
・大量データのインポートに関するベンチマーク等のデータが見れるページ

よろしくお願いします。

回答No.5

nora1962 · ベストアンサー

後考えられるのは一時的にマルチプルインサートのINSERT行数を増やすことです。
そのため、「max_allowed_packet」グローバル変数を一時的に変更しなければいけないのと、ダンプファイルのINSERT文を編集する必要があります。
以下はダンプファイルを編集するperlスクリプトの例です。ここではINSERT文の最大サイズを128MBにしています。
ただし、メモリに空きがないのにあまり大きなサイズを指定してもかえってスラッシング起こしてしまうのでマシンの状況を見て値を調整することが必要です。

printf("SET GLOBAL max_allowed_packet = 134217728;\n");
$size = 0;
while (<>){
chomp;
if( $_ =~ /INSERT/ ){
$_ =~ /(^[^;]+);/;
$buf = $1;
while(<>){
if( $_ =~ /INSERT/ ){
chomp;
$_ = /^INSERT INTO [^ ]* VALUES([^;]*);/;
$line = $1;

if( ( $size + length($line) ) > 134217728 ){
print $buf,";\n";
$buf = $line;
$size = length($line);
}else{
$buf = $buf . "," . $line;
$size = $size + length($line);
}
}else{
print $buf,";\n";
$buf = "";
$size = 0;
last;
}
}
}
print $_,"\n";
}
print "SET GLOBAL max_allowed_packet = 8388608;";

お礼

nora1962様

返信遅くなり大変失礼致しました。

とても丁寧なご返答ありがとうございます。
いろいろと打てる手はありそうですね。
データ加工用のソースコードまで書いて頂いて、とても参考になります。
LOAD DATAを使う場合はCSVへの変換の時間も加味して考えます。
マルチプルインサートのINSERT行数を増やす方法かLOAD DATAを使う方法で
やってみたいと思います。

どうもありがとうございました。

回答No.4

nora1962

後は、LOAD DATA コマンド経由にすることでしょうか。
http://www.inter-office.co.jp/contents/194/
ダンプファイルからCSVファイルに変換するプログラム（何らかにスクリプト）が必要になりますが。

回答No.3

nora1962

すみません
>mysql -u ユーザー名 -p < 編集したファイル
↓
>mysql -u ユーザー名 -p データベース名 < 編集したファイル
>mysql -u ユーザー名 -p < ins.dmp
↓
>mysql -u ユーザー名 -p データベース名 < ins.dmp
に変更してください。

回答No.2

nora1962

データベース単位にダンプしたファイルをインポートする場合
>mysql -u ユーザー名 -p < ダンプファイル名
でのインポートは副次キーを含めてTABLE作成してからINSERTします。
そのため、副次キーが多い場合インデックスの作成に時間がかかる場合があります。

windows環境なら「sed」をダウンロードしてパスの通った場所にコピーします。
http://www.vector.co.jp/soft/win95/util/se015016.html
「sed」コマンドについては
http://www.ishilab.net/~ishihara/tips/sed.txt
を参照。

コマンドプロンプトを起動して
>sed "/CREATE TABLE/,/^)/p" ダンプファイル名 > teigi.dmp
とすると「CREATE TABLE」コマンド部分だけ抽出出来ます。
このファイルを編集して、副次キーの記述をカットします。
そして
>mysql -u ユーザー名 -p < 編集したファイル
であらかじめTABLEを作成します（副次キーは定義されていません）。
>sed "/DROP TABLE/,/^)/d" ダンプファイル名 > ins.dmp
で
「DROP TABLE IF EXISTS」から「CREATE TABLE」コマンドまでを削除し、「INSERT」コマンドのみを抽出します。
>mysql -u ユーザー名 -p < ins.dmp
でデータを流しこんでからmysqlコマンドで該当のデータベースにログインし、
mysql>set sort_buffer_sie=67108864；
作業セッションのソートエリアサイズを拡大する（上記では64MB、メモリ状況に応じて適当に加減する）。
その後、保存した定義ファイルに基づき
ALTER TABLE テーブル名 ADD INDEX [インデックス名] 列名;
又は
ALTER TABLE テーブル名 ADD INDEX [インデックス名] (列名,列名);
で順次INDEXを作成していく。

ま、参考までに。

回答No.1

yambejp

ダンプデータはたいていバルクで処理されているので
それ以上速くなるのはほぼ期待できないような気がします。
一度に投入するデータ数を1000件程度にして、INSERT文をわけるとか
トライしてみたことありますが、あまり効果はなかったかも。

トランザクションも発生していないと思うのでコミットの
タイミングでの調整も利かないんじゃないかと

＞コツ

基本的にはひたすら待つしかないと思います
あとはデータ投入時にデータ参照されないように工夫するなどは必要かと

お礼

ご返答ありがとうございます。
ダンプデータの場合、それ以上速くするのは難しそうですね…。
もう１度ディスクIO周り等基礎的な事を見直して試してみます。

ありがとうございました。