MySQL Server トラブルシューティング その1

MySQLを運用していると様々なトラブルに遭遇する事があります。
トラブルシューティングをする上で大事なのは、現在のMySQLの状況を知ることです。
各種情報から原因を探っていくためのコマンドを紹介していきます。
今回はSHOWコマンドと変数についてです。

SHOW VARIABLES
現在のシステム変数の値を表示する。システム変数とは各種設定値のこと。
これらの変数はmy.cnfで指定するほかにも、動的に変更可能なものがある。
mysqld全体に影響を及ぼすグローバルレベルと、セッションごとに影響するセッションレベルが存在し、
それぞれSHOW GLOBAL VARIABLESおよびSHOW SESSION VARIABLESで確認することができる。

mysql> SHOW VARIABLES;
+---------------------------------------------------+--------------+
| Variable_name                                     | Value        |
+---------------------------------------------------+--------------+
| auto_increment_increment                          | 1            |
| auto_increment_offset                             | 1            |
| autocommit                                        | ON           |
| automatic_sp_privileges                           | ON           |
| back_log                                          | 50           |
・・・

LIKE句やWHERE句を用いた検索結果の絞り込みも可能である。

mysql> SHOW GLOBAL VARIABLES LIKE '%buffer%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| bulk_insert_buffer_size      | 8388608   |
| innodb_buffer_pool_instances | 1         |
| innodb_buffer_pool_size      | 134217728 |
| innodb_change_buffering      | all       |
| innodb_log_buffer_size       | 8388608   |
・・・

SHOW STATUS
統計情報であるステータス変数を表示するコマンド。
システム変数と同じように、グローバルレベルとセッションレベルが存在し、
それぞれSHOW GLOBAL STATUS,SHOW SESSION STATUSで確認することが可能。

mysql> SHOW STATUS;
+------------------------------------------+-------------+
| Variable_name                            | Value       |
+------------------------------------------+-------------+
| Aborted_clients                          | 0           |
| Aborted_connects                         | 0           |
| Binlog_cache_disk_use                    | 0           |
| Binlog_cache_use                         | 0           |
| Binlog_stmt_cache_disk_use               | 0           |
・・・

グローバルステータス変数はmysqldが起動してから作成されたすべてのセッションにおけるステータス変数の合計であるが、
接続中のセッションのステータス変数が動的に反映されるわけではなく、次のタイミングで反映される。

・セッション終了時
・FLUSH STATUSを実行したとき
・SHOW GLOBAL STATUSを実行したとき

SHOW GLOBAL STATUSを実行することで、排他制御が行われステータス変数がマージされる。
つまり、むやみにSHOW GLOBAL STATUSを実行すると性能低下に繋がる可能性があるので気を付ける。
また、このコマンドで表示される値はmysqldが起動してからの累積なので、現時点の瞬間的な負荷状況を反映しているわけではない。
負荷状況を確認するには、一定の間隔をあけて2回SHOW GLOBAL STATUSを実行し、その差分をみるのが有効。

主なステータス変数

Aborted_clients	接続が終了する前にクライアントが切断した回数。クライアントプログラムのクラッシュやネットワーク不調時に加算される
Aborted_conncts	クライアントがログインに失敗した回数。この値が異常に増えていた場合、不正ログインを試みている可能性がある。
Bytes_received/Bytes_sent	ネットワークの送受信バイト数
Com_***	各コマンドを実行した回数。サーバ負荷が更新メインなのか参照メインなのかを知る手がかりになる。
Connections	クライアントが接続した回数
Created_tmp_disk_tables	メモリ内のテンポラリテーブルにデータが収まらず、MyISAMを用いたテンポラリテーブルが作成された回数
Handler_update	ストレージエンジンに対して、行の更新要求が出された回数
Handler_write	ストレージエンジンに対して、行の挿入要求が出された回数
Handler_delete	ストレージエンジンに対して、行の削除要求が出された回数
Handler_read_first	ストレージエンジンから、インデックスの先頭行が取得された回数。インデックススキャン時に加算。
Handler_read_rnd	ストレージエンジンから、行の位置に基づいて行を読み込んだ回数。テーブルスキャン時に加算
Handler_read_rnd_next	ストレージエンジンから、行の位置に基づいて行を読み込んだ回数。Handler_read_rndの次の行を読み込むと加算。
Max_used_connection	MySQL serverの同時接続数のピーク値
Open_files	現在オープンされているファイル数
Open_tables	現在オープンされているテーブル数
Opened_table_definitions	現在までに開かれたテーブル定義ファイルを累積値。この値が急激に増えていた場合テーブル定義キャッシュが少なすぎる兆候
Opened_tables	現在までに開かれたテーブルの累積値。この値が急激に増えていた場合テーブルキャッシュが少なすぎる兆候
Qcache_***	クエリキャッシュ関連の項目
Select_full_join	インデックスを用いないJOINが行われた回数
Select_full_range_join	範囲検索によってJOINが行われた回数
Select_range_check	Extraフィールドに「range checked for each record(index map : N)」と表示されるようなクエリを実行した回数。Select_full_joinよりは高速なクエリだが、改善する必要あり
Select_scan	JOIN時の先頭のテーブル、または単一のテーブルからのクエリにおいて全件スキャンが行われた回数
Slow_queries	実行に時間のかかったクエリ
Sort_merge_passes	ソート処理においてテンポラリファイル上でソートされた行の結果をマージした回数。この値が増えるのはソートバッファが足りない兆候
Threads_connected	現在接続中のクライアント数
Threads_created	mysqldが起動してから現在までに作成されたスレッド数。急激に増えていたらスレッドキャッシュを増やす
Threads_running	現在何かの処理をしているクライアント数。Threads_connectedの中から処理を行っているクライアントのみをカウント

