MySQLサーバのスレッド数を表示する方法の詳細な説明

この記事では、例を使用して、MySQL サーバーのスレッド数を表示する方法について説明します。ご参考までに、詳細は以下の通りです。

mysql 再起動コマンド:

/etc/init.d/mysql を再起動

MySQL サーバーが正常かつスムーズに実行されるためには、MySQL サーバーのスレッド数が適切な範囲内にある必要があります。 Threads_created は作成されたスレッドの数を示します。Threads_created を表示することで、MySQL サーバーのプロセスステータスを確認できます。

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

MySQL サーバー構成ファイルで thread_cache_size を設定すると、クライアントが切断されたときに、このクライアントを処理しているサーバーのスレッドは破棄されるのではなく、次のクライアントに応答するためにキャッシュされます (キャッシュ数が上限に達していない場合)。

Threads_created は作成されたスレッドの数を示します。Threads_created の値が大きすぎる場合、MySQL サーバーがスレッドを作成しており、これもリソースを消費していることを意味します。構成ファイルで thread_cache_size 値を適切に増やし、サーバーにクエリを実行できます。

thread_cache_size 設定:

mysql> 'thread_cache_size' のような変数を表示します。
+-------------------+------+
| 変数名 | 値 |
+-------------------+------+
| スレッドキャッシュサイズ | 64 |
+-------------------+------+

例のサーバーはまだ非常に正常です。

MySQL接続数に関連するいくつかのパラメータを分析する

MySQL 変数とステータスは、Oracle の spfile や v$ テーブルと同様に、管理とメンテナンスのための強力なツールです。

MySQL は、最大接続数 max_connections などのシステム変数を通じて多くの構成情報を記録します。

mysql> '%connect%' のような変数を表示します。
+--------------------------+-----------------+
| 変数名 | 値 |
+--------------------------+-----------------+
| 文字セット接続 | utf8 |
| 照合接続 | utf8_general_ci |
| 接続タイムアウト | 10 |
| init_connect | 名前の設定 utf8 |
| 最大接続エラー数 | 10 |
| 最大接続数 | 200 |
| 最大ユーザー接続数 | 0 |
+--------------------------+-----------------+
セット内の行数は 7 です (0.00 秒)

このパラメータは、同時に接続できるクライアントの数を示します。バージョン 5.1 のデフォルト値は 151 です。実際にサポートされる接続数は、この値に 1 を加えた 152 です。これは、システム管理者がログインして情報を表示するために 1 つの接続が予約されているためです。このパラメータのサイズは、使用するプラットフォームでサポートされているスレッドライブラリの数 (Windows では最大 2048 までしかサポートされません)、サーバー構成 (特にメモリサイズ)、各接続で占有されるリソースの量 (メモリと負荷)、システムに必要な応答時間など、多くの要因に基づいて考慮する必要があります。一般的に、Linux システムでは数百の同時接続をサポートしても問題ありません。このパラメータは、グローバルスコープまたはセッションスコープで変更できます。

mysql> グローバルmax_connectionsを151に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> '%connect%' のような変数を表示します。
+--------------------------+-----------------+
| 変数名 | 値 |
+--------------------------+-----------------+
| 文字セット接続 | utf8 |
| 照合接続 | utf8_general_ci |
| 接続タイムアウト | 10 |
| init_connect | 名前の設定 utf8 |
| 最大接続エラー数 | 10 |
| 最大接続数 | 151 |
| 最大ユーザー接続数 | 0 |
+--------------------------+-----------------+
セット内の行数は 7 です (0.00 秒)

ただし、接続数の増加は多くの連鎖反応を引き起こし、その結果生じる悪影響は実際には回避する必要があることに注意する必要があります。

まず、ステータスの出力を見てみましょう。

mysql> ステータス
--------------
mysql Ver 14.14 Distrib 5.1.49、readline 5.1 を使用する pc-linux-gnu (i686) 用
接続ID: 255260
現在のデータベース: mysql
現在のユーザー: root@localhost
SSL: 使用されていません
現在のページャ: stdout
出力ファイルの使用: ''
区切り文字の使用: ;
サーバーバージョン: 5.1.49-log MySQL コミュニティサーバー (GPL)
プロトコルバージョン: 10
接続: UNIX ソケット経由のローカルホスト
サーバー文字セット: utf8
Db 文字セット: utf8
クライアントの文字セット: utf8
接続文字セット: utf8
UNIX ソケット: /var/lib/mysql/mysql.sock
稼働時間: 161 日 3 時間 42 分 38 秒
スレッド: 14 質問: 160655492 遅いクエリ: 71 オープン: 8124 フラッシュテーブル: 3 オープンテーブル: 64 1秒あたりの平均クエリ数: 11.538
--------------

