MySQL 最適化接続最適化

記事「MySQL の最適化: キャッシュの最適化」では、システムによってコンパイルされた変数値、または my.cnf で構成された変数値を示すために show variables が使用されるという非常に重要な概念について説明されています。ステータスの表示はステータス値と呼ばれ、現在のサービス インスタンスのステータス情報を表示するもので、動的に変化する値です。したがって、現在のMySQl操作が正常かどうかを観察するためによく使用されます。正常でない場合は、静的パラメータを調整することでMySQLのパフォーマンスを向上させることができます。したがって、これら 2 つの概念の違いを理解することが、その後のチューニングの基礎となります。

MySQL接続の最適化

以前、社内で MySQL に接続しようとした時に、接続できなかったことがありました。 DBAを見つけて原因を調査したところ、現在のMySQL接続数がいっぱいであることがわかりました。調整後、問題は解決しました。通常、接続数が多すぎるというエラーには 2 つの原因があります。1 つ目は、MySQL に接続している人が多すぎるために、接続数が不足していることです。 2 つ目は、max_connections 値が小さすぎることです。

1. 接続パラメータ（変数を表示）

mysql> '%connect%' のような変数を表示します。
+-------------------------------------------------+-----------------+
| 変数名 | 値 |
+-------------------------------------------------+-----------------+
| 文字セット接続 | utf8 |
| 照合接続 | utf8_general_ci |
| 接続タイムアウト | 10 |
| パスワードの有効期限が切れると切断 | オン |
| 初期化接続 | |
| 最大接続エラー数 | 100 |
| 最大接続数 | 151 |
| 最大ユーザー接続数 | 0 |
| パフォーマンス スキーマ セッション接続属性サイズ | 512 |
+-------------------------------------------------+-----------------+ 

max_connections 、MySQL サービス インスタンスが同時に受け入れることができる同時接続の最大数を指します。 MySQL は実際に、最大接続数を 1 つ増やすアルゴリズムをサポートしており、接続数が使い果たされた場合でも、スーパー管理者が管理のためにサーバーとの接続を確立できるようにします。

max_user_connections 、指定されたアカウントの同時接続の最大数を設定します。

max_connect_errors不正なホストが悪意を持って MySQL サーバーに接続し、発生したエラーの数が設定値に達すると、MySQL はこのホストからのすべての接続を解決します。ただし、flush hosts を実行するとクリアされます。

2. 接続ステータス（ステータスの表示）

注意すべき点は、変数値（変数を表示）は小文字で始まり、ステータス値（ステータスを表示）は大文字で始まることです。この区別は記憶と分類に役立ちます

mysql> '%connections%' のようなステータスを表示します。
+-----------------------------------+-------+
| 変数名 | 値 |
+-----------------------------------+-------+
| 接続エラー最大接続数 | 0 |
| 接続 | 197 |
| 最大使用接続数 | 2 |
+-----------------------------------+-------+

Connection_errors_max_connections MySQL の最大同時接続数がシステム変数 (show variables) の最大同時接続数より大きい場合、拒否数がこの変数に記録されます。 Connection_error_max_connections 値が比較的大きい場合、現在のシステムの同時実行性が比較的高いことを意味し、max_connections 値を増やすことを検討する必要があります。

Connections 、MySQL の起動以降に正常に確立された接続の数を示します。この値は累積されます。

Max_used_connections は、MySQL の起動以降に同時に行われた接続数の最大値を示します。この値が max_connections より大きい場合、システムは頻繁に高同時接続状態にあることを示しているため、同時接続の最大数を増やすことを検討する必要があります。

3. 接続スレッドパラメータ（スレッド変数とステータス）

mysql> 'thread%' のような変数を表示します。
+--------------------+----------------------------+
| 変数名 | 値 |
+--------------------+----------------------------+
| スレッドキャッシュサイズ | 9 |
| スレッド同時実行性 | 10 |
| thread_handling | 接続ごとに 1 つのスレッド |
| スレッドスタック | 262144 |
+--------------------+----------------------------+

