===================================== 2021-09-27 17:29:56 0x7f49e4397700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 13 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 1578 srv_active, 0 srv_shutdown, 89 srv_idle srv_master_thread log flush and writes: 1666 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 5533 OS WAIT ARRAY INFO: signal count 2493 RW-shared spins 361, rounds 11073, OS waits 337 RW-excl spins 904, rounds 34355, OS waits 927 RW-sx spins 27, rounds 1290, OS waits 40 Spin rounds per wait: 30.67 RW-shared, 38.00 RW-excl, 47.78 RW-sx ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2021-09-27 17:29:43 0x7f49b0bdf700 *** (1) TRANSACTION: TRANSACTION 7858842, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s) MySQL thread id 41081, OS thread handle 139954472736512, query id 914431 localhost zabbix Updating DELETE FROM sessions WHERE status='1' AND userid='3' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 77994 page no 196 n bits 264 index sessions_1 of table `zabbix_5_0`.`sessions` trx id 7858842 lock_mode X waiting Record lock, heap no 159 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 8; hex 0000000000000003; asc ;; 1: len 4; hex 80000001; asc ;; 2: len 4; hex e151d557; asc Q W;; 3: len 30; hex 336435663930663134633033353435303830393939626339663834633535; asc 3d5f90f14c03545080999bc9f84c55; (total 32 bytes); *** (2) TRANSACTION: TRANSACTION 7858840, ACTIVE 0 sec updating or deleting mysql tables in use 1, locked 1 7 lock struct(s), heap size 1128, 10 row lock(s), undo log entries 2 MySQL thread id 41082, OS thread handle 139954474579712, query id 914439 localhost zabbix Updating UPDATE sessions SET status='1' WHERE sessionid='3ae470a4993b653e8343b5f02a4e6777' *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 77994 page no 196 n bits 264 index sessions_1 of table `zabbix_5_0`.`sessions` trx id 7858840 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; Record lock, heap no 146 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 8; hex 0000000000000003; asc ;; 1: len 4; hex 80000001; asc ;; 2: len 4; hex e151d557; asc Q W;; 3: len 30; hex 613865626234616666643464613834373261623630653962383533303262; asc a8ebb4affd4da8472ab60e9b85302b; (total 32 bytes); Record lock, heap no 148 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 8; hex 0000000000000003; asc ;; 1: len 4; hex 80000001; asc ;; 2: len 4; hex e151d557; asc Q W;; 3: len 30; hex 626439353537303337333435323265363462336136613730656165376137; asc bd955703734522e64b3a6a70eae7a7; (total 32 bytes); Record lock, heap no 159 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 8; hex 0000000000000003; asc ;; 1: len 4; hex 80000001; asc ;; 2: len 4; hex e151d557; asc Q W;; 3: len 30; hex 336435663930663134633033353435303830393939626339663834633535; asc 3d5f90f14c03545080999bc9f84c55; (total 32 bytes); Record lock, heap no 161 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 8; hex 0000000000000003; asc ;; 1: len 4; hex 80000001; asc ;; 2: len 4; hex e151d557; asc Q W;; 3: len 30; hex 623961396533386533343533613138386433376661383864353161316161; asc b9a9e38e3453a188d37fa88d51a1aa; (total 32 bytes); Record lock, heap no 196 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 8; hex 0000000000000003; asc ;; 1: len 4; hex 80000001; asc ;; 2: len 4; hex e151d557; asc Q W;; 3: len 30; hex 613662636666643762643863366237393335656630316431393661646534; asc a6bcffd7bd8c6b7935ef01d196ade4; (total 32 bytes); *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 77994 page no 196 n bits 264 index sessions_1 of table `zabbix_5_0`.`sessions` trx id 7858840 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 159 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 8; hex 0000000000000003; asc ;; 1: len 4; hex 80000001; asc ;; 2: len 4; hex e151d557; asc Q W;; 3: len 30; hex 336435663930663134633033353435303830393939626339663834633535; asc 3d5f90f14c03545080999bc9f84c55; (total 32 bytes); *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 7859322 Purge done for trx's n:o < 7859322 undo n:o < 0 state: running History list length 4 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421430918597168, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421430918601424, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421430918605680, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421430918588656, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421430918626960, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421430918592912, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421430918618448, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421430918584400, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421430918575888, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421430918580144, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421430918541840, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421430918571632, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421430918567376, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421430918563120, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421430918554608, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421430918558864, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421430918550352, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421430918546096, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421430918537584, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421430918533328, not started 0 lock struct(s), heap size 1128, 0 row lock(s) -------- FILE I/O -------- Pending flushes (fsync) log: 0; buffer pool: 0 4484 OS file reads, 33314 OS file writes, 29609 OS fsyncs 0.15 reads/s, 16384 avg bytes/read, 11.61 writes/s, 11.61 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 3065, seg size 3067, 48 merges merged operations: insert 1568, delete mark 9743, delete 0 discarded operations: insert 0, delete mark 0, delete 0 0.00 hash searches/s, 1357.43 non-hash searches/s --- LOG --- Log sequence number 22448623852 Log flushed up to 22448615320 Pages flushed up to 22440593644 Last checkpoint at 22440583127 0 pending log flushes, 0 pending chkp writes 30386 log i/o's done, 11.62 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 167772160 Dictionary memory allocated 1263768 Buffer pool size 8065 Free buffers 4333 Database pages 3732 Old database pages 1357 Modified db pages 830 Percent of dirty pages(LRU & free pages): 10.290 Max dirty pages percent: 90.000 Pending reads 0 Pending writes: LRU 0, flush list 0 Pages made young 1283, not young 15 1.62 youngs/s, 0.15 non-youngs/s Pages read 2643, created 1880, written 2473 0.15 reads/s, 0.23 creates/s, 0.00 writes/s Buffer pool hit rate 999 / 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: 3732, unzip_LRU len: 0 I/O sum[1]:cur[2], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 read views open inside InnoDB Process ID=0, Main thread ID=0, state: sleeping Number of rows inserted 154990, updated 34910, deleted 50641, read 9252887 57.38 inserts/s, 12.31 updates/s, 2.92 deletes/s, 625.57 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 ============================