| InnoDB | | ===================================== 2019-03-07 20:18:33 0x7f7f6c74d700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 30 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 250666 srv_active, 0 srv_shutdown, 1366 srv_idle srv_master_thread log flush and writes: 251980 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 3209753 OS WAIT ARRAY INFO: signal count 15168798 RW-shared spins 0, rounds 10364410, OS waits 189211 RW-excl spins 0, rounds 314968551, OS waits 1535097 RW-sx spins 514602, rounds 8104617, OS waits 67449 Spin rounds per wait: 10364410.00 RW-shared, 314968551.00 RW-excl, 15.75 RW-sx ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2019-03-07 18:05:15 0x7f7f2504f700 *** (1) TRANSACTION: TRANSACTION 8632421038, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 38 lock struct(s), heap size 3520, 22 row lock(s), undo log entries 6 MySQL thread id 8663529, OS thread handle 140184476854016, query id 134926303 10.60.205.182 ddx update INSERT INTO ddx_lab_801925.cases_invoices (id, case_id, statement_date, practice_id, invoice_date, taxes, total, line_items, note, payment, payment_id) VALUES ('354775', '399576', NULL, '21289', '2019-03-07', '0.00', '42.00', 'WORKCODES', NULL, '0', NULL) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 345082 page no 272 n bits 1264 index IDX_cases_invoices_1 of table `ddx_lab_801925`.`cases_invoices` trx id 8632421038 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 1197 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 000618da; asc ;; 1: len 4; hex 000569d6; asc i ;; *** (2) TRANSACTION: TRANSACTION 8632421040, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 36 lock struct(s), heap size 3520, 20 row lock(s), undo log entries 6 MySQL thread id 8663541, OS thread handle 140184058656512, query id 134926304 10.60.205.182 ddx update INSERT INTO ddx_lab_801925.cases_invoices (id, case_id, statement_date, practice_id, invoice_date, taxes, total, line_items, note, payment, payment_id) VALUES ('354776', '399575', NULL, '21289', '2019-03-07', '0.00', '42.00', 'WORKCODES', NULL, '0', NULL) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 345082 page no 272 n bits 1264 index IDX_cases_invoices_1 of table `ddx_lab_801925`.`cases_invoices` trx id 8632421040 lock mode S locks gap before rec Record lock, heap no 1197 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 000618da; asc ;; 1: len 4; hex 000569d6; asc i ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 345082 page no 272 n bits 1264 index IDX_cases_invoices_1 of table `ddx_lab_801925`.`cases_invoices` trx id 8632421040 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 1197 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 000618da; asc ;; 1: len 4; hex 000569d6; asc i ;; *** WE ROLL BACK TRANSACTION (2) ------------ TRANSACTIONS ------------ Trx id counter 8632639749 Purge done for trx's n:o < 8632639749 undo n:o < 0 state: running but idle History list length 170 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421700978164704, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421700978141632, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421700978139984, 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 (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 2334798 OS file reads, 14346837 OS file writes, 2618254 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 18.10 writes/s, 3.03 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 5, seg size 7, 12897 merges merged operations: insert 13280, delete mark 2969, delete 433 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 9562499, node heap has 6756 buffer(s) Hash table size 9562499, node heap has 6931 buffer(s) Hash table size 9562499, node heap has 9298 buffer(s) Hash table size 9562499, node heap has 11111 buffer(s) Hash table size 9562499, node heap has 12621 buffer(s) Hash table size 9562499, node heap has 8409 buffer(s) Hash table size 9562499, node heap has 10942 buffer(s) Hash table size 9562499, node heap has 11426 buffer(s) 16.30 hash searches/s, 56.43 non-hash searches/s --- LOG --- Log sequence number 3123938985747 Log flushed up to 3123938985747 Pages flushed up to 3123938985747 Last checkpoint at 3123938985738 0 pending log flushes, 0 pending chkp writes 9593478 log i/o's done, 6.90 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 39617298432 Dictionary memory allocated 1444734709 Buffer pool size 2359296 Free buffers 331100 Database pages 1950702 Old database pages 720224 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 2482, not young 0 0.06 youngs/s, 0.00 non-youngs/s Pages read 1724045, created 226657, written 4087238 0.00 reads/s, 0.00 creates/s, 10.19 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: 1950702, 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 294912 Free buffers 28100 Database pages 257073 Old database pages 94913 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 375, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 227328, created 29745, written 686418 0.00 reads/s, 0.00 creates/s, 2.71 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: 257073, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 294912 Free buffers 33941 Database pages 251263 Old database pages 92771 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 283, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 224377, created 26886, written 472002 0.00 reads/s, 0.00 creates/s, 1.84 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: 251263, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size 294912 Free buffers 48038 Database pages 237184 Old database pages 87573 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 273, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 207905, created 29279, written 483645 0.00 reads/s, 0.00 creates/s, 0.35 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: 237184, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size 294912 Free buffers 61756 Database pages 223570 Old database pages 82546 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 249, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 196316, created 27254, written 643113 0.00 reads/s, 0.00 creates/s, 2.55 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: 223570, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 4 Buffer pool size 294912 Free buffers 29513 Database pages 255717 Old database pages 94414 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 385, not young 0 0.03 youngs/s, 0.00 non-youngs/s Pages read 225858, created 29859, written 566228 0.00 reads/s, 0.00 creates/s, 1.74 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 1 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 255717, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 5 Buffer pool size 294912 Free buffers 33997 Database pages 251291 Old database pages 92781 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 247, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 224024, created 27267, written 414108 0.00 reads/s, 0.00 creates/s, 0.35 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: 251291, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 6 Buffer pool size 294912 Free buffers 35591 Database pages 249644 Old database pages 92166 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 332, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 219650, created 29994, written 435498 0.00 reads/s, 0.00 creates/s, 0.39 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: 249644, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 7 Buffer pool size 294912 Free buffers 60164 Database pages 224960 Old database pages 83060 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 338, not young 0 0.03 youngs/s, 0.00 non-youngs/s Pages read 198587, created 26373, written 386226 0.00 reads/s, 0.00 creates/s, 0.26 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 5 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 224960, 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=6513, Main thread ID=140185592424192, state: enforcing dict cache limit Number of rows inserted 55966561, updated 2055976, deleted 55556912, read 202925928278 0.27 inserts/s, 2.20 updates/s, 0.00 deletes/s, 267.22 reads/s Number of system 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 ---------------------------- END OF INNODB MONITOR OUTPUT ============================