ここでは、 Open tables出力が 64 になっています。これは、現在のデータベースで開かれているテーブルの数が 64 であることを意味します。この 64 は実際の 64 テーブルではないことに注意してください。これは、MySQL がマルチスレッドシステムであり、複数の異なる同時接続で同じテーブルが開かれる可能性があるためです。競合を回避するには、この情報を保存するために、異なる接続セッションに独立したメモリ領域を割り当てる必要があります。したがって、接続数が増加すると、MySQL に必要なファイル記述子の数も増加します。さらに、MyISAM テーブルの場合、共有インデックスファイル記述子が作成されます。

MySQL データベースレベルでは、同時に開くことができるテーブルの数と使用されるファイル記述子を決定するシステムパラメータ (table_open_cache、max_tmp_tables、open_files_limit) がいくつかあります。

mysql> 'table_open%' のような変数を表示します。
+------------------+-------+
| 変数名 | 値 |
+------------------+-------+
| テーブルオープンキャッシュ | 64 |
+------------------+-------+
セット内の 1 行 (0.00 秒)

ここでの table_open_cache パラメータは 64 です。これは、すべての MySQL スレッドが同時に 64 個のテーブルを開くことができることを意味します。開いているテーブル数のシステムの履歴レコードを収集し、このパラメータと比較して、このパラメータのサイズを増やすかどうかを決定できます。現在開いているテーブルの数を確認する方法の 1 つは、上記のstatusコマンドを使用することです。もう 1 つの方法は、このシステム変数の値を直接照会することです。

mysql> 'open%' のようなステータスを表示します。
+--------------------------+-------+
| 変数名 | 値 |
+--------------------------+-------+
| 開いているファイル | 3 |
| オープンストリーム | 0 |
| テーブル定義を開く | 8 |
| オープンテーブル | 8 |
| 開いたファイル | 91768 |
| オープンされたテーブル定義 | 0 |
| オープンしたテーブル | 0 |
+--------------------------+-------+
セット内の行数は 7 です (0.00 秒)
mysql> 'open%' のようなグローバル ステータスを表示します。
+--------------------------+-------+
| 変数名 | 値 |
+--------------------------+-------+
| 開いているファイル | 3 |
| オープンストリーム | 0 |
| テーブル定義を開く | 10 |
| オープンテーブル | 11 |
| 開いたファイル | 91791 |
| オープンされたテーブル定義 | 1211 |
| オープンしたテーブル | 8158 |
+--------------------------+-------+
セット内の行数は 7 です (0.00 秒)

ここで、Open_tables は現在開いているテーブルの数です。現在開いているテーブルは、flush tables コマンドを使用して閉じることができます。グローバルに表示される Opened_tables は、履歴の累積値です。この値が大きすぎる場合、およびflush tablesコマンドが頻繁に実行されない場合は、table_open_cache パラメータのサイズを増やすことを検討してください。

次に、max_tmp_tables パラメータを確認します。

mysql> 'max_tmp%' のような変数を表示します。
+----------------+-------+
| 変数名 | 値 |
+----------------+-------+
| 最大tmpテーブル数 | 32 |
+----------------+-------+
セット内の 1 行 (0.00 秒)

このパラメータは、単一のクライアント接続で開くことができる一時テーブルの数を指定します。現在開いている一時テーブルの情報を表示します。

mysql> '%tmp%table%' のようなグローバル ステータスを表示します。
+-------------------------+-------+
| 変数名 | 値 |
+-------------------------+-------+
| tmp_disk_tables が作成されました | 10478 |
| 作成された tmp テーブル | 25860 |
+-------------------------+-------+
セット内の 2 行 (0.00 秒)

これら 2 つの値を比較して、一時テーブルを作成する場所を決定することもできます。一般的に、BLOB 列と TEXT 列が選択されている場合、Group by および Distinct ステートメントのデータ量が 512 バイトを超える場合、または union select の特定の列のデータが 512 バイトを超える場合は、一時テーブルがディスク上に直接作成されます。また、メモリ内の一時テーブルが大きくなると、MySQL によって自動的にディスクに転送されることもあります (tmp_table_size および max_heap_table_size パラメータによって決定)。

元の議論を続けると、table_open_cache または max_tmp_tables パラメータのサイズを増やすと、オペレーティングシステムの観点から、mysqld プロセスが使用する必要があるファイル記述子の数もそれに応じて増加します。これは、open_files_limit パラメータによって制御されます。ただし、このパラメータは OS によって制限されるため、設定した値が必ずしも有効になるとは限りません。 OS によって MySQL によるこの値の変更が制限されている場合は、0 に設定します。専用の MySQL サーバーの場合は、通常、この値をできるだけ大きく設定する必要があります。つまり、「開いているファイルが多すぎます」というエラーが報告されない最大値に設定して、問題を完全に解決できるようにします。オペレーティングシステムが十分なファイル記述子を割り当てることができない場合、mysqld プロセスはエラーログに警告メッセージを記録します。

