-
Incident report
-
Resolution: Fixed
-
Major
-
None
-
1.6
-
None
-
None
-
OS: Red Hat Enterprise 5.2 x86_64
MySQL: 5.1.30
Zabbix: 1.6.2
I am having issues where certain queries are running for 10 of thousands of seconds and never completing. They cause proxy issues and severely impact the web server.
A sample from "Show full processlist;":
All of these processes are in state "Copy to tmp table" (I have my temp table configured as: tempdir = /dev/shm)
Running for 53,000 seconds:
SELECT COUNT(DISTINCT g.groupid) as grpcount, MAX(g.groupid) as groupid FROM groups g, hosts_groups hg, hosts h WHERE (g.groupid IN (100100000000008,100100000000013,100100000000018,100100000000023,100100000000021,100100000000022,100100000000020,100100000000024,100100000000025,100100000000016,100100000000017,100100000000015,100100000000002,100100000000026,100100000000019,100100000000007,100100000000009,100100000000006,100100000000001,100100000000012,100100000000014,100100000000003,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)
Running for 53,000 seconds:
SELECT COUNT(DISTINCT g.groupid) as grpcount, MAX(g.groupid) as groupid FROM groups g, hosts_groups hg, hosts h WHERE (g.groupid IN (100100000000008,100100000000013,100100000000018,100100000000023,100100000000021,100100000000022,100100000000020,100100000000024,100100000000025,100100000000016,100100000000017,100100000000015,100100000000002,100100000000026,100100000000019,100100000000007,100100000000009,100100000000006,100100000000001,100100000000012,100100000000014,100100000000003,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)
Running for 44,000 seconds:
SELECT DISTINCT g.groupid,g.name FROM groups g, hosts_groups hg, hosts h WHERE (g.groupid IN (100100000000008,100100000000013,100100000000018,100100000000023,100100000000021,100100000000022,100100000000020,100100000000024,100100000000025,100100000000016,100100000000017,100100000000015,100100000000002,100100000000026,100100000000019,100100000000007,100100000000009,100100000000006,100100000000001,100100000000012,100100000000014,100100000000003,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
Running for 11,0000 seconds:
SELECT DISTINCT g.groupid,g.name FROM groups g, hosts_groups hg, hosts h WHERE (g.groupid IN (100100000000008,100100000000013,100100000000018,100100000000023,100100000000021,100100000000022,100100000000020,100100000000024,100100000000025,100100000000016,100100000000017,100100000000015,100100000000002,100100000000026,100100000000019,100100000000007,100100000000009,100100000000006,100100000000001,100100000000012,100100000000014,100100000000003,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
let me know what other information you may need to troubleshoot this issue.