zabbix mysql problems

[krisjturn]

I am just wondering if switching to Protoges database would be better. Sorry for the misspelling I am not a Linux guy and just learning this stuff.

[ataylo13]

@richlv I haven't found anything of interest in debug mode... Zabbix is chugging along as if everything is fine. I do get some queries in MySQL that run for thousands of seconds... (copy to tmp table) is status of almost all of them. If I kill the queries, everything goes back to normal.

This one in particular is killing me:

SELECT DISTINCT g.groupid,g.name FROM groups g, hosts_groups hg, hosts h WHERE (g.groupid IN (100100000000008,100100000000013,100100000000018,1 00100000000016,100100000000017,100100000000015,100 100000000002,100100000000026,100100000000007,10010 0000000009,100100000000006,100100000000014,1001000 00000003,100100000000004)) AND hg.groupid=g.groupid AND h.hostid=hg.hostid AND h.status=0 AND EXISTS(SELECT DISTINCT i.itemid FROM items i, graphs_items gi WHERE i.hostid=h.hostid AND i.itemid=gi.itemid) ORDER BY g.name

The result is 14 rows and is just a list of my host groups... I ran by hand and it took 18mins!!!

[richlv]

The result is 14 rows and is just a list of my host groups... I ran by hand and it took 18mins!!!

that sounds like a reason, yes.
maybe it's worth reporting this on the tracker, listing all the details

[Calimero]

Regarding the Graph screen, queries in 1.6.1-1.6.2 are quite horrible. I assume the way the queries are written is due to portability issues.

The extensive ACL system isn't here to help.

I've patched charts.php to rewrite queries using simple JOINs instead of subselects. I've also removed fine permission checks (only admins have access to zabbix so we don't care abound fine grained ACLs). Some of the queries went from 30sec to 0.1sec and now the Graph screen works pretty good now.

I haven't checked whether 1.6.3 has improvements in that area.

I've also experienced lock timeouts. I haven't spend to much time on it. My work around was to correct items created by one of my colleagues. Polling hundreds of items every 30sec is definitely a waste of CPU.

[ataylo13]

I have found the query in charts.php... the page never comes up... what is it?

[richlv]

maybe this thread is relevant : http://www.zabbix.com/forum/showthread.php?t=11782

[ataylo13]

charts.php = Graphs...

Thanks for the link. That looks like it is the cause to one of my problems.