===================================== 160127 16:32:18 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 30 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 168 1_second, 167 sleeps, 16 10_second, 10 background, 9 flush srv_master_thread log flush and writes: 183 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 228, signal count 236 --Thread 8436 has waited at log0log.c line 1377 for 0.00 seconds the semaphore: Mutex at 000000000543F5C8 created file log0log.c line 771, lock var 1 waiters flag 1 --Thread 7764 has waited at log0log.ic line 320 for 0.00 seconds the semaphore: Mutex at 000000000543F5C8 created file log0log.c line 771, lock var 1 waiters flag 1 --Thread 6172 has waited at log0log.ic line 405 for 0.00 seconds the semaphore: Mutex at 000000000543F5C8 created file log0log.c line 771, lock var 1 waiters flag 1 Mutex spin waits 1355, rounds 4279, OS waits 76 RW-shared spins 135, rounds 3875, OS waits 123 RW-excl spins 12, rounds 938, OS waits 28 Spin rounds per wait: 3.16 mutex, 28.70 RW-shared, 78.17 RW-excl ------------------------ LATEST DETECTED DEADLOCK ------------------------ 160127 16:32:04 *** (1) TRANSACTION: TRANSACTION C23B57, ACTIVE 0 sec starting index readread mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1248, 13 row lock(s), undo log entries 12 MySQL thread id 1, OS thread handle 0x1020, query id 125134 localhost 127.0.0.1 root Updating UPDATE T_CONNECTIONS set Status=1, MessageId=22, AdditionalInfo=null WHERE Id=626551 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 2701 n bits 120 index `PRIMARY` of table `schema`.`t_connections` trx id C23B57 lock_mode X locks rec but not gap waiting Record lock, heap no 37 PHYSICAL RECORD: n_fields 41; compact format; info bits 0 0: len 8; hex 8000000000098f77; asc w;; 1: len 6; hex 000000c23acb; asc : ;; 2: len 7; hex 0200000e1323e4; asc # ;; 3: len 8; hex 8000000000098f75; asc u;; 4: SQL NULL; 5: len 3; hex 65646d; asc edm;; 6: SQL NULL; 7: SQL NULL; 8: SQL NULL; 9: len 8; hex 8000000000098f76; asc v;; 10: len 4; hex 80000001; asc ;; 11: len 4; hex 80002645; asc &E;; 12: len 4; hex 80001304; asc ;; 13: len 4; hex 56a272ee; asc V r ;; 14: len 4; hex 56a93750; asc V 7P;; 15: len 16; hex 4c696e7578207838362053797374656d; asc Linux x86 System;; 16: len 16; hex 4c696e7578207838362053797374656d; asc Linux x86 System;; 17: SQL NULL; 18: len 4; hex 80000016; asc ;; 19: SQL NULL; 20: len 4; hex 56a258e0; asc V X ;; 21: len 4; hex 80000005; asc ;; 22: len 3; hex 544c43; asc TLC;; 23: len 4; hex 494e464f; asc INFO;; 24: len 4; hex 32304d42; asc 20MB;; 25: len 1; hex 35; asc 5;; 26: SQL NULL; 27: len 1; hex 31; asc 1;; 28: len 1; hex 00; asc ;; 29: len 1; hex 00; asc ;; 30: len 1; hex 00; asc ;; 31: SQL NULL; 32: len 1; hex 01; asc ;; 33: SQL NULL; 34: SQL NULL; 35: SQL NULL; 36: SQL NULL; 37: SQL NULL; 38: len 8; hex 362e352e32322e33; asc 6.5.22.3;; 39: len 5; hex 4c696e7578; asc Linux;; 40: len 1; hex 00; asc ;; *** (2) TRANSACTION: TRANSACTION C23A6D, ACTIVE 2 sec inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 2 MySQL thread id 3, OS thread handle 0x1d28, query id 125374 localhost 127.0.0.1 root update insert into T_TRIGGERED_THRESHOLDS (ConnectionId, AlertId, StartTime, LastTime, TriggeredValue, MetricName, ThresholdValue, AlertType, Direction, Duration, Conditions, Dropped, MessageId, Message, State, DCSMessage, AlertDuration, thePriority, EmailMessage, AssignedUser, ForecastId, thresholdid, ID) values (626613, 'Linux CPU#CPU % System for Global.603976_626613', '2016-01-27 16:31:00', '2016-01-27 16:31:30', '0.633', 'CPU % System for Global', '0.000', 0, 'High', '120', '>=', 0, -1, 'CPU % System for Global 0.633 >= 0.000', 1, null, 1, 1, 'CPU utilization is high.|Overall CPU utilization is high. Add more resources or reduce the amount of work this system has to perform.', null, null, 603976, 669458) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 2701 n bits 168 index `PRIMARY` of table `schema`.`t_connections` trx id C23A6D lock mode S locks rec but not gap Record lock, heap no 37 PHYSICAL RECORD: n_fields 41; compact format; info bits 0 0: len 8; hex 8000000000098f77; asc w;; 1: len 6; hex 000000c23acb; asc : ;; 2: len 7; hex 0200000e1323e4; asc # ;; 3: len 8; hex 8000000000098f75; asc u;; 4: SQL NULL; 5: len 3; hex 65646d; asc edm;; 6: SQL NULL; 7: SQL NULL; 8: SQL NULL; 9: len 8; hex 8000000000098f76; asc v;; 10: len 4; hex 80000001; asc ;; 11: len 4; hex 80002645; asc &E;; 12: len 4; hex 80001304; asc ;; 13: len 4; hex 56a272ee; asc V r ;; 14: len 4; hex 56a93750; asc V 7P;; 15: len 16; hex 4c696e7578207838362053797374656d; asc Linux x86 System;; 16: len 16; hex 4c696e7578207838362053797374656d; asc Linux x86 System;; 17: SQL NULL; 18: len 4; hex 80000016; asc ;; 19: SQL NULL; 20: len 4; hex 56a258e0; asc V X ;; 21: len 4; hex 80000005; asc ;; 22: len 3; hex 544c43; asc TLC;; 23: len 4; hex 494e464f; asc INFO;; 24: len 4; hex 32304d42; asc 20MB;; 25: len 1; hex 35; asc 5;; 26: SQL NULL; 27: len 1; hex 31; asc 1;; 28: len 1; hex 00; asc ;; 29: len 1; hex 00; asc ;; 30: len 1; hex 00; asc ;; 31: SQL NULL; 32: len 1; hex 01; asc ;; 33: SQL NULL; 34: SQL NULL; 35: SQL NULL; 36: SQL NULL; 37: SQL NULL; 38: len 8; hex 362e352e32322e33; asc 6.5.22.3;; 39: len 5; hex 4c696e7578; asc Linux;; 40: len 1; hex 00; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 2701 n bits 120 index `PRIMARY` of table `schema`.`t_connections` trx id C23A6D lock mode S locks rec but not gap waiting Record lock, heap no 45 PHYSICAL RECORD: n_fields 41; compact format; info bits 0 0: len 8; hex 8000000000098fb5; asc ;; 1: len 6; hex 000000c23b57; asc ;W;; 2: len 7; hex 0d00000229064a; asc ) J;; 3: len 8; hex 8000000000098fb3; asc ;; 4: SQL NULL; 5: len 3; hex 65646d; asc edm;; 6: SQL NULL; 7: SQL NULL; 8: SQL NULL; 9: len 8; hex 8000000000098fb4; asc ;; 10: len 4; hex 80000001; asc ;; 11: len 4; hex 80001dd3; asc ;; 12: len 4; hex 8000132c; asc ,;; 13: len 4; hex 56a272ee; asc V r ;; 14: len 4; hex 56a93750; asc V 7P;; 15: len 19; hex 4c696e7578207838365f36342053797374656d; asc Linux x86_64 System;; 16: len 19; hex 4c696e7578207838365f36342053797374656d; asc Linux x86_64 System;; 17: SQL NULL; 18: len 4; hex 80000016; asc ;; 19: SQL NULL; 20: len 4; hex 56a258e6; asc V X ;; 21: len 4; hex 80000005; asc ;; 22: len 3; hex 544c43; asc TLC;; 23: len 4; hex 494e464f; asc INFO;; 24: len 4; hex 32304d42; asc 20MB;; 25: len 1; hex 35; asc 5;; 26: SQL NULL; 27: len 1; hex 31; asc 1;; 28: len 1; hex 00; asc ;; 29: len 1; hex 00; asc ;; 30: len 1; hex 00; asc ;; 31: SQL NULL; 32: len 1; hex 01; asc ;; 33: SQL NULL; 34: SQL NULL; 35: SQL NULL; 36: SQL NULL; 37: SQL NULL; 38: len 7; hex 362e352e342e32; asc 6.5.4.2;; 39: len 5; hex 4c696e7578; asc Linux;; 40: len 1; hex 00; asc ;; *** WE ROLL BACK TRANSACTION (2) ------------ TRANSACTIONS ------------ Trx id counter C2452A Purge done for trx's n:o < C23E0B undo n:o < 0 History list length 1018 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION C24527, not started MySQL thread id 8, OS thread handle 0x1a58, query id 133547 localhost 127.0.0.1 root ---TRANSACTION C24528, not started MySQL thread id 7, OS thread handle 0x20fc, query id 133550 localhost 127.0.0.1 root ---TRANSACTION 0, not started MySQL thread id 6, OS thread handle 0x2090, query id 133554 localhost 127.0.0.1 root SHOW ENGINE INNODB STATUS ---TRANSACTION 0, not started MySQL thread id 5, OS thread handle 0x21a8, query id 80410 localhost 127.0.0.1 root ---TRANSACTION C24447, not started flushing log MySQL thread id 4, OS thread handle 0x20f4, query id 132877 localhost 127.0.0.1 root commit ---TRANSACTION C24529, not started MySQL thread id 3, OS thread handle 0x1d28, query id 133553 localhost 127.0.0.1 root ---TRANSACTION C2432E, ACTIVE 3 sec inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 8 lock struct(s), heap size 1248, 24 row lock(s), undo log entries 21 MySQL thread id 2, OS thread handle 0x1e54, query id 133458 localhost 127.0.0.1 root update insert into T_TRIGGERED_THRESHOLDS (ConnectionId, AlertId, StartTime, LastTime, TriggeredValue, MetricName, ThresholdValue, AlertType, Direction, Duration, Conditions, Dropped, MessageId, Message, State, DCSMessage, AlertDuration, thePriority, EmailMessage, AssignedUser, ForecastId, thresholdid, ID) values (655857, '655857', '2016-01-27 16:32:15', '2016-01-27 16:32:15', null, null, null, 3, null, null, null, null, 8, 'Agent not responding', 1, 'Unresolved Internet address: blah:1645', 0, null, null, null, null, null, 669466) Trx read view will not see trx with id >= C24332, sees < C23E07 ---TRANSACTION C23E07, ACTIVE 11 sec 3 lock struct(s), heap size 376, 2 row lock(s), undo log entries 6 MySQL thread id 1, OS thread handle 0x1020, query id 132874 localhost 127.0.0.1 root Trx read view will not see trx with id >= C23E08, sees < C23E08 -------- FILE I/O -------- I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (read thread) I/O thread 4 state: wait Windows aio (read thread) I/O thread 5 state: wait Windows aio (read thread) I/O thread 6 state: wait Windows aio (write thread) I/O thread 7 state: wait Windows aio (write thread) I/O thread 8 state: wait Windows aio (write thread) I/O thread 9 state: wait Windows aio (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 1 Pending flushes (fsync) log: 0; buffer pool: 0 1084 OS file reads, 1655 OS file writes, 389 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 6.83 writes/s, 1.73 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 5 merges merged operations: insert 23, delete mark 0, delete 18 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 222149, node heap has 21 buffer(s) 1195.13 hash searches/s, 572.05 non-hash searches/s --- LOG --- Log sequence number 856695875 Log flushed up to 856695042 Last checkpoint at 856685676 1 pending log writes, 0 pending chkp writes 321 log i/o's done, 1.50 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 114835456; in additional pool allocated 0 Dictionary memory allocated 633963 Buffer pool size 6848 Free buffers 5735 Database pages 1092 Old database pages 419 Modified db pages 23 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 1073, created 19, written 1296 0.00 reads/s, 0.03 creates/s, 5.17 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: 1092, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 1 queries inside InnoDB, 0 queries in queue 3 read views open inside InnoDB Main thread id 6336, state: flushing log Number of rows inserted 1246, updated 2773, deleted 0, read 9161267 11.83 inserts/s, 8.40 updates/s, 0.00 deletes/s, 15841.71 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================