===================================== 2021-09-27 10:59:37 0x7f08f2be3700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 37 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 2020 srv_active, 0 srv_shutdown, 1189 srv_idle srv_master_thread log flush and writes: 3209 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 1254 OS WAIT ARRAY INFO: signal count 811 RW-shared spins 316, rounds 7627, OS waits 128 RW-excl spins 369, rounds 4366, OS waits 96 RW-sx spins 4, rounds 63, OS waits 0 Spin rounds per wait: 24.14 RW-shared, 11.83 RW-excl, 15.75 RW-sx ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2021-09-27 10:59:28 0x7f08f0fc6700 *** (1) TRANSACTION: TRANSACTION 7001386, 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 16088, OS thread handle 139676384147200, query id 787187 localhost zabbix Updating DELETE FROM sessions WHERE status='1' AND userid='4' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 77675 page no 209 n bits 368 index sessions_1 of table `zabbix_5_0`.`sessions` trx id 7001386 lock_mode X waiting Record lock, heap no 242 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 8; hex 0000000000000004; asc ;; 1: len 4; hex 80000001; asc ;; 2: len 4; hex e15179e0; asc Qy ;; 3: len 30; hex 613738303264613664383831356564623765393734343163613637653066; asc a7802da6d8815edb7e97441ca67e0f; (total 32 bytes); *** (2) TRANSACTION: TRANSACTION 7001385, ACTIVE 0 sec updating or deleting mysql tables in use 1, locked 1 7 lock struct(s), heap size 1128, 7 row lock(s), undo log entries 2 MySQL thread id 16095, OS thread handle 139676379539200, query id 787190 localhost zabbix Updating UPDATE sessions SET status='1' WHERE sessionid='0c0338352271169b5af54738bc031900' *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 77675 page no 209 n bits 368 index sessions_1 of table `zabbix_5_0`.`sessions` trx id 7001385 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 213 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 8; hex 0000000000000004; asc ;; 1: len 4; hex 80000001; asc ;; 2: len 4; hex e15179e0; asc Qy ;; 3: len 30; hex 666132636637316366333162323266333864383432393130316464316334; asc fa2cf71cf31b22f38d8429101dd1c4; (total 32 bytes); Record lock, heap no 242 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 8; hex 0000000000000004; asc ;; 1: len 4; hex 80000001; asc ;; 2: len 4; hex e15179e0; asc Qy ;; 3: len 30; hex 613738303264613664383831356564623765393734343163613637653066; asc a7802da6d8815edb7e97441ca67e0f; (total 32 bytes); *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 77675 page no 209 n bits 368 index sessions_1 of table `zabbix_5_0`.`sessions` trx id 7001385 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 242 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 8; hex 0000000000000004; asc ;; 1: len 4; hex 80000001; asc ;; 2: len 4; hex e15179e0; asc Qy ;; 3: len 30; hex 613738303264613664383831356564623765393734343163613637653066; asc a7802da6d8815edb7e97441ca67e0f; (total 32 bytes); *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 7001538 Purge done for trx's n:o < 7001537 undo n:o < 0 state: running History list length 1 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421152212246896, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421152212242640, not started 0 lock struct(s), heap size 1128, 0 row lock(s) -------- FILE I/O -------- Pending flushes (fsync) log: 0; buffer pool: 0 48984 OS file reads, 43223 OS file writes, 28267 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 7.89 writes/s, 7.89 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 29, free list len 42, seg size 72, 3732 merges merged operations: insert 14215, delete mark 251, delete 206 discarded operations: insert 0, delete mark 0, delete 0 0.00 hash searches/s, 3970.78 non-hash searches/s --- LOG --- Log sequence number 20871638048 Log flushed up to 20871638048 Pages flushed up to 20831026431 Last checkpoint at 20831006669 0 pending log flushes, 0 pending chkp writes 27479 log i/o's done, 7.89 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 167772160 Dictionary memory allocated 1263768 Buffer pool size 8065 Free buffers 1 Database pages 8064 Old database pages 2956 Modified db pages 2971 Percent of dirty pages(LRU & free pages): 36.834 Max dirty pages percent: 90.000 Pending reads 0 Pending writes: LRU 0, flush list 0 Pages made young 73857, not young 6827328 0.03 youngs/s, 0.00 non-youngs/s Pages read 45969, created 3399, written 14248 0.00 reads/s, 0.11 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: 8064, unzip_LRU len: 0 I/O sum[0]:cur[0], 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 301654, updated 40559, deleted 1250, read 29210925 2.00 inserts/s, 9.76 updates/s, 2.00 deletes/s, 4949.16 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 ============================