[ZBX-2897] Zabbix eats up MySQL server connections Created: 2010 Aug 18  Updated: 2017 May 30  Resolved: 2011 Aug 20

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Server (S)
Affects Version/s: 1.8.2
Fix Version/s: 1.8.7, 1.9.6 (beta)

Type: Incident report Priority: Major
Reporter: troy dias Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Hardware: Centos 5.3 (x86_64)
Zabbix: 1.8.2
MySQL: 5.0.45, innodb engine
Apache: 2.2.3

Issue Links:
Duplicate
duplicates ZBX-3889 MySQL InnoDB deadlocks are not handle... Closed
duplicates ZBX-1401 Zabbix_server continues failed transa... Closed
duplicates ZBX-1402 Zabbix_server creates deadlocks in tr... Closed

 Description   

Almost once a day, the MySQL database stops accepting connections, which causes zabbix-server to stop. A 'show processlist' indicates most connection threads are attempting to update:

UPDATE ids SET nextid=nextid+1 WHERE nodeid=0 AND table_name='auditlog' AND field_name='auditid'

Doubling max_connections on MySQL doesn't help. The problem seems to occur more frequently when more users are viewing the webUI dashboard.

 Comments   
Comment by richlv [ 2010 Aug 19 ]

correlation with web frontend usage might be similar to deadlock issue ZBX-2494, although supposedly then server should just stop instead of building up connections

Comment by troy dias [ 2010 Aug 20 ]

A 'show engine innodb status' during the outage indicates a fair few of the following:

------------------
---TRANSACTION 0 73532040, ACTIVE 7 sec, process no 17707, OS thread id 1212954944 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 368
MySQL thread id 61967, query id 26169184 zabsvr01 10.250.18.35 zabbix Updating
UPDATE ids SET nextid=nextid+1 WHERE nodeid=0 AND table_name='auditlog' AND field_name='auditid'
------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12 page no 3 n bits 104 index `PRIMARY` of table `zabbix/ids` trx id 0 73532040 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000000; asc ;; 1: len 8; hex 61756469746c6f67; asc auditlog;; 2: len 7; hex 61756469746964; asc auditid;; 3: len 6; hex 000004620284; asc b ;; 4: len 7; hex 000000014212a7; asc B ;; 5: len 8; hex 000000000081c4c9; asc ;;

------------------

I've converted the 'ids' table from innodb to myisam incase it is a resource issue.

Comment by Alexander Vladishev [ 2011 Aug 20 ]

Fixed in ZBX-3889 in version pre1.8.7, r21259

Generated at Sat Oct 25 09:07:22 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.