mysql> show variables like 'open_files%';+------------------+-------+| Variable_name | Value |+------------------+-------+| open_files_limit | 1024 |+------------------+-------+セット内の行が 1 個 (0.00 秒)
mysql> 'open_files%' のような変数を表示します。
+------------------+-------+
| 変数名 | 値 |
+------------------+-------+
| オープンファイル制限 | 1024 |
+------------------+-------+
セット内の 1 行 (0.00 秒)

それに応じて、現在のファイルオープン情報と履歴ファイルオープン情報を記録する 2 つのステータス変数があります。

mysql> '%open%file%' のようなグローバル ステータスを表示します。
+---------------+-------+
| 変数名 | 値 |
+---------------+-------+
| 開いているファイル | 3 |
| 開いたファイル | 91799 |
+---------------+-------+
セットに2行（0.01秒）

MySQL は各接続を処理するためにスレッドを割り当てます。現在割り当てられているスレッドの数は、threads_connected パラメータで確認できます。

mysql> '%thread%' のようなステータスを表示します。
+------------------------+--------+
| 変数名 | 値 |
+------------------------+--------+
| 遅延挿入スレッド | 0 |
| 遅い起動スレッド | 0 |
| スレッドキャッシュ | 0 |
| スレッド接続 | 14 |
| 作成されたスレッド | 255570 |
| 実行中のスレッド | 2 |
+------------------------+--------+
セット内の 6 行 (0.00 秒)

この threads_connected パラメータを上記の max_connections パラメータと比較すると、現在のシステム負荷の基準として使用して、接続数を変更する必要があるかどうかを判断することもできます。

各スレッドの詳細情報を表示するには、 processlistコマンドを使用します。

mysql> プロセスリストを表示します。
+--------+-----------+--------------------+------------+------------+----------------------------------------------------------------+------------------+
| ID | ユーザー | ホスト | db | コマンド | 時間 | 状態 | 情報 |
+--------+-----------+--------------------+------------+------------+----------------------------------------------------------------+------------------+
| 8293 | repl | 192.168.0.33:47208 | NULL | バイナリログ ダンプ | 11574424 | すべてのバイナリログをスレーブに送信しました。バイナリログが更新されるのを待機しています | NULL |
| 140991 | mogile | 192.168.0.33:41714 | mogilefs | スリープ | 0 | | NULL |
| 140992 | mogile | 192.168.0.33:41715 | mogilefs | スリープ | 3 | | NULL |
| 140993 | mogile | 192.168.0.33:41722 | mogilefs | スリープ | 2 | | NULL |
| 140994 | mogile | 192.168.0.33:41723 | mogilefs | スリープ | 1 | | NULL |
| 140995 | mogile | 192.168.0.33:41724 | mogilefs | スリープ | 3 | | NULL |
| 254914 | mogile | 192.168.0.33:43028 | mogilefs | スリープ | 11074 | | NULL |
| 254915 | mogile | 192.168.0.33:43032 | mogilefs | スリープ | 11091 | | NULL |
| 255144 | mogile | 192.168.0.33:47514 | mogilefs | スリープ | 11090 | | NULL |
| 255157 | mogile | 192.168.0.33:47535 | mogilefs | スリープ | 11087 | | NULL |
| 255162 | mogile | 192.168.0.33:47549 | mogilefs | スリープ | 11074 | | NULL |
| 255260 | root | localhost | mysql | クエリ | 0 | NULL | プロセスリストを表示 |
| 255352 | maopaodev | 192.168.0.78:55399 | maopaodb | スリープ | 3172 | | NULL |
| 255353 | maopaodev | 192.168.0.78:55400 | NULL | スリープ | 8926 | | NULL |
+--------+-----------+--------------------+------------+------------+----------------------------------------------------------------+------------------+
セット内の行数は 14 です (0.00 秒)

このコマンドを実行するには Process_priv 権限が必要です。特定の権限割り当て情報については、mysql.user テーブルを参照してください。

システムの動作に影響を与えるスレッドについては、 kill connection | query threadidコマンドを使用して容赦なく強制終了することができます。

MySQL 関連のコンテンツに興味のある読者は、このサイトの次のトピックをチェックしてください: 「MySQL クエリスキル」、「MySQL トランザクション操作スキル」、「MySQL ストアドプロシージャスキル」、「MySQL データベースロック関連スキルの概要」、および「MySQL 共通関数の概要」

この記事が皆様のMySQLデータベース設計に役立つことを願っています。

以下もご興味があるかもしれません: