MySQLの巨大なテーブルにおけるDELETE
共同研究先で行なっているシステム開発において、MySQLの巨大なテーブルのデータを削除したり変更したりする必要が出ました(2019/06)。
その際行なった対応の備忘録です。
事の発端
システムを動かしているサーバのディスク使用率が90%を超えてきてMackerelがすげえうるさい。
てかディスク使用率90%は普通にやばくね？
共同研究先に許可を取って、ある程度過去のデータは吸い出して別のストレージに保存し、そのサーバからは削除することになりました。
よっしゃ吸い出しは終わった
あとはMySQL上でレコードをDELETEすればいいかなっと
delete from log where date < ~;ポチ。
3日が経過
DELETEがマジで終わらん
いくらなんでも時間かかりすぎじゃね？おかしくね？と気づく。
一応ローカルで削除の時間を計測し、サーバのレコード数を考慮して計算したら、1日もあれば終わる計算だったのでDELETEを実行したのだが・・・
罠①
MySQLで巨大なテーブルにDELETEを投げると著しい性能低下が発生する。らしい。
目安としてはレコードが100万件を超えるテーブルにDELETEを投げると、レコードが少ない時と比べて明らかに時間がかかる。らしい。
今回削除しようとしているテーブルに至っては、10億件以上レコードが存在する上に、削除するレコード数も膨大なのでそりゃもう。
そんでローカルはレコードが少なかったから早かったのか。
その間にディスク使用率は94%に
流石に焦り始める。RDBの特性上全て削除出来る・したことを確認してからコミットする気がするので、間に合わない気がしてきた。
てかDELETE処理がディスクを食っている気すらしてきた。
ちょっとこれはまずいので一旦まとめてdeleteしているタスクをキャンセルして、細かくdeleteしてディスク使用率をある程度下げようと企む。
delete from log where date between ~ and ~;ポチ。
お、ちょっと時間かかったけど無事終わった！
罠②
ディスク使用率が減らない・・・？
DELETEには成功している。
レコード数も確実に減っている。
なのにディスクの使用率が一向に減らない。猫の手を借りたい。
調べると
どうやらMySQLのお節介な機能で、一度取得した領域はDELETEしても再利用するために保持しておくらしい・・・
だからレコードをDELETEしようがディスク使用率は減らないのか・・・
何をすれば領域が空くかというと、OPTIMIZE TABLE もしくは ALTER TABLEをすれば良い
ただ先ほどのDELETEでの性能劣化と同じでおそらく巨大なテーブルにこんなことをするとまずいことになる可能性が高い上に、テーブルをロックすることになるので、出来れば避けたい
ということで
調べるとレコードを消すDELETEではだめだが、テーブルごとDROPすればいいよと書いてありました
こんなことを調べている間にディスク使用率は96%を超えている・・・
もう時間がありません、なりふり構ってられないです
やるしかない、からやるしかない
でもきちんと移行してからテーブルをDROPするべき・・・
と思ったけどよく考えたら移行用のテーブル作れる容量ねえじゃん
ということで必要なデータと全体のデータをそれぞれ別のサーバにdumpしておき、兎にも角にも容量を空けねば
最後の確認
必要なデータもある、ちゃんとバックアップも別のサーバに置いてある・・・
問題ないはず・・・
drop table log;ぽちっ
ついに
セーーーフ
なんとかディスク使用率を24%程度に減らすことができました
ディスク使用率が回復した時間を見ただけでも切羽詰まっていたことがわかると思います
新しいテーブルの作成
この時点で午前2時でしたが、新しいテーブルを作成してデータを挿入し、復旧しなければならない
先ほど取っておいた必要なデータをmysqldumpを使って挿入
mysqldumpをテーブルの定義ごとしているので、挿入するだけで従来のlogテーブルが作成され、データも挿入されます
やっと帰れたのが午前4時でした
最後に
ビッグデータを扱うときは現実的な問題が絡みがち
あとこんなギリギリになる前に対応をすべきでした
※追記
この記事を見た准教授の先生からパーティションを分けていたはずなのでそのパーティションをDROPすれば良かったんじゃないか、と助言をいただきました
本当にその通りです
今回のテーブルは月ごとにパーティションを分けていたので、過去のパーティションをDROPするのが一番早くて合理的です
気をつけましょう
