山田と申します。
現在自宅PC上のMySQL5.1にWikipediaのSQLダンプデータをインポートしようとしているのですが、
データが大量なせいかどうしても時間がかかってしまっています。
多少ウェブ上で調べた中から、ダンプデータのテーブル定義からuniqueを外したり、InnoDBをMyISAMに変えたりしたのですが思った程効果が実感できませんでした。
そこで、以下の点についてご存知の方がいらっしゃいましたら教えて頂けないでしょうか?
・MySQLのチューニングに関する情報
・大量データをインポートする際の注意点やコツ
・大量データのインポートに関するベンチマーク等のデータが見れるページ
よろしくお願いします。
No.5ベストアンサー
- 回答日時:
後考えられるのは一時的にマルチプルインサートの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
- 回答日時:
後は、LOAD DATA コマンド経由にすることでしょうか。
http://www.inter-office.co.jp/contents/194/
ダンプファイルからCSVファイルに変換するプログラム(何らかにスクリプト)が必要になりますが。
No.3
- 回答日時:
すみません
>mysql -u ユーザー名 -p < 編集したファイル
↓
>mysql -u ユーザー名 -p データベース名 < 編集したファイル
>mysql -u ユーザー名 -p < ins.dmp
↓
>mysql -u ユーザー名 -p データベース名 < ins.dmp
に変更してください。
No.2
- 回答日時:
データベース単位にダンプしたファイルをインポートする場合
>mysql -u ユーザー名 -p < ダンプファイル名
でのインポートは副次キーを含めてTABLE作成してからINSERTします。
そのため、副次キーが多い場合インデックスの作成に時間がかかる場合があります。
windows環境なら「sed」をダウンロードしてパスの通った場所にコピーします。
http://www.vector.co.jp/soft/win95/util/se015016 …
「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
- 回答日時:
ダンプデータはたいていバルクで処理されているので
それ以上速くなるのはほぼ期待できないような気がします。
一度に投入するデータ数を1000件程度にして、INSERT文をわけるとか
トライしてみたことありますが、あまり効果はなかったかも。
トランザクションも発生していないと思うのでコミットの
タイミングでの調整も利かないんじゃないかと
>コツ
基本的にはひたすら待つしかないと思います
あとはデータ投入時にデータ参照されないように工夫するなどは必要かと
ご返答ありがとうございます。
ダンプデータの場合、それ以上速くするのは難しそうですね…。
もう1度ディスクIO周り等基礎的な事を見直して試してみます。
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- PostgreSQL PostgressからMySQL(MariaDB)へ構造を変更する際のTimestamp等について 2 2023/04/04 12:09
- その他(スマートフォン・携帯電話・VR) PC版LINデータの移行は可能ですか? 2 2023/08/18 06:25
- ドライブ・ストレージ 古い外付けHDDから新品外付けHDDへのデータ移行方法 (Mac) 2 2022/12/11 02:01
- Google Drive Google Drive (G:)の容量を、購入した容量と一致させるにはどうすればよいでしょうか? 1 2022/04/10 22:32
- Access(アクセス) スキルシートのエクセルの項目に 2 2023/04/04 22:41
- MySQL データベースの複製の仕方(mysql) 2 2023/05/30 18:24
- フリーソフト 色々な形式の個人情報を後で参照しやすいようWindow10で管理したいのですが、どんな方法があるの? 1 2023/04/29 16:46
- Android(アンドロイド) Googleのファミリーリンクの危険性に気付いてしまったのですが、皆さんの感想を教えてください! 2 2023/05/09 10:01
- プロバイダー・ISP データ通信量の制限値が無制限のようなプロバイダはありますか? 3 2023/07/12 02:13
- その他(データベース) ファイルメーカーへネットワーク上のexcelデータを開く 1 2023/06/28 20:08
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
下記の問合せを行うクエリを、 ...
-
#1062 - '0' は索引 'PRIMARY' ...
-
下記の問合せを行うクエリを、 ...
-
同一のユーザー、同一商品のと...
-
PhpMyAdminで作成して実行せよ...
-
Mac です。ローカルホストの ph...
-
テーブル名が可変の場合のクエ...
-
書籍の内容はまともでしょうか?
-
SQLでカラムを追加し、条件に合...
-
私の考えていることは ・mySQL ...
-
うまくいきません教えてくださ...
-
クエリを教えてください select...
-
「都道府県の面積の大きい順に...
-
mysqlがインストールされている...
-
PHP 画像のアップロード Qiita
-
php テーブルを作れない
-
エラー 1068 (42000): 複数の主...
-
MySQLのテーブル作成でハイフン...
-
AWSのRDSを無料枠で使用してお...
-
テーブル所有者、スキーマ所有...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
#1062 - '0' は索引 'PRIMARY' ...
-
テーブル名が可変の場合のクエ...
-
「最高気温が35度以上の日を猛...
-
SQLでカラムを追加し、条件に合...
-
mysqlのupdate構文についての質...
-
htmlコードで書かれた表にphpで...
-
php テーブルを作れない
-
SQLです。こんな感じですか?あ...
-
「第一回模試の3科目の各得点...
-
データベースの接続に失敗して...
-
select *, `人口(男)`AND`人口(...
-
次の時間帯の勝率の合計を求め...
-
下記の問合せを行うクエリを、P...
-
うまくいきません教えてくださ...
-
SQLです。下記の問合せを行うク...
-
エラー 1068 (42000): 複数の主...
-
東京23区を、皇居を中心とした...
-
MySQL NULLだけをカウントして...
-
データベースの複製の仕方(mysql)
-
MySQLのエラーメッセージ(エラ...
おすすめ情報