RDS Proxy の導入によって直面した Slow Query 解消の重要性
先日、障害の再発防止策として RDS Proxy を導入しました。
今回は、その中で実感した Slow Query 解消の重要性について説明します。
サービス開発において、
- 検知してslackに流してはいるものの機能してない
- リファクタリングタスクとして後回しにされ続ける(そして永遠に解消されることがない)
という Slow Query。
深刻度が一定を超すと、リファクタタスクでは済まない、深刻な事態にも繋がりうるという話です。
前触れなしのCPUの張り付き
ある日、なんの前触れもなく RDS の CPU 利用率が100%近くに張り付く事象に遭遇しました。
それによってあらゆるリクエストがDBに到達できなくなり、サービスはダウン。
アプリ側・インフラ側両視点から復旧作業に追われる羽目になりました。
ひとまず最優先はサービスの復旧。
張り付いた後もCPU負荷の変化が見られなかった = 自然解消が望めなかったため、フェイルオーバーによって予備のインスタンスと強制入替することで、Connectionを強制断しました。
それによって、一旦はサービスが復活。
根本的な原因を調査し始めました。
Performance Insight を有効化して RDS 負荷を可視化
同様に監視していたDatabase Connection数と並べてみると、CPU利用率と相関がありそうなメトリクスになっています。
そのため Performance Insight を有効にし、処理に時間がかかっているクエリを可視化することにしました。
しかし、可視化はできたもののクエリの特定ができませんでした。
候補が多かったのと、Performance Insight上で表示される SQL ステートメントの最大長が 4,096 バイトに限定されているためです。
長さは 4,096 バイトに固定されます。
根本原因が解消できないため、引き続きサービスは不安定。
ここで RDS Proxy を導入してConnectionの再利用性を高めることで安定化を図ることにしました。
RDS Proxy とは
RDS Proxy は Amazon RDS 向けの高可用性フルマネージド型データベースプロキシです。
主な特徴は下記です。
1. データベース負荷の削減
→ 多数の接続要求によるデータベースの負荷を軽減する
2. 効率的な接続管理
→ アプリケーションがデータベースと確立した接続をプールおよび共有することで、データベースの効率とアプリケーションのスケーラビリティが向上する
3. フェイルオーバーによる高可用性
→ データベースインスタンスに障害が発生した場合、RDS Proxyは自動的にスタンバイインスタンスに接続を切り替える。これによって高可用性が実現される
今回の例でいうと、RDS側のConnectionが張られすぎているため、アプリケーションとRDSの間に Proxy を挟むことで、Connectionの再利用性を高めることを目的に導入しました。
RDS Proxy の導入と接続数の増加
RDS Proxy を導入した後、予想外のメトリクスに変化しました。
Connectionの再利用性を高めるため、RDSから見たConnection数は減ると予想していたのですが、実際のところかなりの勢いで増えました。
しかも、CPUが張り付いた当時の接続数をはるかに超えても、サービスは落ちないという状態です。
下記メトリクスから見てとれるように、特にCPU負荷率との相関関係も見られません。
この挙動は今までの接続・CPU負荷の関係からすると説明がつかないため、RDS Proxyの特性によると踏み、調査を行いました。
すると、BASE さんのブログにて同じ事象について解説がされていました。
RDS プロキシは、データベース接続が使用されなくなった 24 時間後にその接続を閉じます。プロキシは、アイドル状態の最大接続数の設定値に関係なく、このアクションを実行します。
切り替え直後は24時間で接続がガクンと落ち、その後すこしづつ平準化されていく動き
切り替え直後24hは増えていき、その後安定化に向かうとのことです。
「接続数が増えている」という点は正常なものだと判断し、様子を見守りました。
【後日追記】
継続監視を行うと、何度かガクンと接続数が落ちる挙動があったものの、「接続直後は増え続け、あるタイミングで落ち、安定化に向かう」という全体的な流れを確認できました。
ピン留めによって RDS Proxy の性能を活かせていなかった。
増加する接続数を横目にサービスを監視していると、やはりまだ不安定さは残っているようでした。
接続数の増加自体に問題がないとして、RDS側で実際に負荷が生じている原因を調査したところ、RDS Proxy 側でピン留めが大量に発生していることが判明しました。
ピン留めとは、公式から引用すると
The current number of database connections currently pinned because of operations in client requests that change session state.
という事象です。翻訳すると
セッションの状態を変更するクライアント・リクエストの操作のために、現在固定されているデータベース接続の数。
とのこと。
DatabaseConnectionsCurrentlySessionPinned というメトリクスで確認することができます。
特定の接続を設定値した接続上限時間まで維持する仕組みで、
通常は一定時間利用がなければ自動的に開放されるが、ピン留めされた接続は使われていなくとも30分維持されるようです。
つまり、ピン留めが発生すると下記の影響が出てきます。
1. 接続の多重化の効率低下
→ 複数のクライアントが同じデータベース接続を共有できなくなり、接続数が増加する。
2. 接続リソースの無駄
→ ピン留めされた接続は、クライアント接続が終了するまで他のクライアントに再利用できない。
3. パフォーマンスへの影響
→ ピン留めが頻繁に発生すると、RDS Proxyのパフォーマンスが低下する。
要は
「接続を占有してしまうから、そもそもの導入目的だったConnectionの再利用性が落ちる」
ということです。
RDS Proxy を導入しておきながらそのメリットを享受できないため、ピン留めを解消する必要があります。
ピン留め原因の特定
ピン留めが発生するのは、主に下記の原因があるようです。
1. セッション状態の変更
2. 大きなテキストサイズ
3. 一時テーブルやカーソルの作成
4. ロックやトランザクション
5. Prepared Statements
この中で、ピン留めを引き起こしている可能性が高いのは、サービス特性上2,4だとあたりをつけました。
特に2に関して。
Performance Insight 中の記述を再度引用しますが、
長さは 4,096 バイトに固定されます。
という制限があり、実際に表示されていないSQLステートメントが存在しました。
では、
- 大きいテキストサイズ
とはどのボリュームなのか。
Any statement with a text size greater than 16 KB causes the proxy to pin the session.
テキストサイズが16KBを超えるステートメントは、プロキシにセッションを固定させる。
16kBを超えた場合はピン留めされるとのこと。
つまり、Performance Insightでも表示できない、16kBを超えるSQLステートメントがピン留めの要因となっていることが高いと判断できます。
Performance Insight 上で全ステートメントは確認できないものの、表示されているクエリから該当らしきロジックを特定し、実際のSQLを生成してみました。
するとなんと、285437byte。およそ 28.5kBです。
しかもユーザが訪れやすい=クエリ発行量が多い箇所だったため、ピン留めの主要因になっている可能性が高いです。
ピン留めの原因と考えられるSQLの改善
あたりをつけて実際に生成したSQLには下記の特徴がありました。
- 大量のIN句が含まれており、MySQLにとって重い処理だった
- RDS Proxyから見ると16KBを超えるステートメントだったため、ピン留めの対象になった
1の根拠については下記の記事がわかりやすいです。
重要なところを引用すると、
MySQLでIN句に大量の要素を渡すとインデックスを貼っていたカラムだとしてもフルスキャンが発生しスロークエリになる可能性があります。
つまり、indexの効果を減らし、フルスキャンによって重くなってしまうとのことです。
この点とクエリの重さが重なることにより、ピン留めという形で RDS Proxy のメリットを減らし、サービス安定化まで至らなかったということになります。
そこでIN句をより効率的な探索方法に変換し、前後のステートメント比で
515/285437 ≒ 2%
の改善に成功しました。
この改善の前後でピン留めの最大値同士を比較すると、およそ6割まで減らせたことになります。
この後 RDS CPUの負荷含め、サービスの安定を取り戻しました。
まとめと教訓
実は、この重いクエリ含め、私たちは Slow Query を特定・通知する仕組みを導入していました。
しかし、この仕組みは完全に形骸化していました。
「手が空いたらやる」ものとして捉えていましたし、そもそも手が空くことはないのです。
いつかやろう、そのいつかは来ないのです。
サービスの新機能開発に追われ、このような Slow Query を放っておくと、
- DAUの増加
- アプリケーションの複雑化
などにより、思ってもいないタイミングで「ボロが出る」ことになります。
今回はインシデントという形になってしまいましたが、Slow Queryへの対応優先順位が低いことで、こうなる可能性があるという事例にはなったかと思います。
ひたすら後回しにしていて、サービス規模も大きくなってきた場合は、一度腰を据えてサービスのクエリを見直してみる。
そして、機会損失につながる可能性を示唆し、対応優先順位を上げる、という交渉・打診を行なってみてもいいかもしれません。
ありがとうございました。
Discussion