MySQL
シェル
mysqldump

ダンプしたレコードを特定の件数毎にトランザクションで囲って処理する

More than 1 year has passed since last update.

すっごくニッチな要件とは思いますが、やってみたのでメモ。

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使わないこと多いけど、
この辺ちゃんと勉強した方が便利そうでした。