Performance Insightsを利用したMySQL負荷改善プロセスの紹介
この記事はEureka Advent Calendar 2023の21日目の記事です。
こんにちは！Pairs Back-end Engineer の金井です。
私事ですが、今年は業務内容がガラッと変わった一年でした。今年上半期まではプロダクトの機能開発を主に実施しておりましたが、下半期からはPairs Backendの横断的な改善に取り組むようになりました。改善活動自体は去年から色々実施していましたが、より多くの時間を割けるようになったといった感じです。
そんな改善活動の中で今年印象深かったのがDB負荷改善で、過去の改善活動から培った観点を反映したプロセスを構築し実施してみたところ、それなりの手応えを感じることができました（もちろん目的となる負荷改善も達成することができました）。今回のAdvent CalenderはそのDB負荷改善プロセスを紹介したいと思います。
（ちなみに先日ISUCONについての記事も投稿しました。こちらも読んでくださると嬉しいです！）
負荷改善の対象となったDBMSはMySQLでありPerformance Schema/AWS Performance Insights（正確にはPerformance Schemaイベントデータに基づくAWS Performance Insights）を利用したので、先にこの２つについて軽く紹介させていただきます。
Performance Schema
Performance SchemaはMySQL5.5から実装されたMySQL Serverの性能評価ツールです。Performance Schemaを利用することで、MySQL Server上でどのようなイベントがどの程度起こっているのかを知ることができ、MySQL Serverの負荷やパフォーマンスの改善を行う際に役立てることができます。
Performance SchemaのインターフェースはInformation Schema同様テーブルであり、専用のデータベース performance_schemaのテーブルを参照・更新することで参照・更新ができます。
Performance Schemaのイベント収集
Performance SchemaはMySQL Serverコード上のあちこちに埋め込まれた計器（MySQLドキュメントではインストゥルメント/Instrumentと表記）からイベントを収集し、イベントの履歴やそのサマリー、ロック情報などをデータベース performance_schemaのテーブル上に保存します。
デフォルトでは一部のイベントのみ収集されるようになっているので、必要なイベントは追加で収集するよう設定する必要があります。イベントの収集はパフォーマンスや容量とのトレードオフなので、必要最低限のイベントだけ収集するようにしましょう。イベント収集のオン・オフは計器・テーブルなどのオブジェクト・コンシューマー・ユーザーやホスト・実行中スレッドごとに設定できます。
また、テーブルに使用されるストレージエンジンはインメモリにイベントデータを収集しており、MySQL Server再実行時に設定が揮発してしまうので、サーバー起動時に設定が必要になる点も注意が必要です。
Tables in the Performance Schema are in-memory tables that use no persistent on-disk storage. The contents are repopulated beginning at server startup and discarded at server shutdown.
Performance Schemaの有効化
Performance Schemaが有効かどうかは、下記クエリで確認することができます。
# Performance Schema が有効かどうかをチェック
SHOW VARIABLES LIKE 'performance_schema';+------------------+-----+
|Variable_name |Value|
+------------------+-----+
|performance_schema|ON |
+------------------+-----+もし有効でない場合、my.cnf等有効なMySQL設定ファイル上でPerformance Schemaを有効にできます。
[mysqld]
performance_schema=ON後述するPerformance InsightsをAmazon Aurora MySQL /Amazon RDS for MySQL上で使用する場合、Performance Schemaの設定が必要になるので確認しておくと良いでしょう。
Performance Insights
Performance InsightsはAWSから提供されている、データベースパフォーマンスのモニタリングを行う機能です。各クエリの負荷や全体のクエリ負荷を時系列グラフとして表示したり、各クエリごとの負荷（につながるイベント）の内訳を負荷や呼び出し数などでソートしつつ可視化できます。
前述の通りAmazon Aurora MySQLおよび Amazon RDS for MySQLで使用する場合、Performance Insightsの情報収集結果がPerformance Schemaの設定に依存します。そのためMySQLのボトルネック特定に役立つような情報を収集するには、Performance Insightsの設定だけでなく上述のPerformance Schemaの有効化も必要になります。
待機イベント
待機イベントとは、Performance Insightsにおける負荷指標です。待機イベントの名称は作業が妨げられる箇所を示しており、負荷の原因を特定するのに役立ちます。Aurora MySQLの場合、これらのイベント発行は前述の通りPerformance Schemaの設定に依存します。
筆者がPerformance Insightsを使用する際、主に下記の待機イベントを参照してボトルネックを特定することが多いです。
CPU
CPU待機イベントです。このイベントの割合が大きいほど、アクティブな接続スレッドがCPU（vCPU）の割り当てを待っていることになります。
実行時間の長いクエリ（のトランザクション）や並列数の多いクエリが発行されたり、接続数が急激に増加することによって発生します。
io/table/sql/handler
MySQLの場合、wait/io/table/sql/handler 計器によって集計されるテーブル I/O 待機イベント。このイベントが多く発生しているクエリは、対象のテーブルがロックされるなどでアクセス待ちが多く発生している可能性があります。
io/socket/sql/client_connection
クライアント接続処理時のスレッド作成で、mysqldがbusyな時に発生するイベント。アプリケーションからの接続が急に増えると発生する可能性があります。
DB負荷改善の流れ
次はPerformance Insightsを使用したときの、DBの負荷改善を実施する際のプロセスを紹介します。なおDBMSにはMySQL5.7、各テーブルのストレージエンジンにInnoDBを使用しているものとします。
目的を設定する
まずDBの負荷改善を実行する前に、改めて負荷改善の目的(Why)を明確にし、負荷改善そのもののアプローチが正しいかを評価します。
例：
- Webサービスのパフォーマンスを改善したい。APIのレイテンシを小さくしたり、バッチの完了時間の遅延を軽減したい。
- サービスダウンのリスクを軽減したい。DBがSPOFとなってしまっており、休暇シーズンに増加するリクエストに耐えられるようにしたい。
1.が目的であれば、そもそもDB負荷がレイテンシなどのボトルネックになっているかどうかを見極めるための計測・分析作業が必要です。逆に2.のようにDBへの負荷が今後問題になることが明らかであれば、負荷改善対応を実施する価値があると判断できます。
受け入れ条件を設定する
目的が決まったら、目的が達成したと言える基準を設けるため、受け入れ条件を設定します。この条件を達成したら作業完了とします。
ただ負荷改善作業において、自身の採用するアプローチでどの程度改善できるかはやってみないとわからないことが多いです。そのため具体的な基準の設定が難しい場合は、監視・評価すべき項目だけでも設定しておくと、少なくとも改善による変更を追跡しやすくなります。
例：サービスダウンのリスクを軽減したい。
-> サービスが利用するDBのクエリレイテンシ・CPU使用率・メモリ使用率・ストレージ使用率を観測
（より具体的にする場合：Primary DBに使用するインスタンスxxxのCPU使用率をピーク時間帯XX:XX~YY:YYの間で60%以内に抑える）
Performance Insightsを使用して、DB負荷の主な要因となっているクエリを特定する
DBの負荷改善を実行することが決まったら、Performance Insightsを利用してどのクエリの負荷が支配的なのかを調査してみます。
Performance Insights上では前述の通り負荷（AAS）の大きい順にクエリ一覧をソートできるので、大抵の場合上位のクエリを改善すれば良いケースが多いです。長期間での集計結果を参照した方が恒常的に負荷の高いクエリを特定しやすくなるので、短期間のスパイク負荷を解決したい場合などを除き、集計期間は長めに設定すると良いでしょう。
なおPerformance Insightsでは無料枠で7日間パフォーマンス履歴を保存できます。逆にいうとそれ以上の期間履歴の保存するには追加課金が必要なので、履歴が足りないと思ったら課金するのも一手です。
クエリを叩いている箇所のうち、支配的な部分を特定する
Performance Insightsで高負荷クエリを特定しても、アプリケーション上の異なる箇所で同一のクエリを叩いている可能性があります。
この時、同一クエリを叩いている箇所を洗い出し、それぞれの経路でどの程度叩かれているかを集計しておきます。この集計によって頻繁にクエリを叩いている箇所を特定でき、そこから優先的に対応を入れていくことで、少ない労力で大きい効果を見込めます。
アプリケーション側からのクエリ呼び出しを集計する際、筆者は主にログやトレースを利用します。一度限りのアドホックな集計でよければ簡単なログでも問題ないのですが、頻繁に調査する場合はspanにタグを付与するなどして、クエリ呼び出し元となる箇所をいつでも集計可能にしておくと便利です。ちなみに高負荷クエリの集計に利用する性質上、ログ・トレースの量が増えやすいため、集計が不要になったらきちんと削除しましょう。
改善方針の決定
Performance SchemaやPerformance InsightsでDB負荷のボトルネックとなるクエリを発見したら、次は改善方針を決定します。MySQLに限らない一般的な内容も含みますが、筆者がDB負荷改善を行う際は主に以下の観点について考えるようにしています。
1. クエリ実行自体を無くす
不要なクエリはそもそも叩かないのが一番なので、Webサービスやシステムの仕様を満たす上で不要・冗長なクエリがないか調査します。「不要なクエリが実装されているなんておかしい」と思うかもしれませんが、過去必要だったクエリが幾度の変更を経て現在では不要になる、といったケースは十分考えられます。
2. クエリ実行回数を減らす
完全にクエリをなくすことができなくても、クエリ回数を減らすことができるかもしれません。N+1となっている箇所を一つのクエリに集約したり、インメモリにある取得済みの情報を利用できないかなどを確認します。
3.クエリ結果をキャッシュする
データの変更頻度が少ない（or無い）、あるいは仕様上取得するデータが最新であることが特に期待されていない場合、負荷の大きいクエリ結果のキャッシュを検討できます。ただしキャッシュレイヤーで使用するDB・ストレージに負荷が移るので、そちらの負荷も問題がないか確認します。
なおMySQL5.Xのクエリキャッシュに関してはクエリ文字列が完全に一致しないとヒットせず、テーブルデータの更新で内容を問わずキャッシュは無効化されない上、キャッシュ自体のオーバーヘッドもあるのでquery_cache_type=0で無効化した方が良いでしょう（MySQL5.6からはデフォルトで無効）。MySQL8.0以降はクエリキャッシュがサポートされなくなりました。
4.クエリ・インデックス・テーブル定義を改善する
既存の高負荷クエリの改善を検討します。まずはクエリそのものと実行計画を見て、どのようなインデックスが使用されているか、スキャンされているレコード数やフィルタリングの割合はどの程度か等を見つつ、原因となる箇所に当たりをつけていきます。
例：
- 実行クエリが利用できるインデックスがない、あるいは既存のインデックスで効率的に対象レコードを絞ることができていない。
- 実行コストの高くなりやすい構文を使用している。JOINで代替できる箇所でサブクエリを利用しているなど。
- IN句の引数やORの連結が多すぎて、高コストなIndex diveが発生してしまったりマルチレン。またオプティマイザの見積もり誤りによるフルスキャンが発生してしまっている。
- クエリが参照するインデックスがカバリングインデックスにならない。使用するインデックスノードに含まれない不要なカラムを取得しているために、セカンダリインデックス->プライマリインデックスへのアクセスが発生している。
- 可変長文字列カラムのデータサイズが大きい（769バイト以上）ために、オフページカラムとなってしまいディスクアクセスが頻発する。
- etc..
実行計画の取得は、可能であれば本番と同じ統計情報を使用するために本番環境で実施すると良いでしょう。ただし、MySQL 8.0.18 から導入されるEXPLAIN ANALYZE を使用する場合、実際にクエリが実行されるため注意が必要です（実行可能クエリはSELECTに限定されますが）。
5.クエリ実行を遅延させる
大量のクエリ発行が負荷に繋がっている場合、この方法の採用を検討できます。クエリ発行リクエストをキューイングして、実際のクエリ発行タイミングをデキュー側で調整します。レコードの更新操作でlast_insert_id()などの返戻値を必要としない、かつ実行タイミングは任意である場合に利用できます。
6. クエリ発行先DB筐体を分散する
DB筐体あたりのクエリ処理数を減らすために、Primary/Replicaなどの構成をとりクエリ発行先を分散しておくと良いでしょう。既にPrimary/Replica構成を採用しているのであれば、参照クエリ発行先のReplica DB筐体数を増やしたり、更新クエリ発行先のPrimary DB上テーブルのシャーディングなどを検討します。
接続先DBを増やす場合、当然ながら接続自体が不足していると効果を発揮しないのでMySQL・アプリケーション両者のコネクション設定の調整も合わせて行います。
7 . DB筐体あたりのリソースを増強する
6. と似ているのですが、単一DB筐体のCPUやメモリ、ストレージ容量などの増強が可能であればこちらも候補になり得ます。クラウドサービスを利用している場合、これらのリソース増強は即座に反映できる場合が多いので、他の改善・運用コストと比較して追加インフラコストが許容できるのであれば強力な手段になり得ます。
8 . DB設定を見直す
my.cnfなどのMySQL Serverの設定を見直してみます。MySQL8.0以降の場合、 innodb_dedicated_server によるInnoDBの変数自動設定で十分パフォーマンスが出るよう調整されるようです。ただし、計測の結果調整が必要であることが疑われる設定がある場合、現状のシステムに合致していない設定の可能性があるため、改めて調査する価値はあります。
どのような対応方針を採用するかを決定する際には、Performance Insights のイベント内訳が参考になります。例えばio/table/sql/handler が多いクエリの場合、特定のテーブルへのアクセス待ちが頻発していると仮定し、テーブルアクセス自体を回避するのが有効->キャッシュを入れる、クエリ回数を減らすといった判断ができます（もちろん、実際に仮説が正しいかどうかを別途検証する必要があります）。一方で特定のクエリが頻繁・長時間の行ロックやテーブルロックを実施している可能性もあるので、いくつかの可能性を仮定しつつ原因を絞り込んでいきます。
上記対応方針は実際に適用できるかどうかはケースバイケースであり、目的によって適用できる項目も変わってきますが、より良い案を採用できるようチェックリスト的に使用しています。
対応方針の評価
対応方針が決まったら、その対応によってきちんと利益が発生するかどうかを評価します。筆者がよく考える項目としては以下になります。
- 想定するコストはどの程度か。対応にどの程度工数がかかり、費用はどの程度かかるか。
- 想定するアウトカムはどの程度か。目的をどの程度達成でき、それによって得られる利益は何で、どのくらいか。
- 上記コストに対して、アウトカムが小さすぎないか。
- 切り戻しは容易か。想定と異なる結果が発生した場合、元の状態に戻すのにどの程度コストが必要か。
- etc…
アウトカムとコストの評価は特に重要で、改善方針の良し悪しを判断するのに利用することができるだけでなく、他のタスクとの優先順位を決定するのにも使えます。これらの評価は定性的になりやすく主観が入り込みやすいので、なるべく多くの関係者からレビューを受け組織として合理的かどうかを判断します。
負荷改善の文脈ではサービスの信頼性やユーザー体験の向上などが主なアウトカムになると思いますが、今採用しようとしている方針のコストが本当に結果に見合うかどうかは確認しておくと良いでしょう。
改善の実行 & 評価
実際に改善を実行し、受け入れ条件を満たしているかどうか評価します。満たしていない場合、どのような変化が発生したか、今回の修正を切り戻すかどうか、追加の修正を入れるかどうかなどのネクストアクションを決定します。
また今回の対応によって一時的に改善ができても、今後同様の問題が発生する可能性があります。そのため恒久対応も合わせて検討すると、同様の調査・改善対応を繰り返さずに済みます。例えば「改善方針の決定」でN+1のクエリに対して「2. クエリ実行回数を減らす」を適用し改善できた場合、アプリケーション側のCIで静的解析ツールによるN+1の自動チェックなどを検討できるでしょう。
上記のプロセスを繰り返して、目的を達成するまで改善を実施します。
なお実施したプロセスはドキュメントとしてまとめておくと、改善方法や結果をチームにシェアするのに後々役に立つのでおすすめです。
おわりに
今回は筆者がMySQL負荷改善に利用したプロセスについて紹介させていただきました。
上記改善プロセスではMySQL固有の観点だけでなく、筆者が一般的な改善において重要である思う観点、特に改善の効果・コストを評価する観点を、やや抽象的ですが盛り込んでいます。事業における改善活動ではビジネス上の利益が見込めなければいけない前提があるので、それをプロセス上で明示的に評価したいという意図があります。実際にこのプロセスを自身で使用したときも、個々の改善方針が事業上妥当な行いなのか検証するバリデータとしてこれらの観点が機能しました。
今後も改善活動を積み重ねていき、技術改善にもっと強いソフトウェアエンジニアなれるよう引き続き励んでいきたいと思います。
今回紹介した内容が、みなさんの改善活動にもお役に立てられれば幸いです。
