Hi @all,
we have massive problems in our productive monitoring.
we have upgraded from 1.8.2 to 1.8.3 on 17.11.10 , since 26.11.10 there are massive deadlock errors in the zabbix log, and in the DB (mysql).
Since the deadlocks appear the server works some time well and then with each deadlock the load go higher and higher. At some point the server stops working.
I have downgraded to zabbix 1.8.2 , the deadlock isn't completly gone but comes with much lower frequency, and the server works "well" (works, but i don't know how long)
Innodb Status:
Zabbix server log:
mysqld config
Server
4x Intel(R) Xeon(TM) MP CPU 3.16GHz
32 GB Ram
os: Centos5
More Infos needed?
rgds flo
we have massive problems in our productive monitoring.
we have upgraded from 1.8.2 to 1.8.3 on 17.11.10 , since 26.11.10 there are massive deadlock errors in the zabbix log, and in the DB (mysql).
Since the deadlocks appear the server works some time well and then with each deadlock the load go higher and higher. At some point the server stops working.
I have downgraded to zabbix 1.8.2 , the deadlock isn't completly gone but comes with much lower frequency, and the server works "well" (works, but i don't know how long)
Innodb Status:
Code:
===================================== 101127 16:55:36 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 9 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 2141678, signal count 2045441 Mutex spin waits 0, rounds 772411483, OS waits 1191620 RW-shared spins 1061752, OS waits 253238; RW-excl spins 3791177, OS waits 95304 ------------------------ LATEST DETECTED DEADLOCK ------------------------ 101127 16:52:55 *** (1) TRANSACTION: TRANSACTION 0 1309159616, ACTIVE 0 sec, process no 9212, OS thread id 1101355328 starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 368 MySQL thread id 38, query id 46589957 localhost zabbix updating delete from escalations where escalationid=7712544 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 14 page no 57 n bits 264 index `PRIMARY` of table `zabbix/escalations` trx id 0 1309159616 lock_mode X locks rec but not gap waiting Record lock, heap no 188 PHYSICAL RECORD: n_fields 10; compact format; info bits 32 0: len 8; hex 000000000075af20; asc u ;; 1: len 6; hex 00004e0830b6; asc N 0 ;; 2: len 7; hex 0000002b5704b1; asc +W ;; 3: len 8; hex 000000000000000d; asc ;; 4: len 8; hex 0000000000000000; asc ;; 5: len 8; hex 0000000000af9ad0; asc ;; 6: len 8; hex 0000000000000000; asc ;; 7: len 4; hex 80000000; asc ;; 8: len 4; hex 80000000; asc ;; 9: len 4; hex 80000000; asc ;; *** (2) TRANSACTION: TRANSACTION 0 1309159606, ACTIVE 0 sec, process no 9212, OS thread id 1176361280 fetching rows, thread declared inside InnoDB 170 mysql tables in use 1, locked 1 26 lock struct(s), heap size 6752, undo log entries 5 MySQL thread id 7, query id 46590056 localhost zabbix updating delete from escalations where actionid=64 and triggerid=0 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 14 page no 57 n bits 256 index `PRIMARY` of table `zabbix/escalations` trx id 0 1309159606 lock_mode X locks rec but not gap Record lock, heap no 188 PHYSICAL RECORD: n_fields 10; compact format; info bits 32 0: len 8; hex 000000000075af20; asc u ;; 1: len 6; hex 00004e0830b6; asc N 0 ;; 2: len 7; hex 0000002b5704b1; asc +W ;; 3: len 8; hex 000000000000000d; asc ;; 4: len 8; hex 0000000000000000; asc ;; 5: len 8; hex 0000000000af9ad0; asc ;; 6: len 8; hex 0000000000000000; asc ;; 7: len 4; hex 80000000; asc ;; 8: len 4; hex 80000000; asc ;; 9: len 4; hex 80000000; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 14 page no 57 n bits 264 index `PRIMARY` of table `zabbix/escalations` trx id 0 1309159606 lock_mode X waiting Record lock, heap no 188 PHYSICAL RECORD: n_fields 10; compact format; info bits 32 0: len 8; hex 000000000075af20; asc u ;; 1: len 6; hex 00004e0830b6; asc N 0 ;; 2: len 7; hex 0000002b5704b1; asc +W ;; 3: len 8; hex 000000000000000d; asc ;; 4: len 8; hex 0000000000000000; asc ;; 5: len 8; hex 0000000000af9ad0; asc ;; 6: len 8; hex 0000000000000000; asc ;; 7: len 4; hex 80000000; asc ;; 8: len 4; hex 80000000; asc ;; 9: len 4; hex 80000000; asc ;; *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 0 1309167592 Purge done for trx's n:o < 0 1309162234 undo n:o < 0 0 History list length 921 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 9212, OS thread id 1101621568 MySQL thread id 18405, query id 46703780 localhost root SHOW ENGINE INNODB STATUS ---TRANSACTION 0 1309162239, not started, process no 9212, OS thread id 1184614720 MySQL thread id 43, query id 46624922 localhost zabbix ---TRANSACTION 0 1309166313, not started, process no 9212, OS thread id 1184082240 MySQL thread id 41, query id 46682100 localhost zabbix ---TRANSACTION 0 1309163262, not started, process no 9212, OS thread id 1184348480 MySQL thread id 42, query id 46640462 localhost zabbix ---TRANSACTION 0 1309166295, not started, process no 9212, OS thread id 1184880960 MySQL thread id 44, query id 46681911 localhost zabbix ---TRANSACTION 0 1309155236, not started, process no 9212, OS thread id 1183017280 MySQL thread id 32, query id 46697449 localhost zabbix ---TRANSACTION 0 1309166479, not started, process no 9212, OS thread id 1183816000 MySQL thread id 40, query id 46685672 localhost zabbix ---TRANSACTION 0 1309167518, not started, process no 9212, OS thread id 1101089088 MySQL thread id 37, query id 46702000 localhost zabbix ---TRANSACTION 0 1309167577, not started, process no 9212, OS thread id 1101355328 MySQL thread id 38, query id 46703766 localhost zabbix ---TRANSACTION 0 1309167581, not started, process no 9212, OS thread id 1182218560 MySQL thread id 29, query id 46703770 localhost zabbix ---TRANSACTION 0 1309167446, not started, process no 9212, OS thread id 1176361280 MySQL thread id 7, query id 46700688 localhost zabbix ---TRANSACTION 0 1309167579, not started, process no 9212, OS thread id 1185147200 MySQL thread id 45, query id 46703768 localhost zabbix ---TRANSACTION 0 1309167536, not started, process no 9212, OS thread id 1180887360 MySQL thread id 24, query id 46702361 localhost zabbix ---TRANSACTION 0 1309167534, not started, process no 9212, OS thread id 1100822848 MySQL thread id 36, query id 46702359 localhost zabbix ---TRANSACTION 0 1309167511, not started, process no 9212, OS thread id 1181952320 MySQL thread id 28, query id 46701553 localhost zabbix ---TRANSACTION 0 1309167031, not started, process no 9212, OS thread id 1176095040 MySQL thread id 6, query id 46696264 localhost zabbix ---TRANSACTION 0 1309167529, not started, process no 9212, OS thread id 1181419840 MySQL thread id 26, query id 46702181 localhost zabbix ---TRANSACTION 0 1309167080, not started, process no 9212, OS thread id 1180621120 MySQL thread id 23, query id 46697453 localhost zabbix ---TRANSACTION 0 1309167372, not started, process no 9212, OS thread id 1182484800 MySQL thread id 30, query id 46699977 localhost zabbix ---TRANSACTION 0 1309167590, not started, process no 9212, OS thread id 1183549760 MySQL thread id 34, query id 46703779 localhost zabbix ---TRANSACTION 0 1309167558, not started, process no 9212, OS thread id 1181153600 MySQL thread id 25, query id 46703522 localhost zabbix ---TRANSACTION 0 1309167544, not started, process no 9212, OS thread id 1181686080 MySQL thread id 27, query id 46702889 localhost zabbix ---TRANSACTION 0 1309167033, not started, process no 9212, OS thread id 1176627520 MySQL thread id 8, query id 46696265 localhost zabbix ---TRANSACTION 0 1309167487, not started, process no 9212, OS thread id 1180088640 MySQL thread id 21, query id 46700830 localhost zabbix ---TRANSACTION 0 1308633682, not started, process no 9212, OS thread id 1179556160 MySQL thread id 19, query id 46688063 localhost zabbix ---TRANSACTION 0 1309167491, not started, process no 9212, OS thread id 1100036416 MySQL thread id 4, query id 46701494 localhost zabbix ---TRANSACTION 0 1309167563, not started, process no 9212, OS thread id 1100302656 MySQL thread id 5, query id 46703744 localhost zabbix ---TRANSACTION 0 1309166242, not started, process no 9212, OS thread id 1099770176 MySQL thread id 3, query id 46680958 localhost zabbix ---TRANSACTION 0 1309162230, ACTIVE 109 sec, process no 9212, OS thread id 1075734848, thread declared inside InnoDB 51 mysql tables in use 1, locked 0 MySQL thread id 35, query id 46624910 localhost zabbix Sending data select count(*) from history Trx read view will not see trx with id >= 0 1309162231, sees < 0 1309162231 -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 2732724 OS file reads, 1353767 OS file writes, 103404 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 18.11 writes/s, 1.44 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 858, seg size 860, 43843 inserts, 43843 merged recs, 6749 merges Hash table size 53124517, used cells 37607794, node heap has 96409 buffer(s) 28148.43 hash searches/s, 3621.49 non-hash searches/s --- LOG --- Log sequence number 174 3599694510 Log flushed up to 174 3599687481 Last checkpoint at 174 3016664921 1 pending log writes, 0 pending chkp writes 920882 log i/o's done, 8.11 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 29690863256; in additional pool allocated 1048576 Buffer pool size 1638400 Free buffers 148 Database pages 1541843 Modified db pages 105853 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 7979978, created 72377, written 734627 0.00 reads/s, 0.89 creates/s, 15.22 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 1 queries inside InnoDB, 0 queries in queue 2 read views open inside InnoDB Main thread process no. 9212, id 1175828800, state: flushing log Number of rows inserted 8256122, updated 8520531, deleted 10025415, read 44545542888 148.09 inserts/s, 152.09 updates/s, 5.22 deletes/s, 1172040.55 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================
Code:
7048:20101126:141112.192 [Z3005] Query failed: [1213] Deadlock found when trying to get lock; try restarting transaction [update escalations set status=4 where actionid=64 and triggerid=0 and esc_step=0 and status=0] 7126:20101126:141112.198 [Z3005] Query failed: [1213] Deadlock found when trying to get lock; try restarting transaction [delete from escalations where escalationid=7378780 and status=4] 7048:20101126:141112.270 [Z3005] Query failed: [1213] Deadlock found when trying to get lock; try restarting transaction [update escalations set status=4 where actionid=64 and triggerid=0 and esc_step=0 and status=0] 7048:20101126:141112.310 [Z3005] Query failed: [1213] Deadlock found when trying to get lock; try restarting transaction [update escalations set status=4 where actionid=64 and triggerid=0 and esc_step=0 and status=0]
Code:
[mysqld] skip-bdb tmpdir=/var/tmp/mysqltmp datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql innodb_file_per_table=1 innodb_flush_method=O_DIRECT innodb_log_file_size=512M innodb_log_buffer_size=4M innodb_buffer_pool_size=25G innodb_thread_concurrency=8 query_cache_limit=1M query_cache_size=512M tmp_table_size=256M max_heap_table_size=256M max_connections = 400 innodb_flush_log_at_trx_commit=2 join_buffer_size=2048K read_buffer_size=256k read_rnd_buffer_size=256k thread_cache_size=4 table_cache=102
4x Intel(R) Xeon(TM) MP CPU 3.16GHz
32 GB Ram
os: Centos5
More Infos needed?
rgds flo
Comment