免責事項
この記事はGeir Hoydalsvik氏によるMySQL Server Blogの投稿「MySQL Connection Handling and Scaling」(2019/3/19)をユーザが翻訳したものであり、Oracle公式の文書ではありません。
この投稿では、MySQLのコネクション、ユーザースレッドおよびスケーリングについて取り扱います。MySQLがどのように動作するかをよりよく理解することで、アプリケーション開発者やシステム管理者が、トレードオフを踏まえた良い選択をできることでしょう。本記事ではコミュニティー版でコネクションがどのように動作するかについて述べますが、一方でスレッドプール、リソースグループ、あるいはコネクション多重化といった関連するテーマについては取り扱いません。
MySQLサーバー(mysqld)では単一OSプロセスで動作し、同時並行で発生するイベントが複数のスレッドで実行されます。MySQL自体には独自のスレッド実装はなく、OSのスレッドの実装に依存しています。ユーザーがデータベースに接続すると、ユーザースレッドがmysqld内部に作成され、ユーザーが切断するまで、このユーザースレッドがユーザーのクエリーを実行し、結果を返却します。
データベースに接続するユーザーが増えれば、ユーザースレッドはそれに応じて並列で実行されるようになります。すべてのユーザースレッドがあたかもそれだけであるかのように動作する場合、システム(MySQL)がよくスケールする、ということができます。しかし、どこかの時点では限界に達し、ユーザースレッドが増加しても有効ではなくなり、効率が悪くなります。
接続と切断
MySQLのコネクションはSQL標準ではセッションに対応します。クライアントがMySQLサーバーに接続すると、切断するまでは接続状態を維持します。図1にMySQLクライアントがMySQLサーバーに接続した際の挙動を記しています。
図1: 接続
クライアント(Clients): MySQLクライアントとはコマンドラインツールあるいはアプリケーションで、libmysqlclientライブラリあるいはMySQLコネクタを利用してMySQLクライアント-サーバープロトコルでMySQLサーバーと通信するものです。1つのマルチスレッドクライアントはサーバーに対して多くのコネクションをオープンできますが、ここでは単純化するために1クライアントは1コネクションであるとします。
コネクション要求(Connection Requests): MySQLクライアントはコネクション要求をMySQLサーバーに送ります。コネクション要求はサーバーの3306番ポートに送信される単なるTCP-IPのコネクション要求です。
レシーバースレッド(Receiver Thread): コネクション要求はキューイングされ、レシーバースレッドにより1つずつ処理されます。レシーバースレッドはユーザースレッドの作成のみを実施し、その後の処理はユーザースレッドにより処理されます。
スレッドキャッシュ(Thread Cache): レシーバースレッドは新しいOSスレッドを作成するか、あるいはスレッドキャッシュの中に既存の"フリー"のOSスレッドがあればそれを再利用します。OSスレッドの作成が高コストである場合スレッドキャッシュはコネクション速度の観点で重宝します。最近ではOSスレッドの作成は比較的低コストですので、もしかするとスレッドキャッシュは時代遅れとされるかもしれません。thread_cache_sizeのデフォルト値は 8 + (max_connections / 100) で計算され、滅多に変更されません。コネクション数が非常に少なかったり、多くなったりと変動があるケースではスレッドキャッシュを増やすと効果があるでしょう。
ユーザースレッド(User Thread): クライアント-サーバープロトコルを扱う- 例えば、最初のハンドシェイクパケットを返すのは、ユーザースレッドです。ユーザースレッドは対応するTHDを確保し初期化し、それから続けてcapability negotiationおよび認証を行います。この処理ではユーザーのクレデンシャルはTHDのセキュリティーコンテキストに保存されます。コネクションフェーズが全てうまくいけば、ユーザースレッドはコマンドフェーズに入ります。
THD: コネクションはTHDと呼ばれるデータ構造で表現されこれはコネクション確立時に作成され、コネクションが破棄される際に削除されます。ユーザーコネクションとTHDは常に1対1の関係があり、THDは複数コネクションで再利用されません。THDのサイズは 〜10Kであり、sql_class.hに定義されています。THDは実行状態の様々な側面を追跡するために利用される大きなデータ構造です。THD由来のメモリーはクエリー実行の間大きく増えますが、正確にどの程度増えるかはクエリー依存となります。メモリーを利用する際の計画をたてたいのであれば平均して1接続辺り〜10MBと見積もることをおすすめします。
図2: アクティブコネクション
図2はコマンドフェーズについてのものです。この例ではクライアントはサーバーに複数クエリーを発行し、複数の結果を何回かで受け取っています。一般的には複数の連続するステートメントはstart transactionとcommit/rollbackで囲うことができます。このケースではトランザクションコンテキストを保持する必要があります。オートコミットの場合は、それぞれのステートメントが単一トランザクションとして実行されます(各ステートメントがフルトランザクションコンテキストで構成されます)。これに加え、セッションコンテキストがあり、言い換えればセッションはセッション変数、ユーザー変数、一時テーブルを保持できます。このようにコンテキストがクエリー実行に関連するかぎり、あるコネクションのすべてのクエリーは同一のTHDを利用しなければなりません。
図3: 切断
図3はMySQLクライアントがMySQLサーバーに対して切断した際の挙動を表しています。クライアントはCOM_QUITコマンドを送りサーバーにソケットをクローズするよう命令します。どちらかでソケットをクローズした場合にも切断は発生します。切断の際に、ユーザースレッドは片付けられ、THDが開放され、スレッドキャッシュに空きスロットがあれば"サスペンド"状態として扱われます。空きスロットがなければ、ユーザースレッドは"終了"状態となります。
短寿命のコネクション (Short Lived Connections)
短期間の間のみオープンされる接続が短寿命のコネクション(short lived connection)です。典型的なのはPHPアプリケーションで、クライアントがコネクションをオープンし、シンプルなクエリーを実行し、コネクションを切断します。アーキテクチャ上、MySQLは新しいコネクションを高速で受け入れるにはとても優れており、図4により最大秒間80,000接続/切断の性能であることが確認できます。
図4: 単位時間あたりの接続 + クエリー + 切断
図4はMySQLの接続/切断性能を示します。テスト機はIntel(R) Xeon(R) e5-2699 v4 CPU、2CPUソケット、22 Cores-HT/ソケット、2.20GHz(Broadwell世代)です。SysbechクライアントとMySQLサーバーを同じコアセット上で動作させています。各Sysbenchスレッドはソケット経由で通信し、ポイントセレクトを発行し、切断、を繰り返します。データはメモリー上に収まります。グラフはクライアント数の変化に対するTPSを示し、8クライアントで20000、16クライアントで43000、32クライアントで73000、そして64クライアントでは80000となります。
MySQLはいつも接続/切断に関して比較的よく動作してきましたが、MySQL 5.6においてはFacebookの助けもあり大きく改善されました。Domas Mituzas氏と松信嘉範氏の投稿をご覧ください。MySQL 5.7ではいくつかの追加の改善がされています。
長寿命のコネクション
長寿命のコネクションとは"いつまでも"オープンされているコネクションのことです。例えば、ウェブサーバーかアプリケーションサーバーがMySQLサーバーに対して多数のコネクションをオープンしておりクライアント(ウェブ/アプリケーション・サーバー)が止まるまで維持しているもので、数ヶ月間に渡るかもしれません。
サーバーがクライアントに対し許可する最大同時コネクション数はmax_connectionsシステム変数によって決定され、これはユーザーが設定できます。コネクション数が上限に達すると、サーバーはアクティブなクライアントのうちいずれかが切断するまで新規のコネクションを受け付けません。図5で示されるように、MySQLサーバーはコネクションが維持されている間、1つの(THDをもつ)ユーザースレッドをもちます。
図5: 単一のMySQLサーバーに対する多数のコネクション
それではmax_connectionsの値はどのように設定すると一番良いのでしょうか？答えは"場合によりけり"です。主に、クライアントの負荷とMySQLが稼働しているハードウェア、の2つに依存します。
コネクションは多かれ少なかれビジーになりえます。クライアントがサーバーに対してたて続けにクエリーを送った場合、つまりクライアントが毎回結果を受け取り次第、新しいクエリーを送る場合、コネクションはとてもビジーになります。クライアントがサーバーに対してときどきクエリーを発行し、結果を受け取ってから次のクエリーが送られるまで長い間(アイドル期間)がある場合はビジーではありません。すべてのコネクションがビジーでかつ多数のコネクションがある場合、MySQLサーバーが高負荷であるといいます。
コネクションがビジーではない場合、追加のコネクションを受け付けられます。200コネクション以上のユーザー負荷があり、仮にサーバーインスタンスの限界性能が5000TPSだったとしましょう。全く同じサーバーがあれば10000コネクション以上に対して、同じ同時並行処理数(5000TPS)を扱えますが、コネクション数を増やしたところで同時並行処理数は増えません。10000コネクションになるとよりTHD用により多くのメモリが必要となり、ハードウェアを効率的に利用できなくなります。max_connectionsを10000に設定するならば、全体で5000TPSの性能限界を超えるクエリーが全てのコネクションから発行された場合、サーバーが過負荷になるリスクがあることに留意する必要があります。このケースではサーバーがスラッシングしはじめるでしょう。
最大負荷とは何でしょうか？また、どのようにして最大負荷に達したと知ることができるでしょうか？負荷を例えば次のようにしてテストする必要があります: クライアントコネクションを2並列からはじめてTPSとレイテンシーを計測し、クライアントコネクション数を2倍ずつ増やしながら計測します。最初はレイテンシーが一定である一方、TPSが増加するでしょう。どこかの時点でTPSが変わらなくなりレイテンシーが増加しはじめたら、これが最大負荷と最大クライアント数です。
MySQLが処理できる最大のユーザースレッドの同時並行数はいくつでしょうか？他の制約に達するより前にスレッドの同時並行数上限に達した場合の負荷を確認してみましょう。メインメモリー（バッファプール）上にあるデータを主キーで参照した場合の負荷(SysbenchのPOINT SELECT)はこの条件にぴったりです。MySQL/InnoDBの主キーはデータと一緒に格納されるため(クラスターインデックス)、point selectの場合MySQLはメモリー内のB-tree構造からレコードを探し結果を返すだけでよいのです。
スレッドの同時並行数について図示したいので48CPUコアのマシンを選びました。テスト機のCPUは Intel(R) Xeon(R) Platinum 8168、2CPUソケット、24コア ハイパースレッディング、2.7GHz(Skylake)です。同じCPUコアを持つサーバーでSysbenchのクライアント、およびMySQLサーバー(8.0.15)を稼働させています。
図6: SysbenchのPOINT_SELECT (データがメモリーに収まる場合)
図6はコネクション数(ユーザースレッド数)と全体の負荷を秒間トランザクション数の観点で見たもの(TPS)の関係を示しています。それぞれのクライアントはPOINT SELECTの負荷を連続して生成します。全体の負荷は最初はクライアントの同時並行数に対して線形に近い形で増加します。例えば、16接続では30万TPS、32接続では60万TPS、64接続では120万TPSといった具合です。128接続で最大の180万TPSに達します。128接続を超えると減少し始めます。いったい何が起きているのでしょうか？
図7: Sysbench POINT_SELECT (レイテンシー)
図7のクエリーに対するレイテンシーについて見てみましょう。1から64接続ではクエリーのレスポンス(レイテンシー)は約50マイクロ秒で一定です。128接続では70マイクロ秒、256接続では140マイクロ秒、512接続では300マイクロ秒と増加しています。
MySQLは128ユーザースレッドで最大効率に達し、このとき最大TPS(180万)で低いレイテンシー(70マイクロ秒)となります 。このようにして、ピーク時にコアあたりのユーザースレッド数は128/48 = 2.7で、これ以降は効率が悪くなります。ユーザースレッドを増やしてもレイテンシーが増加し、これが許容可能なレイテンシーかどうかはアプリケーション要件つまり定義されたSLAに依存し決まります。我々の例でいえば、全トランザクションのうち95%が210マイクロ秒であることがこれに該当し、恐らくほとんどのアプリケーションではこれで十分でしょう。これによりコアあたりのユーザースレッドが256/48=5.3であることがわかります。これまでの経験によると実CPUコア数の最大4倍のユーザースレッド、この例では4 x 48 = 196、とすることをおすすめします。
MySQLは必ずしもここで記したようにスケールしません。MySQL 5.6では読取り専用の負荷に対する大幅なスケーラビリティーの改善がありましたが、この効果をえるにはREAD-ONLY TRANSACTIONS機能とAUTO COMMITを利用している必要があります。MySQL 5.7では読取り専用トランザクションの自動判定、そしてメタデータロック周辺の競合(THR_LOCK、InnoDBのtrx_sysミューテックスおよびlock_sysミューテックス)が解消されました。これを掘り下げた歴史に興味がある方は、Dimitri氏の次のブログ投稿(1,2,3,4,5)を参照してください。
スレッドの同時並行数のボトルネックにあたる前に他のボトルネックにあたるとき一体何が起きているのでしょうか？1つの例を下の図8に記しています。図8は上の図6と同じSystenchのPOINT SELECTクエリーですが、今回はほとんどのケースでデータをディスクから読み込まなければいけないくらいデータ量が多いケースを示しています。この例では我々はIntel OptaneSSDを利用していますが、ディスクからメモリーへの帯域を飽和させられており、ユーザースレッドがSSDのディスクページがInnoDBバッファプールへの移動するのを待機しています。全体的な結果としては、各クエリがデータの待機により時間を費やしているため先程より低いTPS(180万に対し100万)となります。コネクション数を増やすとTPSはディスクキャパシティーが飽和するおよそ128-256コネクションまでは増加します。この時点ですべてのユーザースレッドはただ単にIOを待機しているため、ユーザースレッドを追加すると効率が悪くなります。
図8: Sysbench POINT_SELECT (IOバウンドの場合)
Dimitri氏の次の投稿はMySQL 8.0のIOスケーラビリティーの改善について記しており、これはInnoDBのfil_systemミューテックス周辺の競合を解消するものです。ディスクIOで競合が発生する状況下でTPSが低下(110万)する一方で、スレッドの同時並行数は上記のインメモリーのケースと同じように変化することを示しています。128ユーザースレッドまでは線形にスケールし、256ユーザースレッドで頭打ちとなり110万QPSに達します。
何がスレッドの同時並行数を制限するのか
スレッドは何かをOSスケジューラーにより決められた時分割スロットまで待つ必要ががない限り適切に命令を実行します。スレッドは3種類のものを待機する必要があります。ミューテックス、データベースのロック、IOです。
ミューテックス: ミューテックスは共有内部データ構造を保護します。すなわち、いかなるときも1スレッドだけが実行可能であることを保証する必要があるケースです。あるスレッドがミューテックスを保持していれば、他のスレッドはそれぞれの番が回ってくるまで一列になり待機しなければなりません。ミューテックスの利用は1つの実装上のテクニックであり、プログラム全体の正しさが維持される範囲においてプログラマーにより調整可能なものです。テクニックとしては、ロックを使わないアルゴリズムを利用したり、保護されたリソースをより細かい粒度に分解し、異なるスレッドが異なるミューテックスを要求するようにする(グローバルリソースの競合を削減する)ようなこともこれに含まれます。過去5〜10年に渡りMySQL開発者が実施したスケーラビリティーに関する活動の多くは、ミューテックスをよりうまく利用することに焦点をあててきました。
ロック: データベースのロックはある意味では全く同じですがデータベースのセマンティクスに結びついているため回避するのが難しくなります。(MySQL/InnoDBはMVCCによりロックをうまく回避しています)。データベースのロックは、データロック(SQL DMLにより発生)とメタデータロック(SQL DDLにより発生)に大別されます。行ロックのようなデータのロックは典型的にはあるスレッドにより更新されたデータを他のスレッドから読まれたり書き込まれたりしないようにデータを保護します。メタデータロックは典型的にはデータベーススキーマが同時に非互換な変更がされないように保護します。データベースのセマンティクスを維持することは性能やスケーラビリティーよりも重要なことであり、ミューテックスを削除するより難易度が高くなります。その結果としてロックによって引き起こされるスケーラビリティーのボトルネックは、OLTPのアプリケーションの設計レベル、すなわちデータベースのスキーマ設計とより良いクエリ設計により解消されることが多くあります。MySQL 8.0ではアプリケーション開発者がロックを避けるためのNO WAITとSKIP LOCKEDという興味深い新機能を提供しています。
ディスクおよびネットワークIO: IOは可能なときはいつでも最小化を試み、これができなくてもプリフェッチ、並列化、バッチなどにより可能な限り効率化を試みるものです。ある時点でほとんどのユーザースレッドがIOを必要とします。ユーザースレッドがIO待ちをする必要があるとき、OSは典型的にはスレッドを待機状態としCPUを他の待機スレッドに譲ります。これは新規スレッドが処理を進めることが可能でCPUを効率的に利用できる場合うまく動作します。しかし、例えばIO帯域が飽和する場合は、新規スレッドは潜在的にただIOを待機するだけで処理は何も進みません。したがってスレッドの同時並行数は図8で見たようにIOキャパシティーに制限されます。
スレッドがOSによりサスペンドされた場合はどうなるでしょうか？第1に何も進まなくなり、第2に他のスレッドが進まないようミューテックスあるいはロックを取得するでしょう。第3に再度起こされた際に、キャッシュアウトが発生し必要なデータを再読込する必要があるかもしれません。どこかの時点でスレッド数が増えても待機スレッドの数のキュー数を増やすだけで、システムはすぐ詰まるようになります。これを解決するにはmax_connectionsを制限することでユーザースレッドの数を制限し、システムが効率的に処理できるユーザースレッドに制限することです。
アプリケーション開発者の役割
アプリケーション開発者はシステム全体のアーキテクチャ、データベーススキーマ、データベースクエリーをコントロールできる場合もあります。しかしもしかするとより多くの場合で、アプリケーション開発者は既存のデータベース上にアプリケーションを開発するよう求められるでしょう。どちらのケースでもアプリケーション開発者はデータベースに発行されるクエリーに注意を払う必要があります。
古典的にはMySQLは、レスポンスタイム要件があるオンライントランザクション処理(OLTP)用途で利用されるのが典型的でした。データベースの許容レスポンスタイムはしばしばミリ秒単位で定められ、これは同時に実行が期待されるクエリーの種別を制限します(データ量とデータベーススキーマの構造の制限につながるかもしれません)。これはより複雑なクエリーであるものの実行頻度が少なくレスポンスタイム要件が緩和されているオンライン分析処理(OLAP)とは対照的です。
とりわけOLTPに対しては、アプリケーション開発者はあるSLAで定められたレスポンスタイム以内に実行可能かつ並列実行可能であるようにクエリーを設計しなければなりません。スケールしないワークロードを生成するのは大して難しくありません。例えば、多数のクライアントが並列で同一テーブルの同一行の更新のみを実施する場合がこれに該当します(代替となる設計についてはこちらを参照してください)。
結論
- MySQLは多くのクライアントに対するデータベースへの高頻度の接続/切断処理がとても得意で、秒間80000接続/切断くらいまで可能
- MySQLはマルチコアCPUで良くスケールし、48CPUコアでは主キーの参照に対し最大秒間200万のクエリーを処理可能
- 経験則: 最大コネクション数 = 利用できるCPUコアの4倍
- ユーザー負荷に応じて効率的なコネクション利用は変化し、スレッド以外の他の箇所にボトルネックがある場合はCPUコア数より少ないコネクション数しか有用でなくなる可能性がある
- TPSが増加せずレイテンシーが増加し始めるまでコネクション数を倍々にしてみることで負荷をチェック
MySQLを利用してくれてありがとう！
