Deadlock history buildup

[xs-]

Hi all,

Anyone experiencing similar behaviour?
when i check 'show innodb status' a day after (re)starting zabbix_server + mysqld (seperate servers on my side tho) i already get a huge list of deadlock history (long enough that 'show innodb status' cant complete the output due to the length of the deadlock list).
Mind you, its not active deadlocks, just a history of them.
Seems to me this happens quite a lot and a bit too much if you ask me.

I am not using the DBCacher module as i haven't seen mention of it being stable enough.

[Alexei]

I am not using the DBCacher module...

This is strange. Could you give us one or two failed queries? BTW, the deadlock problem with DBSyncer is already fixed in pre 1.6.3.

[xs-]

could you give me a hint as to which queries to get where?
Do you want the deadlock output from 'show innodb status' or should i enable some deadlock logging?

thanks for the update on dbsyncer, ill update to 1.6.3-pre when i get some time.

[Alexei]

could you give me a hint as to which queries to get where?

The queries must be in the Zabbix server log file. I am not sure how to log them by MySQL itself.

[xs-]

grep -i deadlock zabbix_server.log
(part of the output)
---------------------------8<--------------------------
21226:20090203:152320 [Z3005] Query failed: [1213] Deadlock found when trying to get lock; try restarting transaction [insert into node_cksu
m (nodeid,tablename,recordid,cksumtype,cksum) select 1,'slideshows',slideshowid,1,concat_ws(',',md5(nam e),delay) from slideshows where 1=1 an
d slideshowid between 100000000000000 and 199999999999999
21226:20090203:152738 [Z3005] Query failed: [1213] Deadlock found when trying to get lock; try restarting transaction [insert into node_cksu
m (nodeid,tablename,recordid,cksumtype,cksum) select 1,'slideshows',slideshowid,1,concat_ws(',',md5(nam e),delay) from slideshows where 1=1 an
d slideshowid between 100000000000000 and 199999999999999
21226:20090203:152949 [Z3005] Query failed: [1213] Deadlock found when trying to get lock; try restarting transaction [insert into node_cksu
m (nodeid,tablename,recordid,cksumtype,cksum) select 1,'slideshows',slideshowid,1,concat_ws(',',md5(nam e),delay) from slideshows where 1=1 an
d slideshowid between 100000000000000 and 199999999999999
21226:20090203:153158 [Z3005] Query failed: [1213] Deadlock found when trying to get lock; try restarting transaction [insert into node_cksu
m (nodeid,tablename,recordid,cksumtype,cksum) select 1,'slideshows',slideshowid,1,concat_ws(',',md5(nam e),delay) from slideshows where 1=1 an
d slideshowid between 100000000000000 and 199999999999999
---------------------------8<--------------------------

[Hauke]

Which Linux distribution?
My Ubuntu server is checking the MySQL tables after an MySQL startup which take's a looong time with high disc load (9GB database), it isn''t good to start Zabbix while this check is running.
Maybe you have a similar problem.

[xs-]

Yes Ubuntu does this (also in my case, although i am at the point where i have had enough of that and will turn it off soon).
But that shouldnt cause deadlocks.

The piece of log i pasted in my previous post is from far after that point tho.

[xs-]

Small update:

Using svn checkout build from today [6725].
Env: Mysql5, DM, DBSync=1

19695:20090216:115745 [Z3005] Query failed: [1213] Deadlock found when trying to get lock; try restarting transaction [insert into node_cksum (nodeid,tab
lename,recordid,cksumtype,cksum) select 1,'slideshows',slideshowid,1,concat_ws(',',md5(nam e),delay) from slideshows where 1=1 and slideshowid between 1000
00000000000 and 199999999999999
union all select 1,'users',userid,1,concat_ws(',',md5(alias),md5(na me),md5(surname),md5(passwd),md5(url),autologin,au tologout,md5(lang),refresh,type,md5(t
heme),attempt_failed,md5(attempt_ip),attempt_clock ) from users where 1=1 and userid between 100000000000000 and 199999999999999
19695:20090216:115956 [Z3005] Query failed: [1213] Deadlock found when trying to get lock; try restarting transaction [insert into node_cksum (nodeid,tab
lename,recordid,cksumtype,cksum) select 1,'slideshows',slideshowid,1,concat_ws(',',md5(nam e),delay) from slideshows where 1=1 and slideshowid between 1000
00000000000 and 199999999999999
union all select 1,'users',userid,1,concat_ws(',',md5(alias),md5(na me),md5(surname),md5(passwd),md5(url),autologin,au tologout,md5(lang),refresh,type,md5(t
heme),attempt_failed,md5(attempt_ip),attempt_clock ) from users where 1=1 and userid between 100000000000000 and 199999999999999
19695:20090216:120206 [Z3005] Query failed: [1213] Deadlock found when trying to get lock; try restarting transaction [insert into node_cksum (nodeid,tab
lename,recordid,cksumtype,cksum) select 1,'slideshows',slideshowid,1,concat_ws(',',md5(nam e),delay) from slideshows where 1=1 and slideshowid between 1000
00000000000 and 199999999999999
union all select 1,'users',userid,1,concat_ws(',',md5(alias),md5(na me),md5(surname),md5(passwd),md5(url),autologin,au tologout,md5(lang),refresh,type,md5(t
heme),attempt_failed,md5(attempt_ip),attempt_clock ) from users where 1=1 and userid between 100000000000000 and 199999999999999

[Alexei]

This, along with other DM related issues, will be addressed prior to release of 1.6.3.