thread_cache_size接続スレッド キャッシュの数を設定します。このキャッシュは、アイドル状態の接続スレッドをすぐに破棄するのではなく、接続プールにキャッシュする MySQL スレッド キャッシュ プールと同等です。新しい接続要求があった場合、接続プール内にアイドル接続があれば、それが直接使用されます。それ以外の場合は、スレッドを再作成する必要があります。スレッドの作成は、重要なシステム オーバーヘッドです。 MySQL のスレッド処理のこの部分は、Nginx のスレッド処理と似ています。後ほど Nginx のスレッド処理を紹介する際に、比較に使用します。

thread_handlingのデフォルト値は、one-thread-per-connection です。これは、リクエストが完了して接続が破棄されるかキャッシュ プールに保存されるまで、リクエストを処理するために各接続に対してスレッドが提供または作成されることを意味します。値が no-threads の場合、接続を処理するために 1 つのスレッドのみが提供されることを意味し、通常はスタンドアロン テストに使用されます。

thread_stack stackヒープを意味します。このブログから、プロセスとスレッドの両方に一意の ID があることがわかります。プロセス ID システムはプロセスの ID を維持し、スレッド ID は特定のスレッド ライブラリ領域によって維持されます。プロセスまたはスレッドが休止状態の場合、プロセス コンテキスト情報は、プログラムをすばやく起動できるように、メモリ内にプロセス コンテキスト情報を保存するための領域を開く必要があります。各MySQLスレッドに設定されるデフォルトのスタックサイズは、262144/1024=256kです。

スレッドのステータス情報を表示する

mysql> 'Thread%' のようなステータスを表示します。
+-------------------+------+
| 変数名 | 値 |
+-------------------+------+
| キャッシュされたスレッド | 1 |
| スレッド接続数 | 1 |
| 作成されたスレッド | 2 |
| 実行中のスレッド | 1 |
+-------------------+------+

Thread_cached現在のスレッドプール内のスレッド数

Thread_connected現在の接続数

Thread_cached : 接続に対して現在作成されているスレッドの数。この値が高すぎる場合は、threadcachesize を調整して、スレッド キャッシュ プールのサイズを調整できます。

Thred_runnint : 現在アクティブなスレッドの数。

接続要求スタック

MySQL が短時間に大量の接続要求を突然受信した場合、MySQL は時間内に処理できない接続要求をスタックに保存し、その後の MySQL による処理に備えます。 back_log パラメータはスタックのサイズを設定します。これは次のコマンドで表示できます。

mysql> 'back_log' のような変数を表示します。
+---------------+-------+
| 変数名 | 値 |
+---------------+-------+
| バックログ | 80 |
+---------------+-------+

接続異常

mysql> 'Aborted%' のようなステータスを表示します。
+------------------+-------+
| 変数名 | 値 |
+------------------+-------+
| 中止されたクライアント | 0 |
| 中止された接続 | 219 |
+------------------+-------+

Aborted_clients MySQL クライアントが異常終了した回数。

Aborted_connects MySQL サーバーへの接続に失敗した試行回数。

他の

mysql> 'Slow%' のようなステータスを表示します。
+---------------------+-------+
| 変数名 | 値 |
+---------------------+-------+
| 遅い起動スレッド | 0 |
| 遅いクエリ | 0 |
+---------------------+-------+

mysql> 'slow_launch_time' のような変数を表示します。
+------------------+-------+
| 変数名 | 値 |
+------------------+-------+
| 遅い起動時間 | 2 |
+------------------+-------+

Slow_lunch_threads スレッドの作成時間が長すぎて slow_launch_time の設定値を超えると記録されます。

Connection_error% を使用して、接続のエラー ステータス情報を表示できます。

mysql> 'Connection_error%' のようなステータスを表示します。
+-----------------------------------+-------+
| 変数名 | 値 |
+-----------------------------------+-------+
| 接続エラー受け入れ | 0 |
| 接続エラー内部 | 0 |
| 接続エラー最大接続数 | 0 |
| 接続エラー ピアアドレス | 0 |
| 接続エラー選択 | 0 |
| 接続エラー_tcpwrap | 0 |
+-----------------------------------+-------+

Connection_errors_peer_address MySQL クライアント IP アドレスの検索中に発生したエラーの数。