DB から大量のレコードを削除するときに気をつけること

こんにちは！Supership社 でバックエンドエンジニアをしています @Kotaroです。
私が所属しているプロダクト開発Gでは、隔週で勉強会を行なっています。
各メンバーがそれぞれ違った得意分野を持っているということもあり、それらを共有する場を作ろうというのが狙いです。

本エントリーは、その勉強会で私が発表した資料の再編になります。
大量のデータを持ったDB からデータを消すことになった背景から、対応方法（暫定）までを記載しています。

背景

私が開発を担当しているサービスの中に、以下のような特徴を持ったサービスがあり、DB には大量のデータが蓄積されています。

・Web サイト内でのユーザーの行動をトラッキング
・トラッキングデータを用いて分析

そんな中、「外部のサービスとDB を連携しよう！」という声があり、その前準備として、古い（もう使われていない）データを削除する 運びとなりました。

当初、何も気にせず「削除SQL 叩けばいいじゃん！」と思っていたのですが、開発メンバーと話をしていくうちに、 何も考えずに、削除SQL を叩いて大丈夫なのか？ という念にかられました。

そこで、以下の前提でデータを削除した場合、どんなことが起こり得るのか調査に乗り出しました。

・直近2年間より古いデータを削除
・削除するレコードを特定する際、インデックスを効かせられる

対象DB のスペック & 対象テーブルの状態

対象DB のスペックと、削除対象のテーブルの状態は以下の通りです。

なかなかのボリュームですw
毎時のI/O も多く、途切れることもほぼほぼないです。

大量のレコードを削除するとどんなことが起こり得る？

いくつかのケースに分けて、起こり得る事象をまとめました。

行ロック時にメモリが枯渇し、エラーが発生する

膨大な量のレコードを行ロックした場合、innoDB が確保するバッファープールを食い潰すこととなり起こる現象です。バッファープールのサイズは、innodb_buffer_pool_size で確認することができます。対象DB の設定を確認したところ、手動で設定がされていないことから、デフォルトの128M が確保されている様でした。削除するレンジを考えると、肌感覚ですが十分事象として起き得そうです。

この事象の問題は、バッファープールに乗っていた他のキャッシュがクリアされることです。アクセス頻度の高いデータまでクリアされてしまうと、素直にDB へのスキャンが実行されてしまい、速度遅延が発生してしまいます。

他のクエリの実行計画が狂い、フルスキャンが発生する

こちらのサイト を参考にさせていただきました。
大量にレコードを削除している途中でINDEX 統計情報が更新されると、次に実行されるクエリで適切なINDEX が使われなくなり、フルスキャンの危険性が増えるというものです。
ここで重要なのは、INDEX 統計情報更新のトリガーです。いくつかあるのですが、その中の一つに、テーブル内のデータが一定量変化したときがあります。Mysql 5.6 だと一定量=1/10 です。
今回削除する直近2年間より古いというレンジを考えると、この事象も起き得る可能性が十分あります。

【暫定】結局どうすることで落ち着いたのか

開発メンバーと協議して、以下の方針で対応することにしました。

・別テーブルを作り、直近2年間だけをそちらに移行
・別テーブルにはパーテーションを作り、データのドロップを容易にする
・500,000/h のレコード増加は、アプリケーション側で吸収する

現状のテーブルに手を加えるのは危険なので、別テーブルへのデータ移行・スイッチを行う予定です。データ移行の方法に関しては、現在協議中です。

まとめ

調べていても実際に起こるのか確証は取れないので、経験値が物を言うなと感じました。それが中々できないわけですが。。
また、データ量が大きくなることがわかっているテーブル（データのトラッキングなどで）は、それを前提とした設計・設定を事前に考えた方が悲しいことにならないです。

最後に) 実践編も多分書きます！