MySQLで3億レコード物理削除した話

はじめに

こんにちは。webエンジニア社会人をしている ningenMe です。
タイトル通り。MySQLで3億レコード物理削除した話。
ちょっとハマったので備忘録。

はじまりはアラート

はじまりはアラートだった。
僕が運用・保守しているバッチサーバでは、mysqlからちょうど直近1ヶ月分のデータを毎日1回selectする定期処理をしている。

いつもなら1時間程度で終わる処理のはずが、その日は7,8時間経っても終わらずアラートが鳴り止まない.....。

原因追求

とりあえずリトライしたり、ログ見たりしたもののあんまり悪いところがなかった。
クエリもちゃんとindex効いてる。なんでだろうと思ったらDBの容量が結構大きくなっていたことに気づいた。

hoge_table | 350'000'000 |

3億5千レコード。インデックスちゃんと効いてたので多分普通に遅いだけっぽい。

必要なデータ取得は1ヶ月分である12'000'000件ほど。このselectクエリが時間かかってトランザクションが長く失敗しているようだった。

DB

基本的に毎日400'000件ぐらいinsertされてレコードが増えているこのテーブル。
データ取得は要件的には直近1ヶ月分だけあれば良いので、それだけ持てばよいのだが生憎rotate処理は入っていなかった。

自分が開発したものではなく引き継いだものだったのだが、その時点では何も言われていなかったので技術的負債のパスをもらってしまった感じである。

日々のinsertでデータが大きくなり、ついに限界を迎えたときに僕のターンだったというわけだ。

これだけ大きくなるならパーティション切っててほしかったものの、それも残念ながらなかった。

改修

一旦ロジックに手を加えるよりはDB軽くして様子見るほうが工数少ないだろうという方針になった。
3億レコード消せばかなり変わるだろう、ということでdeleteを打つことに......。このときはうまく行くと思っていた......。

対応その1

バックアップもしっかりとって、意気揚々とクエリを打ち始めた。

「クエリ打ちまーす」

DELETE FROM hoge_table WHERE create_time <= 'YYYY-MM-DD HH:MM:SS';

「...」

「...」

なんか反応がない、やっぱ時間かかるかあとか思いつつdbのgrafana眺めてたらdisc busyがめちゃめちゃ上がっていた。

「yabeeeeeeeee」

とりあえずクエリを止めた。

対応その2

一回で削除するには量が多すぎたことを反省した。dbのレプリケーション時間も考慮してこまめに削除することが良さそう。
1'000'000件ずつぐらい削除できるスクリプト書いて処理することにした。

「実行しまーす」

これでうまく行くと思っていた......。

対応その3

対応その2はどこへ......。処理はうまくいっていたのだが、時間がとてもかかることが判明した。
負荷をかけずに丁寧にやると約2週間必要だった。が、それではサービス要件を満たせず、やむなく中断。

そして対応その3である。

テーブルcopyしてrename

なんかdeleteが負荷大きくて良くないのでは。と思いinsertで欲しいテーブルを無から作ろうという方針に切り替えた。

| hoge_table     | 350'000'000|
| tmp_hoge_table |  50'000'000|

レコード数的には上記のような状態になればよいので、insertするほうがデータ処理も1/7程度になって速いはずである。

テーブル作ったあとはrenameしてマスターテーブルとして使い、今の3億レコードテーブルはdropすればokなはず。
truncateやdropはdeleteと違い負荷が少ないのは知っていたので、この方針に。

実行

「クエリ打ちまーす」

INSERT INTO tmp_hoge_table SELECT FROM hoge_table create_time > 'YYYY-MM-DD HH:MM:SS';

「...」

「...」

「お...？」

対応その4

うまく行くかと思ったが、insert終わったあとに無限にエラー吐かれた。mysql怖い。
もう本番作業したくねえよ〜って気持ちでいっぱいだった。

まあよく考えると、多分これも1回でinsertしすぎということなんだろう。
とりあえず1日分だけinsertしてみたらうまく行った。

「おお！」

このあとは丁寧丁寧に1日分ずつinsert。1ヶ月ぶんあればいいので35回日分を対応。

テーブルrename

ここはすんなりいった。めでたし。

アラート、止まる

バッチ処理の速度も回復した。
今まで1時間かかってたのに2分ぐらいで終わるようになった(は？)

問題も解決したのを確認できたので、3億レコードテーブルはdropした。
drop tableに爽快感を覚えるのは初めてだった。

まとめ。

他人のバッチ処理のローテート処理忘れでこんなに苦しむとは、って思った機会でした。
些細な設計の粗さが運用として現れて工数を奪うんだなあ......。

DBにくわしく

あなたはdeleteするときレプリケーション負荷など気にしてますか？
mysqlに優しくしましょう。

まあ今回の内容とか知ってる人は全然困るところじゃないとは思うんですが。
知識がないと難しいねーって。

cassandraでdelete多くて死んだパターンも昔経験したので何か近いものがあるのかなあとか思ったり。

また勉強したら記事にでもまとめます。

さいごに

※軽い感じに見えるけど実際はめちゃめちゃ慎重にチームでダブルチェックしながら作業進めてたよ。本番dbって怖いよね。

ではでは。