Transaction timeout

[safl]

Regards!

My zabbix installation has started failing after an upgrade to beta6. Problem is this:

Query::update alerts set retries=3,error='Trigger changed its status. WIll not send repeats.' where triggerid=31134 and repeats>0 and status=0
020673:20060222:101159 Query failed:Lock wait timeout exceeded; try restarting transaction [1205]

Problem seems to be similar to:

http://www.zabbix.com/forum/showthread.php?t=823&goto=nextnewest

Please help me fix this!

When i look at 'mytop', i've got these queries:

36053 surv localhost surv 0 Sleep
36054 surv localhost surv 0 Sleep
36478 surv localhost surv 0 Query show full processlist
36041 surv localhost surv 1 Query update alerts set repeats=repeats+1, nextcheck=1140596654 where alertid=639514
36050 surv localhost surv 1 Query update alerts set retries=3,error='Trigger changed its status. WIll not send repeats.' where
36052 surv localhost surv 1 Query select count(*),min(nextcheck) from items i,hosts h where h.status=0 and h.disable_until<1140
36394 surv localhost surv 3 Sleep
36048 surv localhost surv 5 Query update alerts set retries=3,error='Trigger changed its status. WIll not send repeats.' where
36051 surv localhost surv 5 Query update alerts set retries=3,error='Trigger changed its status. WIll not send repeats.' where
36049 surv localhost surv 14 Query update alerts set retries=3,error='Trigger changed its status. WIll not send repeats.' where
36042 surv localhost surv 22 Query update alerts set retries=3,error='Trigger changed its status. WIll not send repeats.' where
36045 surv localhost surv 30 Query update alerts set retries=3,error='Trigger changed its status. WIll not send repeats.' where
36047 surv localhost surv 38 Query update alerts set retries=3,error='Trigger changed its status. WIll not send repeats.' where
36459 surv localhost surv 38 Sleep
36046 surv localhost surv 46 Query update alerts set retries=3,error='Trigger changed its status. WIll not send repeats.' where
36365 surv localhost surv 54 Sleep

[safl]

I've saw this same behaviour three days ago, the only way i could resolve it.
Was to dump the entire db. Delete the data files and import all data back into the DB. To release the deadlocks.

Know that the issue is appearing again i would very much like to find another way to resolve the deadlock issue.

Does anybody know why beta6 executes so many:

EXPLAIN update alerts set retries=3,error='Trigger changed its status. WIll not send repeats.' where triggerid=31016 and repeats>0 and status=0:

I am having many of these queries and they are taking approximately 30-60 seconds to execute.
When it goes bad.

[Alexei]

Please run "mysqladmin processlist" and post result here. Thanks.

[safl]

I've dropped and recreated the alerts table, zabbix is working properly now. So the information from processlist is no longer usefull since the alert table updates are no longer a problem and the deadlocks are no more.


The alerts table was quite big >250Mb.
But i've stumpled across a couple of queries that could be optimized with a proper index:


| 36045 | surv | localhost | surv | Query | 0 | Copying to tmp table | select distinct t.triggerid,t.expression,t.status,t.dep_level,t.pr iority,t.value,t.description from |
| 36052 | surv | localhost | surv | Query | 0 | Copying to tmp table | select i.itemid,i.key_,h.host,h.port,i.delay,i.descriptio n,i.nextcheck,i.type,i.snmp_community,i.snm |
| 38655 | surv | localhost | surv | Query | 0 | Copying to tmp table | select distinct h.hostid from hosts h, items i, functions f, triggers t where h.hostid=i.hostid and |

These doesn't seem to be related to alerts.

[Alexei]

But i've stumpled across a couple of queries that could be optimized with a proper index.
What queries?

[safl]

The one i listed.

[Alexei]

They are truncated! What new indexes you'd suggest?

[safl]

I really don't have enough insight into the DB to propose a meaningfull index, right now.

But since mysql is making tmp tables, like here:

36053 | surv | localhost | surv | Query | 0 | Copying to tmp table | select i.itemid,i.key_,h.host,h.port,i.delay,i.descriptio n,i.nextcheck,i.type,i.snmp_community,i.snm

Would't adding proper index's, prevent the creation of tmp tables?

[Alexei]

Would't adding proper index's, prevent the creation of tmp tables?
No, it wouldn't.