MySQLのクライアント接続の概要、スレッド・コネクションに関する設定値、ステータス、チューニング方法についてまとめました。
※目次をクリックすると目次の下部にコンテンツが表示されます。
・その接続のクエリーは、その接続用のスレッド内で実行される。
・その接続用のスレッドは、一つのコアまたはCPU上で実行される。
・サーバーはスレッドをキャッシュするので、クライアントが接続するたびにスレッドを生成・削除する必要はない。
・サーバーが待機しているネットワークインタフェース上でクライアントの接続要求を処理。
・1つのマネージャースレッドがTCP/IP接続要求を処理。
・Unixでは、このマネージャースレッドはUnixソケットファイルの接続要求も処理。
・Windowsでは、1つのマネージャースレッドが共有メモリーの接続要求を処理し、別のマネージャースレッドが名前付きパイプの接続要求を処理。
２）接続マネージャスレッドとキャッシュ
・各クライアント接続をその接続の認証および要求を処理する専用スレッドに関連付ける。
必要な場合には新しいスレッドを作成するが、なるべく回避するために、まずスレッドキャッシュを調べて接続に使用できるスレッドが含まれているかどうかを確認する。
・接続が終了すると、スレッドキャッシュが満杯でない場合にそのスレッドはスレッドキャッシュに返す。
３）接続スレッドモデルの負荷
・現在接続しているクライアントと同数のスレッドが存在するので、多数の接続を処理する場合、スレッドの作成と破棄の負荷が大きくなる。
・スタック領域などのサーバーリソースとカーネルリソースが各スレッドに必要になり、多数の同時接続に対応するには、スレッドあたりのスタックサイズは小さく保つ必要があり、結果として小さくなりすぎるか、あるいはサーバーで大量のメモリーを消費することになりる。
ほかのリソースを使い果たす可能性もあり、スケジューリングのオーバーヘッドがかなり大きくなる場合がある。
・MySQLはマルチスレッドサービスで、クライアントが接続するたびに新しいスレッドが生成される。
大量の接続があるサーバーだとこのスレッドの生成、消去に多くの時間を費やす事になってしまう。
この影響を軽減する仕組みとして既存のスレッドを再利用するスレッドキャッシュがある。
・クライアントが接続するとき、キャッシュ内に空きスレッドがある場合はそれを使用し、無い場合は新しいスレッドを生成する。
クライアントが接続を閉じるとそのスレッドはキャッシュに戻される。
・スレッドキャッシュのサイズは、”thread_cache_size”で設定できる。
デフォルトは0でスレッドキャッシュが無効になっている。
２）コネクションに関する設定値、ステータス、チューニング
mysql> show variables like '%connections'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | max_connections | 100 | | max_user_connections | 0 | +----------------------+-------+ mysql> show status like '%connections'; +-----------------------------------+-------+ | Variable_name | Value | +-----------------------------------+-------+ | Connection_errors_max_connections | 0 | | Connections | 3 | | Max_used_connections | 2 | +-----------------------------------+-------+
①max_connections
・サーバが許容可能な最大同時クライアントコネクション数。多すぎるとメモリを消費しすぎる可能性がある。
・多すぎる場合の参照情報
http://dev.mysql.com/doc/refman/5.1/en/too-many-connections.html
②Connections
MySQLサーバーに接続を試みた（成功、失敗を問わず）回数。
③Max_used_connections
・サーバー開始以降の最大同時接続数。
・max_connectionsの設定値と合わせてチェックする。
３）スレッドに関する設定値、ステータス
①設定値
mysql> show variables like 'thread%'; +--------------------+---------------------------+ | Variable_name | Value | +--------------------+---------------------------+ | thread_cache_size | 9 | | thread_concurrency | 10 | | thread_handling | one-thread-per-connection | | thread_stack | 196608 | +--------------------+---------------------------+
②ステータス
キャッシュ内のスレッド数、およびキャッシュから取得できなかったため作成されたスレッドの数を監視するには、Threads_cachedおよびThreads_createdステータス変数を監視する。
mysql> SHOW GLOBAL STATUS LIKE 'thread%'; +-------------------+--------+ | Variable_name | Value | +-------------------+--------+ | Threads_cached | 3 | | Threads_connected | 15 | | Threads_created | 178703 | | Threads_running | 3 | +-------------------+--------+
・Threads_cached
現在キャッシュ内にあるスレッドの数
・Threads_connected
現在オープンしている接続の数。
・Threads_created
MySQLがリスタートしてからスレッドが新規に生成された数。
・Threads_running
オープンしている接続の中で実際に実行中（スリープや待機中ではなく）のスレッドの数。
４）スレッドのチューニング
①thread_cache_size
・スレッドをコネクションの切断後にもキャッシュしておく数。
・デフォルト値は0(キャッシュなし)で、この場合、スレッドは新しい接続ごとにセットアップされ、接続の終了時に破棄される。
・thread_cache_sizeをNに設定すると、N個のアクティブでない接続スレッドをキャッシュできる。関連付けられたクライアント接続が終了すると、接続スレッドはアクティブでなくなる。
・一般的にはmax_connections/3。
②Threads_created
・コネクションを操作するために生成されたスレッドの数。
・”Threads_created”/”Connections”でキャッシュミス率を見積もる事が出来る。
・低い数値であるべきで、大きい場合はthread_cache_sizeを増やす事を検討する。
・Threads_createdの値の増加率が高い場合は（MySQLサーバーリスタート後の積算値なので注意）、新規にスレッドが多く生成されている事になるのでスレッドキャッシュの設定値を増加することを検討する。
③チューニング時の注意点
・サーバーの起動時または実行時にmax_connectionsを設定して、同時に接続できるクライアントの最大数を制御することができる。
・スレッドスタックが小さすぎると、サーバーで処理できるSQLステートメントの複雑さ、ストアドプロシージャーの再帰の深さなど、メモリーを大量に消費する処理が制限される。
各スレッドにNバイトのスタックサイズを設定するには、サーバーを–thread_stack=Nで起動する。