ヌーラボブログリレー2024 for Tech Advent Calendar 2024の6日目の記事です。
Backlogのソフトウェアエンジニアの谷本です。
この記事では、Backlogのパフォーマンス改善のために行ったRDBテーブルリファクタリングについて、その内容と考えたことを紹介します。
目次
課題整理
問い合わせや社内のフィードバックで、特定のエンドポイントのレスポンスタイムが一部のユーザーで極端に遅くなるケースが見つかりました。調査の結果、以下のことがわかりました。
- 該当するSQLのSELECT文がスロークエリとなっており、アプリケーションではなくデータベースがボトルネックになっている。
- 遅いSQLの発行数自体は少なく、RDSのPerformance Insightsで平均アクティブセッション（AAS）順で見ても上位には入っていない。
- 3つのテーブル（X、Y、Z）をJOINして、それぞれのテーブルのカラムがWHERE条件に分かれているため、効果的なインデックスが設定できておらず遅い。
関連課題整理
上記の課題（以降、課題1と記載）を本格的に対応する前に同時に解決できそうな課題を探していたところ、以下の2つの課題が見つかりました。
課題2
課題1のSQLと同様にテーブルX、Y、Zに対して発行される異なる条件のSQLも効果的なインデックスが設定できていませんでした。課題1ほどユーザー体験に影響を与えるものではないものの、リクエスト数が多くDB負荷が高いため、ピークタイムではDB全体のAASの1割から2割を占めていました。
課題3
本来、テーブルXとYのレコードとテーブルZのレコードはライフサイクルが異なり、テーブルZのレコードの方が短命です。しかし、X、Y、ZをJOINするため、テーブルZの一部レコードはXとYのライフサイクルに合わせて長いライフサイクルを強いられていました。これによりテーブルZのレコードが不必要に多くなり、他のパフォーマンス問題の原因となっていました。
各課題の特徴まとめ
- 課題1: リクエスト回数は少ないが、非常に遅いSQLがユーザー体験に影響を与えている。
- 課題2: 遅いSQLでリクエスト数が多く、DB負荷が高い。ユーザー体験には影響なし。
- 課題3: ライフサイクルが短いはずのデータが長く保持されている。
解決方法案
以下の3つの案を検討しました。
- 案1: 必要な情報を1つのテーブルにまとめる
- 新しいテーブルに必要なデータをまとめ、課題1と課題2のSQLに効果的なインデックスを設定する。
- テーブルZの一部のデータを冗長的に新しいテーブルに持たせることで、テーブルZをJOINする必要をなくし、ライフサイクルを分離する。
- 案2: RDBから分離
- 案1と同様にデータをまとめるが、データ特性に合わせて異なるDBに分ける。
- 案3: キャッシュする
- 重いSQLの結果をキャッシュしてパフォーマンスを改善する。
検討の結果、案1を採用することにしました。理由は以下の通りです。
- 案2ではトランザクションの分離や既存のふるまいの変更など、検討事項が増える。
- 将来的に案2に移行する場合でも、まず案1を実施しておくことで整理でき、無駄にならない。
- RDBで解決するよりもキャッシュを使う方が複雑になりやすいため、まずはRDBのインデックスで解決を目指す方がシンプル。
対応するスコープ
- 課題1: インデックスの設定と効果測定まで。ボトルネックが別の場所に移った場合はその解決はスコープ外とする。
- 課題2: インデックスの設定と効果測定。
- 課題3: 新テーブルに冗長的にデータを持たせて不要なライフサイクル制限を取り除く。テーブルZのレコードのライフサイクルロジックの変更はスコープ外とする。
解決までスコープ内に含まれているのは課題2だけですが、それでも意味のある改善と判断しました。
リファクタリング手順
サービスを無停止でリファクタリングを行いたいこと、またDBクラスタのWriterへの負荷があまり高くなかったことから、新旧両テーブルに同時に書き込む以下の手順を採用しました。
- SQLを精査し、新テーブルにインデックスを設定して作成。
- アプリケーションから新旧両方のテーブルに書き込み。
- DB負荷を考慮しながら、旧テーブルから新テーブルへのデータコピー。
- コピーが完了したら、アプリケーションから読み込み先のテーブルを新テーブルに変更。
- 問題が見つかれば読み込み先を旧テーブルに戻して修正。（アプリケーションプロセスの再起動なしで変更できるようにフラグで管理）
- 読み込み先を旧テーブルに戻す必要がなくなったら、旧テーブルへの書き込みを停止。
監視していたメトリックと観点
主に監視していたメトリックは以下の2点です。
- 関連エンドポイントのレスポンスタイムパーセンタイルの変化
- 関連テーブルへのSQLのAASとDB全体のAASの変化
観点は切り替え後の経過時間で分けました。
- 切り替え直後はユーザ体験への悪影響を最小にするために、ほぼリアルタイムに悪い影響がないか監視
- 少しでもおかしそうなものが見つかったら詳細を調査
- 切り替え後1日や1週間後はリファクタリングの効果評価のため、総合的な傾向分析
使用したツールは主に社内で使用しているOpenSearchやRDSのPerformance Insightsです。
AASの効果評価だけはPerformance Insightsの情報をawscliで取得しローカルで立てたElasticsearch+Kibanaで表示しました。Performance Insightsだとクラスタ単位での表示や選んだ2つ以上のSQLのみを表示ができなかったためです。
結果
課題1の結果
インデックスにより極端に遅くなる状況が改善され、対象のエンドポイント全体のパフォーマンスも向上しました。
図1:対象エンドポイント全体のレスポンスタイムのパーセンタイル変化
一部DBクラスタにアクセスしているリクエストのみ
（x軸は時刻、y軸はレスポンスタイム）
課題2の結果
インデックスにより対象のSQLのAASが全体の1割以下に抑えられました。さらにクラスタ毎にばらつきがありましたが、ピーク時のDB全体のAASが1割から4割ほど低下しました。
図2:一部DBクラスタのAASの変化
（x軸は時刻、y軸はAAS、緑色の線は全体、青色の線は旧テーブルへのSQL、薄い青色の線は新テーブルへのSQL）
課題3の結果
ライフサイクルの異なるデータの分離が成功し、今まで削除できていなかったデータの削除が可能になりました。
感想と教訓
大きな問題なく3つの課題を改善でき、結果として良好でした。
しかし、AASの改善は多くても全体の1割程度と予測していたのに対し、1割から4割程度改善しました。AASがどういう指標なのかに対する理解が不足していたと思います。
また、スロークエリログの傾向を整理し、レスポンスタイムの傾向と比較していれば、より正確な予測ができたかもしれません。次回はスロークエリログの傾向整理も検討したいと思います。