すっごくニッチな要件とは思いますが、やってみたのでメモ。
TL;DR
- INSERT限定
-
skip-extended-insertでレコード毎のINSERT文を作る - awk で begin と commit を挟み込む
環境
- mysql5.6互換サービス
- 某クラウドのアレですが、mysql全般できると思う。
- 操作環境はlinux、bashなら多分どこでも行けるはず。
経緯
- 最近新しく開始したサービスでログテーブルのパーティションとインデックスの作り方を間違えた。
- 気づいたときには1.5億レコード。。。
- インデックスはオンラインでも変更できるけどパーティションはちょっとかなり無理
-
PERCONA toolkit のスクリプト使うとオンラインで修正行けるんじゃ....？
- パーティションの数がひどいことになっていて
alter table ... renameが固まる。- 5分かかるとかオンライン更新じゃないですねこれ
- パーティションの数がひどいことになっていて
- テーブル差し替えはメンテ入れるとして、データ移行は先に済ませておきたい。
- ptのSQLを参考にパーティションとかインデックス整えた別テーブル作成して、元テーブルからトリガーで流し込む
- 差分のデータは元テーブルからダンプして流し込む必要あり
- 途中でコケたりした場合にリトライしやすいように、id順かつ1レコードずつINSERTするようにした。
- O・SO・I！！
- トランザクションって偉大ですね ← 結論ここ
やった処理
mysqldumpの内容を加工して、mysqlコマンドに流し込むSQLを作成した。
遅かった処理
1行ずつ書き込むようにしたバージョン。
テーブル構成次第だけどmysqlに流し込む時に毎秒200レコード程度しか処理できない → 全行登録に75万秒(9日近く)かかる。
実際はボリュームあったのでid帯で分割したけど概ねこんなイメージ
DB=hoge
TABLE=foo
mysqldump --skip-extended-insert --order-by-primary $DB $TABLE --where='id < 123456789' \
| egrep '^INSERT' \
| perl -ple "s/\`$TABLE\`/\`__${TABLE}__\`/g" \ # 別名テーブルは __[table]__ の名前で作った
| gzip > table.dump
# 後で↓で流し込む
zcat table.dump | mysql
修正版
指定行数毎にトランザクションで囲うと一気に速度上がった。
リニアには伸びないけど、100件単位投げ込んで10倍程度の速さ。
DB=hoge
TABLE=foo
(
echo 'begin;'
(
mysqldump --skip-extended-insert --order-by-primary $DB $TABLE --where='id < 123456789' \
| egrep '^INSERT' \
| perl -ple "s/\`$TABLE\`/\`__${TABLE}__\`/g" \ # 別名テーブルは __[table]__ の名前で作った
) | awk '(NR%100==0){$0=$0"\ncommit;\nbegin;"}{print}' # 100行毎にcommitしてまたbeginで開始
echo 'commit;'
) | gzip > table.dump
# 後で↓で流し込む
zcat table.dump | mysql
これで75000秒(1日弱)まで縮まった。
注意事項
ログだからINSERTだけで処理できたのでまとめられたけど、UPDATEやDELETEみたく既存レコードいじると
トランザクション = デッドロックの温床になりかねないので、その場合はおとなしく1行毎とか
インデックス値毎にやりましょう。
おまけ
ダンプファイルを10ファイルずつ並行とか処理すると当然早いです。
最初の1レコード毎の処理でも速くなるけど、それでもDBの性能限界あったので(´・ω・｀)
awkとかまともに使ったことない、正規表現すらperl挟んでsed使わないこと多いけど、
この辺ちゃんと勉強した方が便利そうでした。