前回のあらすじ
- スキーマ分離設計のDB（テナント毎に独立したスキーマを持つDB）でサービス規模が拡大すると、スキーマ数の増加に由来するオーバーヘッドが無視できないものになる
- 次はパラメータチューニングなどで何とか延命できないか試してみたい
はじめに
前回の負荷試験によって、弊社サービスは600テナントを超えたあたりから、データベースの急激な性能劣化を起こすリスクが高いことが判明しました。長期的には根本的な構成の見直しを行うとして、パラメータチューニングなどでデッドラインを後ろにずらせるのであれば、それはそれでありがたいです。
よって、今回の負荷試験の目的は、チューニングによってアーキテクチャ改善をどの程度後ろ倒しにできるかを検討することでした（過去形）。
結論を申し上げると、弊社のケースではパラメータチューニングによって延命を図れる見込みは薄いことが判明しました。ただし、調査の過程で、前回の結果の解釈の誤りを発見したり、新たな条件で見えてきたスキーマ分離の定性的な特性を発見したりと、それなりに実りのある結果が得られたので、再び報告させていただきたいと思います。
追加試験の結果報告
真のボトルネックはwait/io/table/sql/handlerだった
wait/io/table/sql/handlerは、ストレージエンジン層における行レベルの操作（読み取り、挿入、更新、削除など）に対して、SQL層が費やした待機時間を計測するイベントです。一般には物理I/O、行レベルのロック待ちなどが主原因になります。
前回の結果ではwait/synch/mutex/innodb/dict_sys_mutexがボトルネックになると誤った結論をつけてしまいましたが、これは暖機運転（ウォームアップ）不足による一時的な現象でした。以下は暖機運転完了前後の待機時間の内訳を、PeformanceInsiteの平均アクティブセッション(AAS)のグラフで確認したものです。
これを見るとvCPU数を大幅にオーバーしてwait/synch/mutex/innodb/dict_sys_mutexの待機イベントが支配的になっている時間（左側: 暖機運転中）と、vCPU数以下の範囲内でwait/io/table/sql/handlerが支配的になっている時間（右側: 暖機運転完了）がハッキリと分かれていることが確認できます。wait/synch/mutex/innodb/dict_sys_mutexはデータディクショナリへのアクセス競合でしたが、必要なメタデータがすべてメモリに乗り切れば、基本的に競合は発生しません。よって、今回の測定の条件下では、wait/synch/mutex/innodb/dict_sys_mutexが発生している場合は暖機運転が十分でないと言えます。
また、このようなwait/synch/mutex/innodb/dict_sys_mutexの変化は、今回の測定条件内ではtable_definition_cache（テーブル定義のメタデータを載せるキャッシュ）などのキャッシュサイズが十分に足りていることを示唆しています。
以降の測定は暖機運転を十分に行い、この待機イベントの変化を確認してから本測定を行いました。
なお、今回の検証では最終的にこのwait/io/table/sql/handlerのボトルネックを解消することはできませんでしたが、高並列・高QPSの負荷環境においては、ストレージエンジンが膨大なリクエストを処理する上で避けられない現象であると解釈しています。wait/io/table/sql/handlerの待機時間の内訳を、クエリの種別ごとに分けたものが以下の画像です。負荷を構成しているクエリの比率がそのまま待機時間の比率になっていました。したがって、特定のスロークエリがこれ発生させているわけではないことがわかります。
パラメータチューニングについて
チューニング（インスタンスサイズなどの変更も含む）を試みた項目について、端的に結果をまとめていきます。結論、今回の測定条件下では、パフォーマンスの改善はほとんどみられませんでした。
table_definition_cache & table_open_cache
table_definition_cache は、テーブルの定義情報（メタデータ）を保持するグローバルなキャッシュです。データディクショナリにアクセスする代わりにこのキャッシュを参照することで、テーブルの定義参照処理を高速化することができます。
table_open_cacheは、各スレッドがテーブルにアクセスする際に使用するオブジェクトを保持するキャッシュです。テーブルを物理的にオープンする処理は CPU コストが高いため、このキャッシュに保存されたオブジェクトを再利用することで、オーバーヘッドを劇的に低減できます。
前回の調査結果から、メタデータへのアクセス待機がボトルネックであると予想していたため、キャッシュサイズを大きくすることで改善が見込めると思い、これらのパラメータについて調査を行いました。
チューニングが有効でなかった理由は、先ほどの暖機運転の議論でも述べた通り、今回の測定においてはキャッシュサイズは最初から十分足りていたためです。キャッシュヒット率も確認しましたが、99.555%とほとんどキャッシュミスは発生していませんでした。前回も述べたとおり、負荷は十数種類のパターンのクエリで作っており、参照テーブルも数種類にとどまります。そのため、メモリの使い方は本番環境の方が圧倒的にハードであり、今回の測定ではメモリへの負荷や改善策を評価することができません。もしも本番環境で頻繁にキャッシュミスが発生していた場合には、これらのパラメータのチューニングはパフォーマンスを改善する上で非常に有効になります。
innodb_sync_array_size
innodb_sync_array_sizeは、内部同期用の配列サイズを調整するパラメータで、CPUコア数の多い環境で値を大きくすると、複数のスレッドが内部ラッチを取り合う際の競合wait/synch/mutex/innodb/sync_array_mutexを緩和し、スケーラビリティを向上させることができます。
このパラメータを増やすと、待機中スレッドの多いワークロードの同時実行性が高まるというのが通説なので、調査を行いました。
チューニングが有効でなかった理由はシンプルで、wait/synch/mutex/innodb/sync_array_mutexが発生していなかったので調整する必要がなかったというものです。
インスタンスサイズを大きくしてみる
これまでの測定では2xlargeを使用していましたが、これを4xlargeにスケールアップした際に性能が改善するかを調査しました。クライアント側は24並列・各スレッドは500QPSの設定で負荷をかけました。以下は測定結果の一部です。
| インスタンス | TotalQPS | QueryCount | P99[μs] | Avg[μs] |
|---|---|---|---|---|
| r8g.2xlarge | 8450 | 15,210,198 | 4.44E+03 | 2.36E+03 |
| r8g.4xlarge | 9233 | 16,618,969 | 3.15E+03 | 2.06E+03 |
4xlargeの方が全体的に性能が改善しているのがわかります。しかし、2xlarge -> 4xlargeではCPU、メモリ共に2倍になっているにも関わらず、性能の改善はQPS換算でせいぜい10％程度でした。これは費用対効果としては非常に効率が悪いです。
この理由は、CPUを使用しているセッションの内訳で説明できます。
両方とも主要な待機はwait/io/table/sql/handlerです。4xlargeの方はCPUが2倍になっているため縦軸の縮尺が異なりますが、その絶対値はほとんど変わっていないことがわかります。2xlarge時点でCPUはボトルネックではなかった（CPU数を超えたアクティブセッションが発生していなかった）ため、4xlargeに変更してCPUが増えても、目に見えた性能改善はできなかったと解釈できます。逆に、このグラフでCPU数を超えて待機しているセッションが多くなった場合は、CPUの数を増やすことで大きな性能の改善が期待できます。
したがって、インスタンスのスペックアップは、必ずしも限界を迎えた際の応急処置として使えるとは限らないと言えます。
一定高負荷下でのスキーマ数によるパフォーマンスの変化
Aurora MySQLのパフォーマンスとスキーマ数の関係をより掘り下げて調べてみました。前回とは以下の点を変更し、150 ~ 1200スキーマにて再測定を行いました。
- クライアントの負荷設定を固定する（24並列 * 500QPS = 12000TotalQPS）
- 十分な暖機運転を行いキャッシュに乗り切ったことを確認してから本測定を行う
測定結果を以下にまとめます。かなり直感とは異なる結果になりました。
なんと、600スキーマが最もスループットが高く、スキーマ数が増えた時だけではなく、少なくなった時にも性能の劣化が見て取れます。
そして150, 600, 1200スキーマでの測定時のAASのグラフは以下のようになっていました。色は異なりますが、支配的となっているのはすべてwait/io/table/sql/handlerです。
600スキーマでの測定で最も平均AASが少なくなっています。また、1200スキーマに加えて、150スキーマでの測定でもwait/io/table/sql/handlerの待機時間が増加しています。
150スキーマで性能が劣化する理由
150スキーマ構成では、600スキーマ構成と比較してテーブルあたりのアクセス密度が4倍になります。たとえスキーマが分かれていても、InnoDB内部の管理用ハッシュテーブルやラッチ（排他制御）はインスタンス全体で共有、あるいは少数のパーティションで管理されています。
150スキーマでは管理対象が少ない分、特定の管理パーティションにアクセスが集中する「ホットスポット」が発生しやすく、内部的な順番待ちが頻発します。この微細な足止めが積み重なり、結果としてストレージエンジン層の処理時間であるwait/io/table/sql/handlerを引き延ばしていると考えられます。満遍なく一定の遅延が発生していることも、この説を裏付けます。
1200スキーマで性能が劣化する理由
一方、1200スキーマ構成での劣化は、150スキーマの時とは逆に「管理対象の膨大さ」によるオーバーヘッドが原因であると考えられます。
今回の測定ではキャッシュヒット率が99.5%を超えており、ディスクI/Oの影響は無視できます。しかし、これほど大量のスキーマが存在すると、InnoDBが高速化のために生成する「アダプティブハッシュインデックス（AHI）」などの管理データ自体が巨大化します。この巨大化したデータの中から目的のデータを探し出す際、たとえメモリ上であっても管理パーティションの奪い合いが発生します。この様子はwait/synch/sxlock/innodb/hash_table_locksの待機時間(茶色)として現れており、これに比例してwait/io/table/sql/handlerが増加していることがわかります。
つまり、分散のメリットよりも、巨大なリソースを管理・検索するコストが上回ってしまった状態と言えます。
逆に言えば、適切な範囲内であればスキーマを分離して負荷を分散すること（パーティショニング）によってパフォーマンスが向上するケースもあると言えます。（これは前回時点では認識していなかったスキーマ分離のメリットですね）
なお、今回の測定はクエリの種類とテーブルを絞り、メモリ負荷がスキーマ数に対して非常に少なくなるような条件で行ってます。実際に大量スキーマのテーブルに対して満遍なくアクセスが発生する場合は、wait/synch/mutex/innodb/dict_sys_mutex（データディクショナリへのアクセス競合）などが支配的になることも十分に考えられます。
負荷試験を行う上で注意したいこと
負荷試験において最も注意しなければならないのは、「得られた数値を絶対視し、誤った解釈をしてしまうこと」です。今回の測定を通して見えてきた、試験設計と結果評価における注意点をまとめます。
本番環境と試験環境の違いを考える
前回、今回の負荷試験は、 上位のものとはいえ、使用するクエリやアクセスするテーブルを絞った環境にて行ったものになります。また、アクセスの並列数も24並列と実際のワークロードに比べると少ないものであり、1スレッドあたりの負荷を上げて総量を補っているとはいえ、本番環境を再現しているとは言い難いです。
よって、今回は以下のような点に注意して結果を評価しています。
- 「キャッシュ不足」は評価できても、「キャッシュ十分」とは評価できない
- 負荷試験はあくまでよく使われている一部のクエリ、テーブルのみをサンプリングしている。使用されないテーブルはキャッシュに乗らない
- 結果は鵜呑みにするのではなく、定性的に再解釈する
- 例えば負荷の総量が同じでも、1並列でかける負荷と100並列でかける場合ではDB内部の排他制御は全く異なるため、結果の数値をそのまま受け取ってはいけない。数値をそのまま本番のキャパシティとして解釈するのではなく、振る舞いの定性的な変化を読み取ることに主眼を置くべき
負荷試験には、条件設定によって評価できるものと評価できないものがあるということです。また、数値をそのままの状態で受け取ることもミスリードを生む危険があります。このような点に注意して試験設計、結果評価を行いましょう。
測定の分散について
以下は全く同じ負荷設定で、数時間以内に測定した結果を比較したものです。サンプルは少ないですが、QPS換算で1%程度の分散におさまっています。
| TotalQPS | QueryCount | P99[μs] | Avg[μs] |
|---|---|---|---|
| 8554 | 15,398,029 | 4.73E+03 | 2.34E+03 |
| 8459 | 15,226,641 | 4.49E+03 | 2.37E+03 |
| 8460 | 15,228,216 | 4.32E+03 | 2.36E+03 |
対してこちらは全く同じ負荷設定で、日付を跨いで測定した結果を比較したものです。
| TotalQPS | QueryCount | P99[μs] | Avg[μs] |
|---|---|---|---|
| 9118 | 16,412,667 | 4.60E+03 | 2.09E+03 |
| 8450 | 15,210,198 | 4.44E+03 | 2.36E+03 |
なんと、QPS換算で7.3%程度の差が出てしまっています。マネージドサービスゆえの不可避な外部要因（AWSの帯域や近隣インスタンスの負荷）によるものだと思っていますが、この結果は、測定そのものの分散よりも時間帯による分散の方が遥かに大きなことを示唆しており、最低でも7%以上の分散があることがわかります。
つまり、この測定では「5%程度の性能改善」を論じても意味がないわけです。その数値は誤差の範囲に埋もれてしまうからです。結果を数値的に求めたい場合は、測定自体の誤差がどの程度なのかを評価する必要があり、それができない限りは定量的な評価は難しいということです。間違っても、1回だけ測定して「こんな数値が取れました！」という結果の受け取り方はしないようにしましょう。
おわりに
前回記事の内容と合わせて本格的な負荷試験を行い、当初の目的であった現行アーキテクチャのデッドラインを見積もるという目的は無事達成できました。しかし、目的達成以上に、その試行錯誤の過程から多くのことを学ぶことができたと思います。
「スキーマ数が増えれば管理コストで遅くなる」という一般論は知っていても、実際に手を動かして測定を行い、予想と異なる振る舞いに悩み、考え抜いたことで、よりMySQLに関する理解は深まりました。そして、「推論と検証を繰り返すことでブラックボックスを一つずつ明らかにしていく」というプロセスそのものが、エンジニアにとって何よりも大切な経験であり、自信にもつながることを再確認できました。
本記事の試行錯誤の過程が、これから負荷試験に挑む皆様にとって、一歩を踏み出すための参考になれば幸いです。
MNTSQ株式会社 SRE 西室
