MySQLの状態確認
はじめに
MySQLの状態確認に使用するコマンドについて学習したことを簡単にまとめます。
検証に使用した環境
DB：MySQL8.0
システムステータスを確認する
SHOW STATUS
サーバーのステータス情報を確認できます。
このステートメントは、どの権限も必要ありません。
like句を使用することでシステム変数を部分一致で検索できます。
また、SHOW STATUSで、スコープを指定することもできます。
GLOBALを指定した場合、グローバルシステム変数のステータス値を表示します。
また、SESSIONを指定した場合、現在の接続のステータス変数値を表示します。
※GLOBALを指定したが、変数にグローバル値がない場合は、セッション値が表示されます。
※SESSIONを指定したが、変数にセッション値がない場合は、グローバル値が表示されます。
これにより、一つのシステム変数でグローバル・セッション値がそれぞれある場合に、どちらを確認するか指定できます。
デフォルトではSESSIONが表示されます。
mysql> show スコープ status like パターン;
mysql> # 例
mysql> show status;
mysql> show global status like '%size%';
出力される項目
Variable_name
変数名
Value
設定値
接続状況を確認する
show processlist
サーバー内の各スレッドのセットで、現在実行されている操作を出力します。
サーバの状態把握やトラブル調査、チューニングなどで利用できます。
このステートメントを実行したユーザーがPROCESS権限を持っている場合は、他ユーザーによるスレッドも含めた、すべてのスレッドを表示します。
権限が無ければ、自分のスレッド情報にはアクセスできますが、他ユーザーのスレッド情報にはアクセスできません。
MySQLでは、管理者が常にシステムに接続してチェックできるように、CONNECTION_ADMIN権限、またはSUPER権限（非推奨）を持つユーザーで使用される追加接続が予約されています。
ただし、この権限をすべてのユーザーに付与していないことを前提となっています。
なので、もしスレッドを強制終了したい場合は、KILLステートメントを使用します。
mysql> show [full] processlist
mysql> # 例
mysql> show full processlist;
※FULLキーワードを指定しなかった場合、Info項目には各ステートメントの最初の100文字が表示されます。
出力される項目
Id
接続ID
以下と同じものです
information_schemaデータベースのprocesslistテーブルのIDカラム
performance_schemaデータベースのthreadsテーブルのPROCESSLIST_IDカラム
CONNECTION_ID()関数の戻り値
User
ステートメントを発行したMySQLのユーザー
Host
接続しているホスト
db
選択されているデータベース名
選択されていなければ、NULL
Command
スレッドが実行しているコマンドの種類
クエリを実行している時はQuery
アイドル状態の時はSleep
Time
スレッドが現在の状態になってからの秒数。
レプリカSQLスレッドの場合、最後にレプリケートされたイベントのタイムスタンプとレプリカホストのリアルタイムの間の秒数。
State
現在の状態
(starting, optimizing, preparing, copying to tmp table on diskなど)
ほとんどの状態は、素早く各操作に対応しています。
そのため、スレッドの状態が何秒間も特定の状態にとどまっている場合は、問題が発生している可能性があります。
Info
SQLなどの実行している内容
ステートメントを実行していない場合はNULL。
テーブルの状態を確認する
show table status
TEMPORARYテーブルを除く、各テーブルの情報を出力します。
このステートメントはまた、ビューに関する情報もできます。
ただし、ストレージエンジンによって意味が変わることがあるので注意。
MySQLでは、このステータスを見てクエリをの処理方法を決定します。
テーブルステータスは統計情報「analyze table テーブル名」で更新されます。
統計情報のメリット・デメリット
統計情報が正確なほど、実行計画の精度が高くなります。
しかし、統計情報のデータ量が多くなると、維持管理に時間要し、高負荷の要因となることもあるので注意が必要です。
mysql> show table status [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr];
mysql> # 例
mysql> show table status from データベース名;
出力される項目
Name
テーブル名
Engine
ストレージエンジン
Version
このテーブルの .frm ファイルのバージョン番号。
MySQL8.0では、未使用の項目であるため、一律で10となります。
Row_format
行ストレージフォーマット (Fixed、Dynamic、Compressed、Redundant、Compact)。
MyISAMエンジンで作成されたテーブルの場合、Dynamicは、「myisamchk -dvv」がPackedとしてレポートする内容となります。
Rows
行数。
※概算値のため最大50%ほどのずれる可能性があります
MyISAMなどの一部のストレージエンジンは、正確な数を格納します。
InnoDBなどの他ストレージエンジンの場合は、概算値になり、実際の値と40%から50%まで異なる可能性があります。
正確な値を取得したい場合はSELECT COUNT(*)を使用することになります。
Avg_row_length
1行辺りの平均データ量
Data_length
clusterd indexのメモリ割り当て量（バイト）
Max_data_length
MyISAMの場合は、データファイルの最大長が出力されます。
これは、このテーブル内に格納できるデータの合計バイト数になります。
※使用されるデータポインタサイズが指定された場合
InnoDBでは未使用の項目です。
Index_length
MyISAMの場合、インデックスファイルの長さ (バイト単位) です。
InnoDB の場合、クラスタ化されていないインデックスに割り当てられる領域の概算量 (バイト単位) です。
具体的には、クラスタ化されていないインデックスサイズの合計 (ページ数) にInnoDBページサイズを乗算した値です。
Data_free
テーブルの空き容量です。
InnoDBエンジンで作成されたテーブルの場合
このテーブルが属するテーブルスペースの空き領域をレポートします。
例えば、共有テーブルスペース内に存在するテーブルの場合、共有テーブルスペースの空き領域ということになります。
もし、複数のテーブルスペースを使用していて、このテーブルに独自のテーブルスペースがある場合は、そのテーブルのみの空き領域になります。
空き領域とは、完全な空きエクステントから安全上のマージンを引いたバイト数となります。
空き領域が0と表示されている場合でも、新しいエクステントを割り当てる必要がないかぎり、行を挿入できる可能性があります。
NDB Clusterエンジンで作成されたテーブルの場合、
ディスク上の「ディスクデータ」テーブルまたはフラグメント用にディスクに割り当てられたが使用されていない領域を表示します。
メモリー内データリソース使用率は、Data_length カラムによってレポートされます。
Auto_increment
AUTO_INCREMENTの次の値
Create_time
テーブルの作成日時
Update_time
データファイルの更新日時
一部のストレージエンジンでは、この値はNULL。
InnoDBの場合
InnoDBは誤差要因が多いため厳密ではありません。
システムテーブルスペース内に複数のテーブルを格納するため、データファイルのタイムスタンプは適用されません。
各InnoDBテーブルが個別の.ibdファイル内に存在する「file-per-table」モードでも、変更バッファリングによってデータファイルへの書き込みが遅延される可能性があるため、
ファイルの変更時間は最後の挿入、更新、または削除の時間とは異なります。
MyISAMの場合
データファイルのタイムスタンプが使用されます。
しかし、Windowsではタイムスタンプは更新によって更新されないため、値は正確ではありません。
Check_time
テーブルの最終検査日時
パーティション化されたInnoDBテーブルの場合
常にNULLです。
Collation
デフォルトの照合順序名。
デフォルトの文字セットは明示されませんが、照合順序名は文字セット名で始まりますので汲み取れます。
Checksum
テーブルチェックサムの値
Create_options
テーブル作成時のオプション
(ROW_FORMAT、KEY_BLOCK_SIZEなど)
Comment
テーブルを作成するときに使用されたコメントが出力されます。
また、SHOWでデータが取得できなかった場合のエラーレポートなどに利用されます。
ストレージエンジンの状態を確認する
SHOW ENGINE
ストレージエンジンに関する動作情報を出力します。
このステートメントにはPROCESS権限が必要です。
mysql> show engine engine_name {status | mutex};
mysql> # 使用できるパターン
mysql> show engine innodb status;
mysql> SHOW engine innodb mutex;
mysql> SHOW engine performance_schema status;
STATUSを指定すると、ストレージエンジンの状態に関する情報を出力します。
MUTEXを指定すると、相互排他ロックおよび読み書きロックの統計を出力します。
出力される項目
BACKGROUND THREAD
バックグラウンドスレッドの動作状況
SEMAPHORES
セマフォのwait状況
TRANSACTIONS
トランザクション全体の状況、各トランザクションのロック状況
FILE I/O
IOスレッドの状況
INSERT BUFFER AND ADAPTIVE HASH INDEX
Insert Bufferの状況
LOG
Redoログの状況
BUFFER POOL AND MEMORY
InnoDBバッファプールの状況
ROW OPERATIONS
行操作の状況
参考文献