inSmartBank

B/43を運営する株式会社スマートバンクのメンバーによるブログです

管理画面とユーザー機能の調和を取り戻す！~クエリパフォーマンス改善の成功物語~

Engineering

はじめに

こんにちは！

CRE（Customer Reliability Engineering）でサーバーサイドエンジニアをしているotaka(@oh_minisera)です。

突然ですが、管理画面から重いクエリを発行してしまい本番環境に障害が発生...。あなたならどう対応しますか？

今回は管理画面を舞台にした障害事例に対し、どんな背景の下で意思決定をしたのか紹介できればと思います。

障害は突然やってくる

いつもアラートを確認しているチャンネルにこんな投稿が。

この後、本番環境にてアプリ向けのAPIや決済用APIのパフォーマンスが一時的に悪化する障害が確認されました。調査の結果、管理画面から発行されるクエリがDBのCPUを圧迫していたことが原因でした。

解決方針

私たちのインフラの構成では、管理画面のアプリとユーザー向けアプリ（B/43）が一つのDBを共有しています。そのため、今回のように管理画面から重いクエリが発行されるとユーザー向けアプリにも影響が及ぶことがありました。

この状態を解決すべく私たちがやったことは「max_execution_timeの設定とAPIの改修」でした。

max_execution_timeとは、mysqlのシステム変数の一つで実行タイムアウトを設定できます。*1

管理画面から実行されるクエリにタイムアウトを設定することで実行時間が長いクエリを強制終了できます。今回はこれを設定して仮にCPUが圧迫されても短期間で解消することで他への影響を小さくする方法を選びました。

意思決定の背景

当時、以下のソリューションが候補に上がりました。

工数 やるやら
問題のクエリを発行するAPI改修 小 ？
管理画面のみにmax_execution_timeを設定 小 ？
レプリケーションによりユーザー向けサービスと管理画面のDBを分ける 大 ？

今回はCREの他タスクとの兼ね合いもあり多くの工数を掛けられないのため、短期間で障害を予防できるような方針を検討しました。

結論、以下のような方針でまとまりました。

  1. 閾値（max_execution_timeに設定する値）を決める
  2. 閾値を超えうる管理画面で使用されているAPIを修正する
  3. 管理画面にのみmax_execution_timeを設定する

閾値と修正対象のAPIの設定

閾値を決めるにあたり、管理画面で使用されているクエリ実行時間を洗い出しました。 「max_query_execution_time_ms」はAPIから発行されたクエリの中で一番長い処理時間です。この値の降順にcontroller#actionを表示しています。

集計期間 5.1-6.13

controller action max_query_execution_time_ms
clearings index 165828.8
bulk_personal_notification_creations index 62350.2
user_activations index 56392.1
authorization_request_histories index 44470.1
convenience_stores index 35813.8
kyc_verification_assignees create 33023.6
payeasies index 27124.3
deposit_intents index 25777.3
gmo_aozora_virtual_accounts index 25148.9
balance_transactions index 23369
…

全てのAPIを改修できれば最高ですが、短期間で対応する方針のためスコープを絞って修正します。

上位からgmo_aozora_virtual_accounts#indexまでが現実的な修正可能なAPI数（9本）とし、balance_transactions#indexの23369msをタイムアウトさせないようmax_execution_timeを25秒とすることにしました。

修正対象API

controller action
clearings index
bulk_personal_notification_creations index
user_activations index
authorization_request_histories index
convenience_stores index
kyc_verification_assignees create
payeasies index
deposit_intents index
gmo_aozora_virtual_accounts index

max_execution_timeの設定

今回はmax_execution_timeの設定にスポットを当てて紹介したいため、APIの修正内容は割愛します。*2

APIの修正が終わった後、max_execution_timeを設定していきます。Railsではconfig/database.ymlにて以下のように設定が可能です。

# config/database.yml
# Before

default: &default
  adapter: mysql2
  encoding: utf8mb4
   # 省略
  variables:
    max_execution_time: 25000
    
# 省略

しかし、今回は管理画面にのみに限定して設定したいです。

少し私たちの検証環境、本番環境についてお話しします。

前項で話した修正対象のAPIは下図でいうcore-apiに属するもので、このcore-apiは管理画面用のコンテナとユーザー向けアプリ用のコンテナにそれぞれ配置されています。

今回は管理画面用のコンテナのみに環境変数を設定し、その変数があればmax_execution_timeを設定するようにしました。

