Ubuntu 12.04 LTS, x86_64, 9GB RAM
PostgreSQL 9.1.4, pgbouncer for the web frontend
Zabbix 2.0.0, dbForBix
We're experiencing huge problems due to (apparently) database locks. Soon after all services start (zabbix-server, jmx monitoring process, dbforbix) the postgreSQL log starts showing deadlocks on apparently extremely simple queries on the "ids" table.
postgres: [2-1] 2012-07-04 15:01:40 CEST ERROR: deadlock detected
postgres: [2-2] 2012-07-04 15:01:40 CEST DETAIL: Process 10453 waits for ShareLock on transaction 1096711; blocked by process 10439.
postgres: [2-3] #011Process 10439 waits for ShareLock on transaction 1096420; blocked by process 10453.
postgres: [2-4] #011Process 10453: update ids set nextid=nextid+1 where nodeid=0 and table_name='triggers' and field_name='triggerid'
postgres: [2-5] #011Process 10439: update ids set nextid=nextid+3 where nodeid=0 and table_name='functions' and field_name='functionid'
The History Syncer Processes show as busy 100% and the database sharelocks spike, and everything grinds to a halt: no data is collected for a while, then (probably as transactions die in postgres for timeouts) everything goes back to normal for a few minutes. Then, the process starts again: big db lock spike, busy syncer, all blocked.
The behaviour is clearly cyclic, though not regular: every (roughly) half hour the db locks peak, then come down again to zero, then go up again.
ZBX-5225 Frequent "Lock wait timeout exceeded; try restarting transaction" on zabbix_server. LLD related ?