show innodb statusmyfinder

@MySQL Casual Talks Vol.1

show innodb status

• InnoDBの内部情報をいろいろ出してくれるコマンド

> show innodb status\G*************************** 1. row *************************** Type: InnoDB Name:Status:=====================================101206 0:46:59 INNODB MONITOR OUTPUT=====================================Per second averages calculated from the last 3 seconds----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 496314624, signal count 421008783Mutex spin waits 0, rounds 96750651457, OS waits 250979918RW-shared spins 80479277, OS waits 34254151; RW-excl spins 329848899,OS waits 16602110------------TRANSACTIONS------------Trx id counter 0 430135197Purge done for trx's n:o < 0 424553032 undo n:o < 0 0History list length 6LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 0 0, not started, process no 10546, OS thread id 1196796256MySQL thread id 147161807, query id 1720668646 192.168.1.3 rootshow innodb status～～～～---TRANSACTION 0 430135168, not started, process no 10546, OS thread id 1188276576MySQL thread id 147160629, query id 1720668519 192.168.1.3 user--------FILE I/O--------I/O thread 0 state: waiting for i/o request (insert buffer thread)I/O thread 1 state: waiting for i/o request (log thread)I/O thread 2 state: waiting for i/o request (read thread)I/O thread 3 state: waiting for i/o request (write thread)Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0Pending flushes (fsync) log: 0; buffer pool: 045062351 OS file reads, 1019581544 OS file writes, 44466421 OS fsyncs0.33 reads/s, 16384 avg bytes/read, 219.26 writes/s, 9.00 fsyncs/s-------------------------------------INSERT BUFFER AND ADAPTIVE HASH INDEX-------------------------------------Ibuf: size 73058, free list len 60022, seg size 133081,260514070 inserts, 250832819 merged recs, 9490383 mergesHash table size 36124721, node heap has 12966 buffer(s)1772.41 hash searches/s, 2751.75 non-hash searches/s---LOG---Log sequence number 595 2117169635Log flushed up to 595 2117127797Last checkpoint at 594 41278225760 pending log writes, 0 pending chkp writes810407423 log i/o's done, 140.29 log i/o's/second----------------------BUFFER POOL AND MEMORY----------------------Total memory allocated 20041555249; in additional pool allocated 10475520Dictionary memory allocated 240472Buffer pool size 1114112Free buffers 0Database pages 1101146Modified db pages 241999Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages read 49379527, created 119626199, written 7619819250.33 reads/s, 14.33 creates/s, 140.62 writes/sBuffer pool hit rate 1000 / 1000--------------ROW OPERATIONS--------------0 queries inside InnoDB, 0 queries in queue1 read views open inside InnoDBMain thread process no. 10546, id 1157658976, state: sleepingNumber of rows inserted 10645951638, updated 0, deleted 0, read 1077221523.83 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s----------------------------END OF INNODB MONITOR OUTPUT============================

1 row in set, 1 warning (0.00 sec)

なんのこっちゃ

最初の行あたり

最初の行あたり*************************** 1. row *************************** Type: InnoDB Name:Status:=====================================101206 0:46:59 INNODB MONITOR OUTPUT=====================================Per second averages calculated from the last 3 seconds

最初の行あたり

•データを取得した日付

•取得するのに掛かった時間

•この場合は3秒の間の値ということ

SEMAPHORES

SEMAPHORES

OS WAIT ARRAY INFO: reservation count 2, signal count 2Mutex spin waits 0, rounds 0, OS waits 0RW-shared spins 4, OS waits 2; RW-excl spins 0, OS waits 0

SEMAPHORES

• InnoDBの同期はセマフォとミューテックスを使っているので、その統計

OS WAIT ARRAY INFO

•セマフォ配列内のOS待ち数

• reservation count•配列に入った回数

• signal count•スレッドが通知を受け取った回数

Mutex spin...

•スレッドのスピンロックに関する値

• waits -> スピンロック待ち回数

• rounds -> スピンロックのループ回数

• OS waits -> スピンロックでもロックできなかった回数(セマフォ行き)

RW-shared / RW-excl

•共有ロックと排他ロックの待ち種別カウンタ

• spin -> スピンロック待ち

• OS waits -> OS待ち

OS待ちを見る理由

•あるスレッドがロックを取るのに、最初はループして待つ(スピンロック)

• CPU使うけど処理進まない

•短時間なら効率いい

OS待ちを見る理由

• innodb_sync_spin_loops回以上ループしたらOS待ちにフォールバック

•他のスレッドがCPU使う処理をしたいのにループがCPU使ってるのはもったいない→ 待機配列に突っ込む

OS待ちを見る理由

• OS待ちが多い = ロック取れなくて待機配列に一杯突っ込まれてる→ I/Oの改善とか、スレッド数調整

•多いものだと1500万件over→ 改善後の数値取って確認したい

TRANSACTIONS

TRANSACTIONSTrx id counter 0 1280Purge done for trx's n:o < 0 0 undo n:o < 0 0History list length 0LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 0 0, not started, OS thread id 5229043712MySQL thread id 3361, query id 19498 localhost rootshow innodb status