# config/database.yml
# After

default: &default
  adapter: mysql2
  encoding: utf8mb4
    
# 省略
production:
  <<: *default
   # 省略
    <% if ENV['ENABLE_MYSQL_MAX_EXECUTION_TIME'] %>
  variables:
    max_execution_time: 25000
    <% end %>

設定されているか検証環境で確認

コードを書いた後、検証環境に上げてmax_execution_timeが設定されているか確認します。

…

INFO -- : Database Variables:[["max_execution_time", "25000"]]

…おお！期待通り設定されていました！

INFO -- : Database Variables:[["max_execution_time", "25000"]]

...ん？

INFO -- : Database Variables:[["max_execution_time", "0"]]

ですがどうでしょう？設定されていないDBコネクションもちらほら見かけます。

更には、本来設定する想定でないユーザー向けアプリが載っているコンテナにて、max_execution_timeが設定されているコネクションも存在しました。

なぜだ…

RDS Proxyの設定見直し

そこに鋭い考察が…！

私たちの全てアプリケーションは、ECSの各コンテナとRDSの間にRDS Proxyを経由していました。*3

RDS Proxyはコネクションプーリングという機能があり、既存のコネクションを再利用してアプリケーションからの接続リクエストを効率よく管理することができます。*4

今回のmax_execution_timeが設定が（されていたり、されていなかったり）まばらになる現象はこちらに起因するものでした！

整理すると以下になります。

  1. 管理画面のアプリからのDBへ接続リクエスト（max_execution_timeの設定有）
  2. RDS Proxyによりコネクションがプールされる（コネクションA）
  3. ユーザー向けアプリからのDBへ接続リクエスト（max_execution_timeの設定無）
  4. RDS Proxyによりコネクションがプールされる（コネクションB）
  5. 再び管理画面のアプリからのDBへ接続リクエスト
    1. コネクションA or Bが使用される

そこで今回を機に管理画面のアプリからはRDS Proxyを通すことをやめ、直にDBを参照することになりました。

元々RDS Proxyを経由していた理由は、RDS のインスタンスサイズが小さい時代、デプロイ時に max_connections を超えることがあるためでした。現在はインスタンスサイズも大きくなり余裕があるためRDS Proxyを外しても良いと判断しました。

RDS Proxyを外して再度確認します…。

…

INFO -- : Database Variables:[["max_execution_time", "25000"]]

…オッ！無事に管理画面のアプリのみにmax_execution_timeが設定されていることを確認できました！

本番環境に反映

検証環境で動作確認した後、本番環境にも同様の設定を反映しました。これによりクエリ実行時間の上限が25000msとなり処理時間の長いクエリに予防線を張ることができました！

この処置から3ヶ月ほど経ちますが管理画面を起因とした障害は起こってません。

集計期間 8.1-8.31

controller action max_query_execution_time_ms
balance_transactions index 25057.9
post_pay_intent_units index 25050.4
convenience_stores index 25045.2
kyc_verification_assignees create 22680.2
user_activations index 21697.2
deposit_intents index 20567.8
authorizations index 15241.8
…

残課題

上記の表を見ると分かり易いですが、convenience_stores#indexなどはmax_execution_timeにより処理が中断されることが稀にあります。頻度としては高くないですが、体験としては良くないため改修していきたいです。（本来タイムアウトを回避したかったbalance_transactions#indexも同様）

また、いくら一つのクエリの処理時間の上限を設定できたと言えど、短期間に重いクエリを何回も発行されればCPUに負荷がかかるため、今回修正できなかったAPIも腰を据えて改修していきたいです。

エンジニア募集中です！

絶賛エンジニアを大募集しています！

CREでやりたいことは沢山ありますが手が足りていません…。あなたの手が必要です！

是非カジュアル面談からでもご応募お待ちしております。

smartbank.co.jp

*1:https://dev.mysql.com/doc/refman/8.0/ja/server-system-variables.html#sysvar_max_execution_time

*2:APIの修正でやったことは、地道に不要なクエリを削ったぐらいです。

*3:https://aws.amazon.com/jp/rds/proxy/

*4:https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/rds-proxy.howitworks.html#rds-proxy-connection-pooling

We create the new normal of easy budgeting, easy banking, and easy living.
In this blog, engineers, product managers, designers, business development, legal, CS, and other members will share their insights.