MySQLで3億件のレコードを物理的に削除するストーリー

前書き

ねえ。私はウェブ開発者のningenMeです。



タイトルが示すように、私の話はMySQLで3億件のレコードを物理的に削除することについてです。



これに興味を持ったので、メモ（指導）をすることにしました。

開始-アラート

私が使用および保守しているバッチサーバーには、MySQLから先月のデータを1日1回収集する定期的なプロセスがあります。



通常、このプロセスは約1時間で完了しますが、今回は7時間または8時間完了せず、アラートの出力が停止することはありませんでした...

理由を探す

プロセスを再開してログを確認しようとしましたが、ひどいものは何も見つかりませんでした。

リクエストは正しくインデックス付けされました。しかし、何が問題なのか疑問に思ったとき、データベースのサイズが非常に大きいことに気付きました。

hoge_table | 350'000'000 |

3億5000万レコード。インデックス作成は正しく機能しているようですが、非常に遅いです。



1か月に必要なデータ収集は約12,000,000レコードでした。selectコマンドに時間がかかり、トランザクションが長時間実行されなかったようです。

DB

基本的には、毎日約40万レコードずつ増えるテーブルです。データベースは先月のみデータを収集することになっていたため、正確にこの量のデータに耐えられるという事実に基づいて計算されましたが、残念ながら、ローテーション操作は含まれていませんでした。



このデータベースは私が開発したものではありません。別の開発者から引き継いだので、技術的な借金のように感じました。



毎日挿入されるデータ量が多くなり、ようやく限界に達した瞬間が訪れました。このような大量のデータを扱う場合は、それらを分離する必要があると想定されますが、残念ながら、これは実行されませんでした。



そして、私は足を踏み入れました。

補正

ロジック自体を変更するよりも、データベース自体を減らして処理にかかる時間を短縮する方が合理的でした。



3億枚のレコードが消去されれば状況は大きく変わるはずなので、そうすることにしました…えー、間違いなくうまくいくと思いました。

ステップ1

信頼できるバックアップを準備したので、ようやくリクエストを送信し始めました。



「リクエストの送信」

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

「...」



「...」



「うーん…無回答。たぶん、プロセスに時間がかかっているのでしょうか？」-思ったのですが、グラファナを見て、ディスクの負荷が急激に増加しているのを見た場合に備えて。

「危険」-もう一度考えて、すぐにリクエストを停止しました。

ステップ2

すべてを分析した後、データの量が多すぎて一度にすべてを削除できないことに気付きました。



約1,000,000件のレコードを削除できるスクリプトを作成して実行することにしました。



「スクリプトを実装します」



「これで間違いなく動作します」と思いました

ステップ3

2番目の方法は機能しましたが、非常に時間がかかることがわかりました。

余分な神経を使わずにすべてをきちんと行うには、約2週間かかります。しかし、それでも、このシナリオはサービス要件を満たしていないため、私はそれから離れなければなりませんでした。



したがって、これが私がやることに決めたものです：

テーブルをコピーして名前を変更します

前のステップから、このような大量のデータを削除すると、同じように大きな負荷が発生することに気付きました。そのため、挿入を使用して新しいテーブルを最初から作成し、削除するデータをそのテーブルに移動することにしました。

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

新しいテーブルを上記と同じサイズにすると、処理速度も1/7速くなります。



テーブルを作成して名前を変更した後、マスターテーブルとして使用し始めました。3億レコードのテーブルをドロップすると、すべて問題ないはずです。

切り捨てまたは削除は削除よりもオーバーヘッドが少ないことがわかり、その方法を使用することにしました。

パフォーマンス

「リクエストの送信」

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

「...」

「...」

「ええと...？」

ステップ4

前のアイデアはうまくいくと思いましたが、挿入リクエストを送信した後、複数のエラーが表示されました。MySQLはスペアではありません。



私はすでにとても疲れていたので、もうこれをやりたくないと思い始めました。



私は座って考え、一度に挿入要求が多すぎることに気づきました...

データベースが1日に処理する必要のあるデータ量の挿入要求を送信しようとしました。起こりました！



その後も、同じ量のデータのリクエストを送信し続けます。毎月のデータを削除する必要があるため、この操作を約35回繰り返します。

テーブルの名前を変更する

ここで、運は私の側にありました：すべてが順調に進みました。

アラートが消えました

バッチ処理速度が向上しました。



以前は、このプロセスには約1時間かかりましたが、現在は約2分かかります。



すべての問題が解決したと確信した後、3億件のレコードを削除しました。スプレッドシートを削除して、生まれ変わったと感じました。

要約

バッチ処理では回転処理が見落とされており、それが主な問題であることに気づきました。このアーキテクチャ上のエラーは時間の無駄です。



データベースからレコードを削除することによるデータ複製の負荷について考えますか？MySQLに過負荷をかけないようにしましょう。



データベースに精通している人は、間違いなくそのような問題に直面することはありません。残りの部分については、この記事がお役に立てば幸いです。



読んでくれてありがとう！



この記事が気に入ったら、翻訳は明確でしたか、役に立ちましたか？