TRANSACTIONS

•トランザクションのサマリとアクティブなトランザクション一覧

Trx id counter

•トランザクションが発生するごとにインクリメントされる

•差分とって記録するとかにすれば発生数が定期的に取れる

Purge done for

•先にMVCCについて

MVCC•同じ行に対して、複数のバージョン (Undoレコード)→ トランザクションによって見える値が違う

•トランザクション中は保持されている→ トランザクションが終わったらそのバージョンは破棄できる

Purge done for• trx’s• Trx id counterの値との差が、まだパージされていないバージョンの数

• undo•パージプロセスが操作している対象 Undoレコードの数

History list length

• Undoレコードの長さ

•トランザクションが更新とかコミットすると増える

•パージプロセスがUndoレコードを消すと減る

何の参考になるのか

•トランザクションを開いたままコミットされない状態のものがどれほどあるかの指標

•トランザクション開いたままだと、古いバージョンの行がメモリに残ったままになる

FILE I/O

FILE I/OI/O thread 0 state: waiting for i/o request (insert buffer thread)I/O thread 1 state: waiting for i/o request (log thread)I/O thread 2 state: waiting for i/o request (read thread)I/O thread 3 state: waiting for i/o request (write thread)Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0Pending flushes (fsync) log: 0; buffer pool: 026 OS file reads, 3 OS file writes, 3 OS fsyncs0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

FILE I/O

•保留中のI/Oやfsync発生回数を見れる

見るべき行

•最後の行

• reads/s -> 秒間の読み出し

• avg bytes/read -> 読み出し量

• writes/s -> 秒間の書き込み

• fsyncs/s -> 秒間のfsync数

read/s

•後述のバッファプールヒット率が高くてもI/Oが発生しまくっている場合はよろしくない→ メモリだけで済んでいない

•可能な限り0に近づけるのが吉

INSERT BUFFER AND

ADAPTIVE HASH INDEX

INSERT BUFFERAND

ADAPTIVE HASH INDEX

Ibuf: size 73058, free list len 60022, seg size 133081,260514070 inserts, 250832819 merged recs, 9490383 mergesHash table size 36124721, node heap has 12966 buffer(s)1772.41 hash searches/s, 2751.75 non-hash searches/s

INSERT BUFFER ?

•主キーは昇順だったりするが、他のインデックスはランダムな順番で挿入されるケースが多い

• id int, name varcher とかの場合、nameはランダムな順番

INSERT BUFFER ?

•そんなセカンダリインデックスページがバッファプールにない場合、そこへの挿入は一旦”挿入バッファ”に入る

•挿入バッファに入ったものが、定期的にセカンダリインデックスにマージされる

INSERT BUFFER

Ibuf: size 73058, free list len 60022, seg size 133081,260514070 inserts, 250832819 merged recs, 9490383 merges

INSERT BUFFER

•挿入回数に対するマージ回数の割合→ = 挿入バッファの効率

ADAPTIVE HASH INDEX ?

•テーブルデータがメモリに全部乗っている場合、InnoDBがハッシュインデックスを作ってくれる→ レコードを含むインデックス→ 一回の検索でレコードが取れる→ = 高速

•逆に言うと非ハッシュインデックス検索は遅い

ADAPTIVE HASH INDEX

Hash table size 36124721, node heap has 12966 buffer(s)1772.41 hash searches/s, 2751.75 non-hash searches/s

ADAPTIVE HASH INDEX

• non-hash searches/s が多い→ ハッシュインデックスが作られるように工夫するなど

LOGと

ROW OPERATIONS

は多分時間が足りないので飛ばします

BUFFER POOLAND

MEMORY

BUFFER POOLAND MEMORY

Total memory allocated 20041555249; in additional pool allocated 10475520Dictionary memory allocated 240472Buffer pool size 1114112Free buffers 0Database pages 1101146Modified db pages 241999Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages read 49379527, created 119626199, written 7619819250.33 reads/s, 14.33 creates/s, 140.62 writes/sBuffer pool hit rate 1000 / 1000

BUFFER POOL ?

•データ、インデックス、アダプティブハッシュインデックスを格納するための領域

• innodb_buffer_pool_size で指定した分+α確保される

Pages read 49379527, created 119626199, written 7619819250.33 reads/s, 14.33 creates/s, 140.62 writes/sBuffer pool hit rate 1000 / 1000

見るべき点

Pages read, created, written

• read, written•バッファプールとディスク間のやり取り

• created•バッファプール内での割当

Buffer pool hit rate

•文字通りバッファプールのヒット率

•メモリにデータが乗り切っているかどうかの指標

BUFFER POOLAND MEMORY

• Buffer pool hit rate→ 1000/1000が理想

• read/s→ 1000/1000でもこれが出てる場合はI/Oが発生している

まとめ

• show innodb status は怖くないよ！

•ロック待ちの状態とか、メモリにデータが乗っているかを確認するのにいいコマンドだよ！

Thanks!