😔

Amazon RDS（MySQL）で困ったことになった話とその対策

2024/06/05に公開

AWS

MySQL

tech

はじめに

かなり前ですがAmazon RDS（MySQL）で困ったことになった話があったので教訓として書き留めておきます。

どんなことが起きたのか

システムの概要

該当のシステムはかなり昔に作られたシステムで図のように写真をユーザがアップロードしたものをRDS上に保存して色々な処理を行うようなシステムでした。

RDSはMySQL（InnoDB）を使用し、IDや日付情報と一緒にBLOB形式で画像データを保存するものとなっていました。（あまりにもアンチパターン過ぎる…）
それなりにユーザ数も多かったため膨大な量のリクエストとデータが格納されるようなシステムでした。
保存データには期限が設けられており、所謂お掃除バッチが定期的に動作し、一定期間が過ぎたデータを削除するような動きとなっていました。

問題の発生

大量のリクエスト×MySQL（InnoDB）×大量のBlobデータ×バッチ処理となると何となく察した方もいると思いますが、ギャップロックとネクストキーロックの問題が発生しました。

ギャップロックとは

ギャップロックは、インデックスレコード間の「ギャップ」、つまり範囲をロックします。これは、他のトランザクションがその範囲内に新しいレコードを挿入することを防ぎます。

ネクストキーロックとは

ネクストキーロックは、レコードロックとギャップロックの組み合わせです。これは、指定されたレコードと、そのレコードの前後の「ギャップ」をロックします。これにより、ファントムリードを防ぐことができます。

詳細はこちらが分かりやすいので参考にどうぞ

https://softwarenote.info/p1067/

話は戻りまして大量のBlobデータを削除しようとするとそれなりに時間がかかります。
もちろんトランザクションを張って処理を行いますので、前述のギャップロックとネクストキーロックが広範囲かつ長時間かかってしまいます。
つまり何が起こるかというとユーザのリクエストに対していつまでも終わらないロック解除待ちになってしまうのでリクエストタイムアウトが頻発してしまいます。

また、リクエストが急激に伸びたことで削除処理自体の負荷やバイナリログが大量に出ることでサーバ容量の枯渇等様々な問題が発生しました

対策

こういった場合、いくつか対策が考えられます。

1.トランザクションの分割

比較的お手軽にできるのがトランザクションの分割です。
例えば100レコード単位のトランザクション処理を繰り返してロック範囲とロックの保持時間を短縮することが期待できます。
⇒結局広範囲にロックがかかることには変わらず、トランザクションとトランザクションの間で一瞬隙間時間ができるだけなので大量リクエスト×大量データの前には焼け石に水でした…

2.テーブルのパーティショニング

次に考えられるのがテーブルのパーティショニングです。

パーティショニングとは？

パーティショニングはこの認識をさらに一歩進めて、必要に応じて多くの部分を設定できるルールに従って、個々のテーブルの部分をファイルシステムに配分できるようにしています。それにより、テーブルの異なる部分が別個のテーブルとして別個の場所に格納されます。データを分割するためにユーザーが選択するルールはパーティショニング関数と呼ばれ、MySQL では法、範囲セットまたは値リストに対する単純な照合、内部ハッシュ関数、または線形ハッシュ関数が使用されます。関数は、ユーザーが指定したパーティショニングタイプに従って選択され、ユーザーが指定した式の値をパラメータとして取ります。この式には、使用されるパーティショニングのタイプに応じて、カラム値、1 つ以上のカラム値を操作する関数、または 1 つ以上のカラム値のセットを指定できます。

イメージとしては下記のように1つのテーブルを仮想的に複数の区切りに分けることです。

この機能を使うことでパーティション単位での削除が可能となり、前述したロックの諸問題から解放されます！！！

これでうまくいかないのが辛い所ですね…
パーティションを導入するにあたりいくつか課題がありました。

大量データのためパーティションを1時間等短い時間で作成する必要がある
パーティション作成はバッチで行うが、もしバッチがこけてしまうとシステム自体がダウンしてしまう

これ以外にもいくつかの課題により断念しました。

3.BlobデータのS3への格納

ここまでの対策でどうにもならないのであれば正攻法でいくしかないですね。
どうしたかというとRDSのBLOBデータをS3に保存し、リンクをテーブル内に保存するという方法です。

この構成には以下のようなメリットがあります。
・ S3のデータ削除はライフサイクルに任せられる（バッチ管理が不要）
・ RDSの容量が削減できる。
・ RDSの削除バッチの速度が大幅に改善され、ギャップロックやネクストキーロックの発生が抑えられる

この対策の結果
実際に数時間かかっていた削除バッチが数秒で終わるようになりました！やったね！！！

また、DynamoDBを使う場合でも同様にS3に画像本体を保存する方法が推奨されています。
こちらはアイテムサイズの上限が影響していたりします。

まとめ

昔から使っていたようなシステムだと、今回のように本来のあるべき姿やベストプラクティスから逸脱したようなシステム構成になっていることも多いです。

もし同様に何かしらのバイナリデータを保存するときにはDBとS3の組み合わせで行うようにしましょうという教訓でした。