SHOW ENGINE INNODB STATUSの見方が全然わからなかったので読み方を調べていく
mysql> SHOW ENGINE INNODB STATUS\G;
BACKGROUND THREAD
----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 4973 srv_idle srv_master_thread log flush and writes: 4970
InnoDBには、サーバーの状態に応じて1秒に1回バックグラウンドタスクを実行するマスタースレッドがありメインのバックグラウンドスレッドによって実行された作業を表示している。active値が大きいほど、システムはビジーとなる。
SEMAPHORES
mutextとかsemaphoresとかlockについては以下の知識があると良さそう
排他制御に関する用語を大雑把に理解しよう「ロックとセマフォ」「共有ロックと排他ロック」「楽観的ロックと悲観的ロック」 - Qiita
---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 1 ① OS WAIT ARRAY INFO: signal count 1 ② Mutex spin waits 2, rounds 33, OS waits 1 ③ RW-shared spins 1, rounds 11, OS waits 0 ④ RW-excl spins 0, rounds 0, OS waits 0 ⑤ Spin rounds per wait: 16.50 mutex, 11.00 RW-shared, 0.00 RW-excl
セマフォーを待機しているスレッド、およびスレッドが相互排他ロックまたは読み書きロックセマフォーでスピンまたは待機を必要とした回数に関する統計をレポートしている。
①はスレッドがセマフォの配列に入ったreservation countが配列に入った回数、②がsignal countが配列に入ってOSからシグナルを受け取った回数です。③はスレッドのMutexによる各種待ち回数を示していて、spin waitsはMutexのスピンロック待ちに入った回数、roundsはスピンロック待ちに入ったロックのスピンラウンド総数を表示しています。
④は共有ロック(RW-shared)の待ち種別カウンタで⑤は排他ロック(RW-excl)の待ちを示す。この辺の値が高すぎる場合はinnodb_thread_concurrencyをチューニングするみたいなのが有効とのこと。デフォルトは0で無制限。ある程度クライアントが多い環境でdiskIOみたいなのが発生して長時間lockを保持するスレッドがあるとDBがサチるみたいな現象が発生する。
- InnoDB の mutex の話（入門編） | エンジニアブログ | GREE Engineering
- innodb_thread_concurrencyに関する話 | エンジニアブログ | GREE Engineering
mackerelの数値の取り方を見てるとspin lockは一括りにされていてRWロックなのかmutexによるspinなのかがわからなくなってしまっている。これだとロックしたクエリの特定に時間がかかりそうな気もするが数値が高い場合はSHOW ENGINE INNODB MUTEX;とかで詳細を見る前提の作りなのだろうか？
OS waitsはspinでもセマフォでも資源を獲得できなかった場合のフォールバックでスピン回数なんかはチューニング可能な値となっている。
TRANSACTIONS
------------ TRANSACTIONS ------------ Trx id counter 2306 Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 6, OS thread handle 0x7efc448b8700, query id 62 localhost root init SHOW ENGINE INNODB STATUS
このセクションでロック待機がレポートされている場合は、アプリケーションでロック競合が発生している可能性があるとのこと。接続数が少ない場合は、すべての接続がトランザクションリストに出力される。この出力は、トランザクションのデッドロックの理由を追跡するのにも役立つ。上記ではないですがLATEST DETECTED DEADLOCKセクションに、直前に発生したDeadLockの情報が表示されます。
FILE I/O
-------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 298 OS file reads, 5 OS file writes, 5 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
InnoDB がさまざまなタイプの I/O を実行するために使用するスレッドに関する情報を表示する。
INSERT BUFFER AND ADAPTIVE HASH INDEX
------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 276671, node heap has 0 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s
InnoDB 挿入バッファーおよびアダプティブハッシュインデックスのステータスを表示
LOG
--- LOG --- Log sequence number 1626007 Log flushed up to 1626007 Pages flushed up to 1626007 Last checkpoint at 1626007 0 pending log writes, 0 pending chkp writes 8 log i/o's done, 0.00 log i/o's/second
InnoDB のログに関する情報を表示
BUFFER POOL AND MEMORY
---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137363456; in additional pool allocated 0 -> バッファプールに割り当てられた合計メモリ（バイト） Dictionary memory allocated 40512 -> InnoDBデータディクショナリのキャッシュに割り当てられている合計メモリ Buffer pool size 8191 -> バッファプールに割り振られているページの合計 Free buffers 8042 -> バッファプールの空きリストのページの合計 Database pages 149 -> バッファプールLRUリスト合計サイズ Old database pages 0 -> IBP旧LRU合計サイズ Modified db pages 0 -> 変更されたデータベースページ Pending reads 0 -> Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 149, created 0, written 1 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 149, unzip_LRU len: 0 -> バッファプールLRUリストの合計ページ数.バッファプールのunzip_LRUリストの合計ページ数 I/O sum[0]:cur[0], unzip sum[0]:cur[0]
読み取られたページと書き込まれたページに関する統計を表示。クエリが現在実行しているデータファイルI/O操作の数を計算できます。ページサイズはデフォルトで16KBなのでここの数値を見る際は積算が必要です。特に重要なのは以下の3つでさらにreadがとても重要。buffer poolのreadだけで済んでいれば特に問題はないがこの値が高い場合はbuffer poolのサイズが足りておらずディスクIOが発生している証拠となる。pool sizeをチューニングしましょうねという話になるはず。
- read：ディスクから読んでバッファプールにロードしたページ総数
- created ：バッファプールに作成されたページの総数
- written：バッファプールからディスクに書き込まれたページの総数
ROW OPERATIONS
-------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Main thread process no. 1, id 139621917124352, state: sleeping Number of rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
メインスレッドが実行している内容 (各タイプの行操作の数とパフォーマンスレートを含む) を表示
1行目からInnoDB内で0クエリ、キュー内で0クエリ、InnoDB内で開いている読み取りビューがわかる。一番気になるのは一番最後の行でQPSとかが表示されている。
