SHOW ENGINE INNODB STATUS Walk
SHOW ENGINE INNODB STATUS is a specific form of the SHOW ENGINE statement that displays the InnoDB Monitor output, which is extensive InnoDB information which can be useful in diagnosing problems.
The following sections are displayed
- Status: Shows the timestamp, monitor name and the number of seconds, or the elapsed time between the current time and the time the InnoDB Monitor output was last displayed. The per-second averages are based upon this time.
- BACKGROUND THREAD: srv_master_thread lines show work performed by the main background thread.
- SEMAPHORES: Threads waiting for a semaphore and stats on how the number of times threads have needed a spin or a wait on a mutex or rw-lock semaphore. If this number of threads is large, there may be I/O or contention issues. Reducing the size of the innodb_thread_concurrency system variable may help if contention is related to thread scheduling. Spin rounds per wait shows the number of spinlock rounds per OS wait for a mutex.
- LATEST FOREIGN KEY ERROR: Only shown if there has been a foreign key constraint error, it displays the failed statement and information about the constraint and the related tables.
- LATEST DETECTED DEADLOCK: Only shown if there has been a deadlock, it displays the transactions involved in the deadlock and the statements being executed, held and required locked and the transaction rolled back to.
- TRANSACTIONS: The output of this section can help identify lock contention, as well as reasons for the deadlocks.
- FILE I/O: InnoDB thread information as well as pending I/O operations and I/O performance statistics.
- INSERT BUFFER AND ADAPTIVE HASH INDEX: InnoDB insert buffer (old name for the change buffer) and adaptive hash index status information, including the number of each type of operation performed, and adaptive hash index performance.
- LOG: InnoDB log information, including current log sequence number, how far the log has been flushed to disk, the position at which InnoDB last took a checkpoint, pending writes and write performance statistics.
- BUFFER POOL AND MEMORY: Information on buffer pool pages read and written, which allows you to see the number of data file I/O operations performed by your queries. See InnoDB Buffer Pool for more. Similar information is also available from the INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS table.
- ROW OPERATIONS:Information about the main thread, including the number and performance rate for each type of row operation.
If the innodb_status_output_locks system variable is set to 1, extended lock information will be displayed.
Example output:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 |
mysql> show engine innodb status\G *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2020-12-25 18:45:59 0x7fbcca38c700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 25 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 9101767 srv_active, 0 srv_shutdown, 260 srv_idle srv_master_thread log flush and writes: 9102027 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 1063339384 OS WAIT ARRAY INFO: signal count 6191383907 RW-shared spins 0, rounds 4072890199, OS waits 538799136 RW-excl spins 0, rounds 13503360885, OS waits 312417535 RW-sx spins 15790334, rounds 344341865, OS waits 5753039 Spin rounds per wait: 4072890199.00 RW-shared, 13503360885.00 RW-excl, 21.81 RW-sx ------------ TRANSACTIONS ------------ Trx id counter 4155568249 Purge done for trx's n:o < 4155568078 undo n:o < 0 state: running but idle History list length 164 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 422037917105712, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422037917101168, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422037917085264, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422037917082992, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422037917092080, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422037917080720, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422037917100032, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422037917087536, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422037917081856, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422037917072768, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422037917103440, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 422037917137520, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 4155568168, ACTIVE 1 sec 6 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 4 MySQL thread id 161879215, OS thread handle 140447562884864, query id 30007739795 10.168.0.161 flipproxy ---TRANSACTION 4155561707, ACTIVE 163 sec 1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 406 MySQL thread id 161230513, OS thread handle 140449057949440, query id 30007742058 10.168.0.161 flipproxy -------- 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: 0 1131927901 OS file reads, 3739511583 OS file writes, 880003797 OS fsyncs 179.19 reads/s, 16384 avg bytes/read, 462.22 writes/s, 135.99 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 50, free list len 39658, seg size 39709, 54680983 merges merged operations: insert 75782086, delete mark 12490431, delete 2870714 discarded operations: insert 116, delete mark 0, delete 0 Hash table size 26559067, node heap has 1144 buffer(s) Hash table size 26559067, node heap has 141938 buffer(s) Hash table size 26559067, node heap has 20101 buffer(s) Hash table size 26559067, node heap has 22409 buffer(s) Hash table size 26559067, node heap has 42761 buffer(s) Hash table size 26559067, node heap has 106711 buffer(s) Hash table size 26559067, node heap has 52188 buffer(s) Hash table size 26559067, node heap has 3739 buffer(s) 51254.15 hash searches/s, 5758.65 non-hash searches/s --- LOG --- Log sequence number 7675479621545 Log flushed up to 7675479621432 Pages flushed up to 7675479431134 Last checkpoint at 7675478641960 Max checkpoint age 3478212404 Checkpoint age target 3369518267 Modified age 190411 Checkpoint age 979585 0 pending log flushes, 0 pending chkp writes 383081906 log i/o's done, 51.24 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 111778201600 Dictionary memory allocated 1922540 Internal hash tables (constant factor + variable factor) Adaptive hash index 8105810496 (1699780288 + 6406030208) Page hash 13280296 (buffer pool 0 only) Dictionary cache 426867612 (424945072 + 1922540) File system 976784 (812272 + 164512) Lock system 295864008 (295674296 + 189712) Recovery system 0 (0 + 0) Buffer pool size 6552800 Buffer pool size, bytes 107361075200 Free buffers 8182 Database pages 6153627 Old database pages 2271394 Modified db pages 285165 Pending reads 0 Pending writes: LRU 2, flush list 29, single page 0 Pages made young 4385757132, not young 53079509851 0.00 youngs/s, 0.00 non-youngs/s Pages read 1131908969, created 111395654, written 3281462446 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 6153627, unzip_LRU len: 0 I/O sum[189168]:cur[0], unzip sum[0]:cur[0] ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 819100 Buffer pool size, bytes 13420134400 Free buffers 1018 Database pages 769526 Old database pages 284046 Modified db pages 34591 Pending reads 0 Pending writes: LRU 1, flush list 0, single page 0 Pages made young 564915022, not young 6702442236 0.00 youngs/s, 0.00 non-youngs/s Pages read 145671145, created 14080842, written 491021270 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 769526, unzip_LRU len: 0 I/O sum[23646]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 819100 Buffer pool size, bytes 13420134400 Free buffers 1023 Database pages 769043 Old database pages 283865 Modified db pages 35511 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 549810021, not young 6694044420 0.00 youngs/s, 0.00 non-youngs/s Pages read 142799390, created 13967069, written 322269790 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 769043, unzip_LRU len: 0 I/O sum[23646]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size 819100 Buffer pool size, bytes 13420134400 Free buffers 1024 Database pages 769145 Old database pages 283902 Modified db pages 35712 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 553638170, not young 6847287984 0.00 youngs/s, 0.00 non-youngs/s Pages read 141774563, created 13928430, written 324058782 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 769145, unzip_LRU len: 0 I/O sum[23646]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size 819100 Buffer pool size, bytes 13420134400 Free buffers 1024 Database pages 769233 Old database pages 283935 Modified db pages 35884 Pending reads 0 Pending writes: LRU 0, flush list 23, single page 0 Pages made young 551588620, not young 6607163567 0.00 youngs/s, 0.00 non-youngs/s Pages read 141633931, created 13942444, written 517155076 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 769233, unzip_LRU len: 0 I/O sum[23646]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 4 Buffer pool size 819100 Buffer pool size, bytes 13420134400 Free buffers 1024 Database pages 769137 Old database pages 283899 Modified db pages 37797 Pending reads 0 Pending writes: LRU 0, flush list 2, single page 0 Pages made young 526444157, not young 6516655677 0.00 youngs/s, 0.00 non-youngs/s Pages read 137371317, created 13624029, written 541266525 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 769137, unzip_LRU len: 0 I/O sum[23646]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 5 Buffer pool size 819100 Buffer pool size, bytes 13420134400 Free buffers 1024 Database pages 769181 Old database pages 283915 Modified db pages 35316 Pending reads 0 Pending writes: LRU 0, flush list 2, single page 0 Pages made young 543020376, not young 6584545896 0.00 youngs/s, 0.00 non-youngs/s Pages read 139480525, created 13953554, written 406793857 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 769181, unzip_LRU len: 0 I/O sum[23646]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 6 Buffer pool size 819100 Buffer pool size, bytes 13420134400 Free buffers 1023 Database pages 769093 Old database pages 283884 Modified db pages 35656 Pending reads 0 Pending writes: LRU 0, flush list 2, single page 0 Pages made young 552522104, not young 6604869490 0.00 youngs/s, 0.00 non-youngs/s Pages read 142242185, created 13935986, written 323935865 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 1 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 769093, unzip_LRU len: 0 I/O sum[23646]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 7 Buffer pool size 819100 Buffer pool size, bytes 13420134400 Free buffers 1022 Database pages 769269 Old database pages 283948 Modified db pages 34698 Pending reads 0 Pending writes: LRU 1, flush list 0, single page 0 Pages made young 543818662, not young 6522500581 0.00 youngs/s, 0.00 non-youngs/s Pages read 140935913, created 13963300, written 354961281 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 769269, unzip_LRU len: 0 I/O sum[23646]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 3 read views open inside InnoDB 2 RW transactions active inside InnoDB ---OLDEST VIEW--- Read view low limit trx n:o 4155568078 Trx read view will not see trx with id >= 4155568078, sees < 4155561707 Read view individually stored trx ids: Read view trx id 4155561707 Read view trx id 4155567631 Read view trx id 4155568065 ----------------- Process ID=24767, Main thread ID=140449197762304, state: sleeping Number of rows inserted 74837634891, updated 494442761, deleted 60577147, read 64714676372399 11061.76 inserts/s, 65.84 updates/s, 0.08 deletes/s, 8205091.56 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ |
☛ MinervaDB is trusted by top companies worldwide