クエリキャッシュのヒット率計算

Qcache_hits / ( Qcache_hits + Com_select )

Qcache_hitsはクエリキャッシュにヒットして、キャッシュからクライアントへ結果送信された回数。
キャッシュミスが発生した場合、MySQL ServerはSELECT文を実行した際に、Com_selectが加算される。
似た結果を出す式として、

Qcache_hits / ( Qcache_hits + Qcache_inserts + Qcache_not_cached )

SHOW GLOBAL STATUS LIKE 'Qcache%'コマンドで表示した項目だけでヒット率計算ができるのでこちらのほうが便利。

クエリキャッシュへ結果がキャッシュされるとQcache_insertsが加算され、されなかった場合は、Qcache_not_cachedが加算される。
7〜8割程度のヒット率が出ていれば、それなりの効果が期待できる。

クエリキャッシュのヒット率が悪い場合、Qcache_lowmem_prunesが多くなっていないか確認する。
Qcache_lowmem_prunesはクエリキャッシュに割り当てられたメモリが足りないために、クエリキャッシュからエントリを削除した回数。
クエリキャッシュが溢れているので、query_cache_sizeを増やすのが良い。
Qcache_not_cachedが多い場合、明示的にSQL_NO_CACHEでキャッシュしないように指定しているなら問題はない。
その場合は上記式からQcache_not_cachedを除外して計算する。
明示的に指示していない状態でQcache_not_cachedが多い場合、SELECTの結果セットが大きすぎてキャッシュに格納されていないので、
クエリキャッシュに追加できる最大サイズであるquery_cache_limitを増やす。
これらを試してもヒット率が改善しない場合、単純に更新系処理が多いためクエリキャッシュが効率よく利用されていない可能性がある、
その場合クエリキャッシュをOFFにしてしまうのがよい。

SHOW PROCESSLIST
現在接続中のスレッドを一覧で表示する。
Threads_connectedの内訳を表示するコマンド。

SHOW BINARY LOGS
バイナリログが有効になっている場合、存在するバイナリログを表示。

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     57797 |
+------------------+-----------+
1 row in set (0.00 sec)

SHOW BINLOG EVENTS
記録された更新系クエリを表示するコマンド。
デフォルトではもっとも古いバイナリログを表示する。

mysql> SHOW BINLOG EVENTS [IN 'ログファイル名'] [FROM 開始位置] [LIMIT [オフセット,]エントリ数]

かなりの数が表示される場合があるので、LIMIT句でエントリ数を絞ったほうが良い。
レプリケーション失敗したときに、その前のクエリを調べるといった用途で役に立つ。

SHOW MASTER STATUS

レプリケーションに必要なバイナリログ情報を表示する。

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |    57797 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

SHOW SLAVE STATUS
レプリケーションのステータスを表示するコマンド。
コマンド実行時の各フィールドの説明は以下。

Slave_IO_State	スレーブIOスレッドの状態。FailedやReconnectingなどが表示されていたら問題あり
Master_Host	接続するマスター情報
Master_User	〃
Master_Port	〃
Connect_Retry	スレーブIOスレッドが切断してからリトライするまでの時間。：デフォルト60秒
Master_Log_File	スレーブIOスレッドが読んでいるマスター上のログファイル名
Read_Master_Log_Pos	スレーブIOスレッドが読んでいるマスター上のログファイルの位置。レプリケーション遅延を知るには、スレーブ上でSHOW SLAVE STATUSを実行した際に表示されたファイル名と位置を、マスター上で実行したSHOW MASTER STATUSで表示される値と比べて判断する。
Relay_Log_File	スレーブSQLスレッドが実行しているSQL文が含まれるリレーログのファイル名とその中の位置
Relay_Log_Pos	〃
Relay_Master_Log_File	スレーブSQLスレッドが実行しているSQL文が含まれているマスターのバイナリログファイル名。SQLスレッドでエラーが発生した時に、マスター上でSHOW BINLOG EVENTSを実行してその直前に実行していたSQL文を確認するなどの対応ができる。
Slave_IO_Running	スレーブIOスレッドおよびスレーブSQLスレッドが動いているかどうかを示す。もしもどちらかがNoになっていた場合すぐにメンテナンスしたほうが良い。
Slave_SQL_Running	〃
Last_Errno	最後に発生したエラーのエラー番号
Last_Error	最後に発生したエラー内容
Skip_Counter	自動的にエラーをスキップする回数。グローバルシステム変数のsql_slave_skip_counterで設定。重複キーエラーなどをスキップしたいときに。
Replicate_***	スレーブ側のレプリケーションフィルタ
Until_***	UNTIL句を指定してSTART SLAVEを実行したときに表示される
Master_SSL_***	マスターへ接続するときに利用するSSL関係の情報
Exec_Master_Log_Por	スレーブSQLスレッドが実行しているSQL文に含まれているマスターのバイナリログファイルポジション。Relay_Master_Log_Fileに対応。
Seconds_Behind_Master	SQLスレッドがログの適用にどれだけ時間がかかっているかの値。リレーログ中に実行するべきSQL文が何もない場合は0になる。もしこの値が大きいならスレーブの更新性能が低すぎるということを示しているので、innodb_flush_log_at_trx_commit=2にするなどの更新処理が早くなるチューニングを施すべきである。

SHOW SLAVE HOSTS
　--report-host=スレーブホスト名　オプションを付けて起動したスレーブがマスター上でリストアップされる。
スレーブが接続されているか一覧できるので、スレーブにはこのオプションをつけたほうが良い。