Obtaining innodb monitor output

wahyuway MySQL 172 Views

To assist MySQL troubleshooting, you can enable InnoDB monitor output.

InnoDB monitor output begin with a header containing timestamp, as shown below.

=====================================2014-10-16 18:37:29 0x7fc2a95c1700 INNODB MONITOR OUTPUT=====================================

Following an example InnoDB monitor output. It has many data to examine your troubleshooting needs.

=====================================2019-07-09 17:07:17 0x7fefc9c2c700 INNODB MONITOR OUTPUT=====================================Per second averages calculated from the last 24 seconds-----------------BACKGROUND THREAD-----------------srv_master_thread loops: 1148 srv_active, 0 srv_shutdown, 609 srv_idlesrv_master_thread log flush and writes: 1757----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 37997651--Thread 140666066396928 has waited at btr0sea.cc line 1127 for 1.00 seconds the semaphore:S-lock on RW-latch at 0x56294a9e8190 created in file btr0sea.cc line 243a writer (thread id 140666071004928) has reserved it in mode  exclusivenumber of readers 0, waiters flag 1, lock_word: 0Last time write locked in file btr0sea.cc line 1228--Thread 140667732961024 has waited at btr0sea.cc line 212 for 1.00 seconds the semaphore:X-lock on RW-latch at 0x56294a9e8190 created in file btr0sea.cc line 243a writer (thread id 140666071004928) has reserved it in mode  exclusivenumber of readers 0, waiters flag 1, lock_word: 0Last time write locked in file btr0sea.cc line 1228--Thread 140665962563328 has waited at btr0sea.cc line 212 for 1.00 seconds the semaphore:X-lock on RW-latch at 0x56294a9e8190 created in file btr0sea.cc line 243a writer (thread id 140666071004928) has reserved it in mode  exclusivenumber of readers 0, waiters flag 1, lock_word: 0Last time write locked in file btr0sea.cc line 1228--Thread 140667748013824 has waited at btr0sea.cc line 1127 for 1.00 seconds the semaphore:S-lock on RW-latch at 0x56294a9e8190 created in file btr0sea.cc line 243a writer (thread id 140666071004928) has reserved it in mode  exclusivenumber of readers 0, waiters flag 1, lock_word: 0Last time write locked in file btr0sea.cc line 1228--Thread 140666056873728 has waited at btr0sea.cc line 1366 for 1.00 seconds the semaphore:S-lock on RW-latch at 0x56294a9e8190 created in file btr0sea.cc line 243a writer (thread id 140666071004928) has reserved it in mode  exclusivenumber of readers 0, waiters flag 1, lock_word: 0Last time write locked in file btr0sea.cc line 1228--Thread 140665915868928 has waited at btr0sea.cc line 212 for 1.00 seconds the semaphore:X-lock on RW-latch at 0x56294a9e8190 created in file btr0sea.cc line 243a writer (thread id 140666071004928) has reserved it in mode  exclusivenumber of readers 0, waiters flag 1, lock_word: 0Last time write locked in file btr0sea.cc line 1228--Thread 140667747706624 has waited at btr0sea.cc line 212 for 1.00 seconds the semaphore:X-lock on RW-latch at 0x56294a9e8190 created in file btr0sea.cc line 243a writer (thread id 140666071004928) has reserved it in mode  exclusivenumber of readers 0, waiters flag 1, lock_word: 0Last time write locked in file btr0sea.cc line 1228--Thread 140665859958528 has waited at btr0sea.cc line 1127 for 1.00 seconds the semaphore:S-lock on RW-latch at 0x56294a9e8190 created in file btr0sea.cc line 243a writer (thread id 140666071004928) has reserved it in mode  exclusivenumber of readers 0, waiters flag 1, lock_word: 0Last time write locked in file btr0sea.cc line 1228--Thread 140665951504128 has waited at buf0buf.cc line 6177 for 1.00 seconds the semaphore:Mutex at 0x56294a7250d0, Mutex BUF_POOL created buf0buf.cc:1822, lock var 1--Thread 140665978844928 has waited at btr0sea.cc line 212 for 1.00 seconds the semaphore:X-lock on RW-latch at 0x56294a9e8190 created in file btr0sea.cc line 243a writer (thread id 140666071004928) has reserved it in mode  exclusivenumber of readers 0, waiters flag 1, lock_word: 0Last time write locked in file btr0sea.cc line 1228--Thread 140667361453824 has waited at btr0sea.cc line 212 for 1.00 seconds the semaphore:X-lock on RW-latch at 0x56294a9e8190 created in file btr0sea.cc line 243a writer (thread id 140666071004928) has reserved it in mode  exclusivenumber of readers 0, waiters flag 1, lock_word: 0Last time write locked in file btr0sea.cc line 1228--Thread 140665945667328 has waited at btr0sea.cc line 1127 for 1.00 seconds the semaphore:S-lock on RW-latch at 0x56294a9e8190 created in file btr0sea.cc line 243a writer (thread id 140666071004928) has reserved it in mode  exclusivenumber of readers 0, waiters flag 1, lock_word: 0Last time write locked in file btr0sea.cc line 1228--Thread 140668907927296 has waited at btr0sea.cc line 212 for 1.00 seconds the semaphore:X-lock on RW-latch at 0x56294a9e8190 created in file btr0sea.cc line 243a writer (thread id 140666071004928) has reserved it in mode  exclusivenumber of readers 0, waiters flag 1, lock_word: 0Last time write locked in file btr0sea.cc line 1228--Thread 140665967171328 has waited at buf0lru.cc line 1796 for 0.00 seconds the semaphore:Mutex at 0x56294a7250d0, Mutex BUF_POOL created buf0buf.cc:1822, lock var 1--Thread 140665823094528 has waited at buf0lru.cc line 1796 for 0.00 seconds the semaphore:Mutex at 0x56294a7250d0, Mutex BUF_POOL created buf0buf.cc:1822, lock var 0--Thread 140665804969728 has waited at buf0lru.cc line 1092 for 0.00 seconds the semaphore:Mutex at 0x56294a7250d0, Mutex BUF_POOL created buf0buf.cc:1822, lock var 0--Thread 140666023081728 has waited at btr0sea.cc line 1366 for 1.00 seconds the semaphore:S-lock on RW-latch at 0x56294a9e8190 created in file btr0sea.cc line 243a writer (thread id 140666071004928) has reserved it in mode  exclusivenumber of readers 0, waiters flag 1, lock_word: 0Last time write locked in file btr0sea.cc line 1228--Thread 140665826780928 has waited at buf0buf.cc line 3657 for 1.00 seconds the semaphore:Mutex at 0x56294a7250d0, Mutex BUF_POOL created buf0buf.cc:1822, lock var 0--------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] , aio writes: [0, 0, 0, 0] , ibuf aio reads:, log i/o's:, sync i/o's:Pending flushes (fsync) log: 0; buffer pool: 092305698 OS file reads, 634188 OS file writes, 533158 OS fsyncs35844.76 reads/s, 16384 avg bytes/read, 21.17 writes/s, 20.83 fsyncs/s-------------------------------------INSERT BUFFER AND ADAPTIVE HASH INDEX-------------------------------------Ibuf: size 1, free list len 1030, seg size 1032, 26109 mergesmerged operations: insert 43231, delete mark 0, delete 0discarded operations: insert 0, delete mark 0, delete 0Hash table size 34673, node heap has 1 buffer(s)Hash table size 34673, node heap has 0 buffer(s)Hash table size 34673, node heap has 1 buffer(s)Hash table size 34673, node heap has 1 buffer(s)Hash table size 34673, node heap has 0 buffer(s)Hash table size 34673, node heap has 0 buffer(s)Hash table size 34673, node heap has 134 buffer(s)Hash table size 34673, node heap has 1 buffer(s)306.45 hash searches/s, 40159.58 non-hash searches/s---LOG---Log sequence number 450285450616Log flushed up to   450285450616Pages flushed up to 450285450616Last checkpoint at  4502854506070 pending log flushes, 0 pending chkp writes50821 log i/o's done, 2.58 log i/o's/second----------------------BUFFER POOL AND MEMORY----------------------Total large memory allocated 170852352Dictionary memory allocated 188496Buffer pool size   8191Free buffers       0Database pages     7741Old database pages 2859Modified db pages  0Percent of dirty pages(LRU & free pages): 0.000Max dirty pages percent: 75.000Pending reads 268Pending writes: LRU 0, flush list 0, single page 0Pages made young 124801, not young 24216226721.92 youngs/s, 61572.89 non-youngs/sPages read 92306125, created 2546, written 34373335846.51 reads/s, 0.00 creates/s, 9.46 writes/sBuffer pool hit rate 846 / 1000, young-making rate 0 / 1000 not 263 / 1000Pages read ahead 0.00/s, evicted without access 0.04/s, Random read ahead 0.00/sLRU len: 7741, unzip_LRU len: 0I/O sum[1886324]:cur[17332], unzip sum[0]:cur[0]--------------ROW OPERATIONS--------------0 queries inside InnoDB, 0 queries in queue827 read views open inside InnoDBProcess ID=1, Main thread ID=140668349437696, state: sleepingNumber of rows inserted 35638, updated 13586, deleted 0, read 1174342160.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 41446.40 reads/sNumber of system rows inserted 0, updated 0, deleted 0, read 00.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s----------------------------END OF INNODB MONITOR OUTPUT============================

See also  How to resolve cannot set max_connections through my.cnf

To enable, set InnoDB monitor output variable.

SET GLOBAL innodb_status_output=ON;

To access the output, run below query.

mysql> SHOW ENGINE INNODB STATUS\G

Originally posted 2019-07-22 14:00:00.

Daily Tech Blog
© Copyright 2023, All Rights Reserved