Deadlock detected

[avecsi]

Hi All,

Zabbix Server. Zabbix 2.0.2
Zabbix Agents Zabbix 1.8.11 (revision 26205)
Package: postgresql-9.1
New: yes
State: installed
Automatically installed: yes
Version: 9.1.11-0ubuntu0.12.04

I found this logs in the zabbix server logs:

4472:20140219:103904.528 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: deadlock detected
DETAIL: Process 30323 waits for ShareLock on transaction 157609095; blocked by process 30321.
Process 30321 waits for ShareLock on transaction 157609101; blocked by process 30318.
Process 30318 waits for ExclusiveLock on tuple (5,38) of relation 29985 of database 29898; blocked by process 30323.
HINT: See server log for query details.
[update ids set nextid=nextid+1 where nodeid=0 and table_name='events' and field_name='eventid']
zabbix_server [4472]: ERROR [file:db.c,line:1412] Something impossible has just happened.

4469:20140219:103905.530 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: deadlock detected
DETAIL: Process 30318 waits for ShareLock on transaction 157609095; blocked by process 30321.
Process 30321 waits for ShareLock on transaction 157609101; blocked by process 30318.
HINT: See server log for query details.
[update ids set nextid=nextid+12 where nodeid=0 and table_name='events' and field_name='eventid']
zabbix_server [4469]: ERROR [file:db.c,line:1412] Something impossible has just happened.

I found this logs in the DB server logs:

2014-02-19 10:39:04 GMT ERROR: deadlock detected
2014-02-19 10:39:04 GMT DETAIL: Process 30323 waits for ShareLock on transaction 157609095; blocked by process 30321.
Process 30321 waits for ShareLock on transaction 157609101; blocked by process 30318.
Process 30318 waits for ExclusiveLock on tuple (5,38) of relation 29985 of database 29898; blocked by process 30323.
Process 30323: update ids set nextid=nextid+1 where nodeid=0 and table_name='events' and field_name='eventid'
Process 30321: update triggers set value_flags=1,error='Received value [timeout while executing a shell script] is not suitable for value type [Numeric (unsigned)] and data type [Decim' where triggerid=14274;
insert into events (eventid,source,object,objectid,clock,ns,value,val ue_changed) values (2364923,0,0,14274,1392806204,0,2,0);

Process 30318: update ids set nextid=nextid+12 where nodeid=0 and table_name='events' and field_name='eventid'
2014-02-19 10:39:04 GMT HINT: See server log for query details.
2014-02-19 10:39:04 GMT STATEMENT: update ids set nextid=nextid+1 where nodeid=0 and table_name='events' and field_name='eventid'
2014-02-19 10:39:05 GMT ERROR: deadlock detected
2014-02-19 10:39:05 GMT DETAIL: Process 30318 waits for ShareLock on transaction 157609095; blocked by process 30321.
Process 30321 waits for ShareLock on transaction 157609101; blocked by process 30318.
Process 30318: update ids set nextid=nextid+12 where nodeid=0 and table_name='events' and field_name='eventid'
Process 30321: update triggers set value_flags=1,error='Received value [timeout while executing a shell script] is not suitable for value type [Numeric (unsigned)] and data type [Decim' where triggerid=14274;
insert into events (eventid,source,object,objectid,clock,ns,value,val ue_changed) values (2364923,0,0,14274,1392806204,0,2,0);

After I restarted the zabbix-server service all the test came back to normal (real) status, after the deadlock all alerts went to Disaster.

I appreciate any help about deadlocks on postgresql database.

Thanks, Andrew

[avecsi]

Status of zabbix:

Number of hosts (monitored/not monitored/templates)
296 (165 / 59 / 72)
Number of items (monitored/disabled/not supported)
11449 (9077 / 2011 / 361)
Number of triggers (enabled/disabled)[problem/unknown/ok]
3554 (3433 / 121) [62 / 0 / 3371]
Number of users (online)
24 (9)

Required server performance, new values per second
270.06

[avecsi]

my database log are fulled with the followings:

2014-02-20 11:45:22 GMT LOG: checkpoints are occurring too frequently (11 seconds apart)
2014-02-20 11:45:22 GMT HINT: Consider increasing the configuration parameter "checkpoint_segments".
2014-02-20 11:45:37 GMT LOG: checkpoints are occurring too frequently (15 seconds apart)
2014-02-20 11:45:37 GMT HINT: Consider increasing the configuration parameter "checkpoint_segments".
2014-02-20 11:45:48 GMT LOG: checkpoints are occurring too frequently (11 seconds apart)


Any advice/hint where should I start the optimization?

Thanks.