Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-813

Long running queries slow down zabbix and cause many other side effects

    XMLWordPrintable

Details

    • Incident report
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 1.6
    • None
    • None
    • None
    • OS: Red Hat Enterprise 5.2 x86_64
      MySQL: 5.1.30
      Zabbix: 1.6.2

    Description

      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.

      Attachments

        Activity

          People

            alexei Alexei Vladishev
            ataylo13 Brandon Taylor
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: