LIFULL Creators Blog

LIFULL Creators Blogとは、株式会社LIFULLの社員が記事を共有するブログです。自分の役立つ経験や知識を広めることで世界をもっとFULLにしていきます。

MySQLの不要データをテーブルローテーションでイージーに削除した

検索エンジンチームにいながら外部公開APIのメンテナンスもしている加藤宏脩です。

この記事では、毎日大量に書き込まれ膨れ上がったMySQLのテーブルを、 テーブルローテーションさせることで不要なデータを継続的かつ安全に削除する処理の実装をしたのでそれについてお話したいと思います。

利用している技術

  • Amazon RDS for MySQL

    • Engine version: 5.7.41
  • Amazon ElastiCache for Redis

    • Engine version: 6.2.6

起きていた問題

LIFULLのとあるサービスは、アプリケーションとMySQL、DBの結果をキャッシュするRedisがあるというよくみる一般的なアーキテクチャで運用しています。

このMySQLのテーブルは毎日100万件以上のレコードが追加されていく状態になっており、 総レコード数は6億件を超え、容量は2TBを超えていました。 またMySQLの仕様もあり、不要になったレコードを簡単に削除することはできなくなっていました。 そのため、DBの空き容量が少なくなるたびにストレージを追加する運用を数年続けていました。 このままでは永遠にデータが増え続けてしまうので、不要な数億のレコードを削除して、今後も増え続けないようにする必要がありました。

問題解決を阻む課題

書き込みが継続しているテーブルへのレコード削除は不安定

MySQLのデフォルトのストレージエンジンであるInnoDBは、DELETE文を実行しても物理的な削除を行わずフラグメンテーションしてしまう仕様となっています。 サービスをとめずに解決するためには、DELETEコマンドで不要なデータを削除したあとに下記のように、ALTER TABLEコマンドを実行することでテーブルを再構築する必要があります。

ALTER TABLE tbl_name ENGINE=INNODB

MySQLのドキュメント: https://dev.mysql.com/doc/refman/5.7/en/innodb-file-defragmenting.html

検証環境で上記コマンドを実行したところ、以下のことがわかりました。

  • 初回のALTER TABLEの実行時間は12時間を超える
  • 初回のALTER TABLEはテーブルをコピーするため容量が2倍の4TB以上必要になる
  • InnoDB テーブルの online DDL 操作中に使用される一時ログファイルのサイズの上限設定値以上に書き込むと、 それまでのデータがすべて失われる。(innodb-online-alter-log-max-size)

このアプリケーションのデータ書き込み量は不安定で、突然今までの2倍以上書き込まれる可能性がありました。 そのためサービスで利用しているテーブルのデータを削除して容量確保することは危険であり、 定期的に実行できるものではないことがわかりました。

DBごと移行することは、データの同期にラグができてしまい切り替え時に不整合が起きてしまう

上述の通り、運用しているDBに手を加えて解決することはできなかったため、新しく空のDBまたはテーブルを作り、 移行する処理を考えましたが、こちらにも課題がありました。

考えていた処理は以下の通り。

  1. 利用中のテーブルから不要なデータをDELETE文で削除する(この時点ではフラグメンテーションされているため空き容量は確保されていない)。
  2. その後、新しくDBもしくはテーブルを作り、データの同期をする。
  3. データの同期が終わり次第2で作ったDBもしくはテーブルに書き込みの向き先を切り替える。

このDBは絶えず激しく書き込みが行われているため、3の実行時に データの同期が完全に終わることがなく、DBの向き先を切り替えると 不整合が起きてしまうためできないことがわかりました。

書き込みを制限することは困難

本来、書き込みは一日数十万件にもなるようなものではなく、意図した設計とは違う使われ方をしていました。 アプリケーション側で書き込み数上限を設定することも考えました。 しかし、アプリケーションの利用者が社外にいるため 突然制限するわけにも行かず、 半年〜1年以上システムの改修対応をする期間を待つ必要があったため、書き込み制限は行いませんでした。

この問題に時間はかけられない

データ量が多いため、レコード数の取得をするだけでも長時間かかります。 システムの状況を把握し検証をすると、数日かかってしまう状況でした。 本プロジェクト自体、チームのメイン業務ではなく、早くメイン業務に合流する必要があるため 時間をかけずにできるだけ早めに解決する必要がありました。

解決策

解決策は、 4つのテーブルを用意し3ヵ月ごとにローテーションし、 向き先のテーブルにデータが見つからなければ、前の時期のテーブルを探すように実装することでした。 こうすることで古いテーブルに書き込んで新しいテーブルにない状態でも不整合が起きなくなります。 さらに、2期間前のテーブルには完全にアクセスされなくなるため、 TRUNCATEするバッチ処理を実装するようにしました。 4つのテーブルの内訳は、下記の通りです。

  • 現在の時期の向き先。読み書きするテーブル
  • 1つ前の向き先。現在の時期の向き先にデータがない場合に呼ばれるテーブル
  • 2つ前の向き先。読み書きされないため安全にデータを削除できるテーブル
  • 次回のローテーションの向き先。TRUNCATE前にローテーションして新しいデータを書き込んでしまうのを防ぐためにある空のテーブル

結果、最小限の工数でサービスもやめず、DBの不要なデータを定期的に削除できるようになりました。

問題解決をするうえで着目したポイント

  • レコードの有効期限は最長でも1ヵ月なのでそれ以降のデータはすべて削除できる
  • データは一本釣りしかないので、向き先のテーブルにデータがなければ前の時期のテーブルから探しやすい
  • 検索結果はRedisにてキャッシュするため、 同じレコードを取るために何度も向き先のテーブルと前の時期のテーブルにクエリが走ることは起きない

解決策の実装

  • アプリケーションの変更: 現在の向き先テーブルにデータが見つからなければ、古いテーブルを探すようにする
  • DBの変更: 同じテーブルを数個用意する
  • バッチの実装: 2期間前のテーブルをTRUNCATEする

他に考えていた手段

一本釣りのようなクエリが多くデータの賞味期限が短いことなどから、 MySQLのようなRDBの利用は妥当じゃないので、ほかのデータストアの利用を検討していました。 前段に検索結果をキャッシュするRedisがいるので、 Redisを永続的なデータストアとしても使えるAmazon MemoryDB for Redisを検討していました。 今回は工数の関係で解決策に記載している方法をとりましたが、 まだ諦めていないのでAmazon MemoryDB for Redisに変えてみたいと思っています。

感想

6億レコードを超えたMySQLのテーブルを継続的かつ安全に削除する処理の実装をした話でした。 読んでいただきありがとうございました。

フラグメンテーションの問題を回避しつつ、 サービスをとめることなく 完全にアクセスされないデータを削除できるようになりました。 このような泥臭い作業は軽視されがちですが、 積み重ねることでシステム運用の負担を減らせて、開発者が本来の力を発揮しやすくなるのだと考えています。 ひいては LIFULLのビジョン実現につながるのだと思います。

最後に、 このような効率化をしたいまたは得意なエンジニアの方々、 LIFULL では一緒に働く仲間を募集しています。この記事を読んで LIFULL に興味ができた方は求人情報も御覧ください。

hrmos.co

hrmos.co