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
| root@MySQL-01 17:58: [(none)]> show engine innodb status\G; *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2020-10-21 18:03:37 0x7f17f0110700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 15 seconds
BACKGROUND THREAD
srv_master_thread loops: 110 srv_active, 0 srv_shutdown, 2345383 srv_idle srv_master_thread log flush and writes: 2345493
SEMAPHORES
OS WAIT ARRAY INFO: reservation count 353 OS WAIT ARRAY INFO: signal count 305 RW-shared spins 0, rounds 7763, OS waits 293 RW-excl spins 0, rounds 965, OS waits 14 RW-sx spins 3, rounds 207, OS waits 2 Spin rounds per wait: 7763.00 RW-shared, 965.00 RW-excl, 69.00 RW-sx
LATEST DETECTED DEADLOCK
2020-10-21 14:00:46 0x7f17ff08f700 *** (1) TRANSACTION: TRANSACTION 42089766, ACTIVE 3 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 MySQL thread id 77, OS thread handle 139740656969472, query id 658 localhost root executing insert into z select 4,4 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 577 page no 4 n bits 72 index b of table `qinxi`.`z` trx id 42089766 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 4; hex 80000007; asc ;;
*** (2) TRANSACTION: TRANSACTION 42089767, ACTIVE 158 sec inserting mysql tables in use 1, locked 1 4 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 78, OS thread handle 139741039752960, query id 659 localhost root executing insert into z select 4,4 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 577 page no 4 n bits 72 index b of table `qinxi`.`z` trx id 42089767 lock mode S locks gap before rec Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 4; hex 80000007; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 577 page no 3 n bits 80 index PRIMARY of table `qinxi`.`z` trx id 42089767 lock mode S waiting Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000004; asc ;; 1: len 6; hex 000002823d26; asc =&;; 2: len 7; hex d30000024e0110; asc N ;; 3: len 4; hex 80000004; asc ;;
*** WE ROLL BACK TRANSACTION (2)
TRANSACTIONS
Trx id counter 42089796 Purge done for trx's n:o < 42089792 undo n:o < 0 state: running but idle History list length 2 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421218363488080, not started 0 lock struct(s), heap size 1136, 0 row lock(s) -------- 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 (read thread) I/O thread 7 state: waiting for completed aio requests (read thread) I/O thread 8 state: waiting for completed aio requests (read thread) I/O thread 9 state: waiting for completed aio requests (read thread) I/O thread 10 state: waiting for completed aio requests (write thread) I/O thread 11 state: waiting for completed aio requests (write thread) I/O thread 12 state: waiting for completed aio requests (write thread) I/O thread 13 state: waiting for completed aio requests (write thread) I/O thread 14 state: waiting for completed aio requests (write thread) I/O thread 15 state: waiting for completed aio requests (write thread) I/O thread 16 state: waiting for completed aio requests (write thread) I/O thread 17 state: waiting for completed aio requests (write thread) Pending normal aio reads: [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0, 0, 0] , ibuf aio reads:, log i/o's:, sync i/o's: Pending flushes (fsync) log: 0; buffer pool: 0 41693 OS file reads, 1833 OS file writes, 1220 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 4 merges merged operations: insert 2, delete mark 2, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 553193, node heap has 1 buffer(s) Hash table size 553193, node heap has 0 buffer(s) Hash table size 553193, node heap has 0 buffer(s) Hash table size 553193, node heap has 0 buffer(s) Hash table size 553193, node heap has 0 buffer(s) Hash table size 553193, node heap has 0 buffer(s) Hash table size 553193, node heap has 0 buffer(s) Hash table size 553193, node heap has 1 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 13780133802 Log flushed up to 13780133802 Pages flushed up to 13780133802 Last checkpoint at 13780133793 0 pending log flushes, 0 pending chkp writes 551 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 2198863872 Dictionary memory allocated 640362 Buffer pool size 131056 Free buffers 89450 Database pages 41609 Old database pages 15493 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 41499, created 110, written 932 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: 41609, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 16382 Free buffers 11216 Database pages 5166 Old database pages 1924 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 5158, created 8, written 142 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: 5166, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 16382 Free buffers 11181 Database pages 5201 Old database pages 1934 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 5186, created 15, written 143 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: 5201, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size 16382 Free buffers 11187 Database pages 5194 Old database pages 1934 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 5183, created 11, written 68 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: 5194, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size 16382 Free buffers 11139 Database pages 5242 Old database pages 1954 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 5203, created 39, written 204 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: 5242, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 4 Buffer pool size 16382 Free buffers 11073 Database pages 5309 Old database pages 1977 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 5300, created 9, written 87 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: 5309, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 5 Buffer pool size 16382 Free buffers 11360 Database pages 5022 Old database pages 1870 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 5012, created 10, written 90 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: 5022, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 6 Buffer pool size 16382 Free buffers 11146 Database pages 5238 Old database pages 1950 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 5229, created 9, written 111 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: 5238, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 7 Buffer pool size 16382 Free buffers 11148 Database pages 5237 Old database pages 1950 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 5228, created 9, written 87 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: 5237, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Process ID=22311, Main thread ID=139740711540480, state: sleeping Number of rows inserted 279, updated 16, deleted 0, read 910613 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================
1 row in set (0.02 sec)
ERROR: No query specified
|