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

Non-optimized queries into Monitoring-Latest Data and Monitoring Problems

    XMLWordPrintable

Details

    • Problem report
    • Status: Confirmed
    • Major
    • Resolution: Unresolved
    • 5.4.10
    • None
    • Frontend (F)
    • None
    • Zabbix-web-mysql 5.4.7
      MariaDB 10.3

    Description

      Query

      SELECT h.hostid,h.name,h.status FROM hosts h WHERE h.flags IN (0,4) AND EXISTS (SELECT NULL FROM hosts_groups hgg JOIN rights r ON r.id=hgg.groupid AND r.groupid IN (11,13) WHERE h.hostid=hgg.hostid GROUP BY hgg.hostid HAVING MIN(r.permission)>0 AND MAX(r.permission)>='2') AND h.status=0
      

      running for 113 seconds in a prod environment for non-Super-admin users
      Recommended optimization

      SELECT h.hostid,h.name,h.status FROM hosts h, hosts_groups hg
      WHERE h.hostid=hg.hostid 
       and hg.groupid in
         (select id from rights 
           where groupid in (11,13) and permission>='2') ; 
      

      affects just milliseconds for query execution
      Alternative - two queries

      select id from rights where groupid in (11,13) and permission>='2';
      
      SELECT h.hostid,h.name,h.status FROM hosts h, hosts_groups hg 
      WHERE h.hostid=hg.hostid 
      and hg.groupid in ( ** FIRST QUERY RESULTS ** ) 
      and h.flags IN (0,4) AND h.status=0 limit 1000;
      

      The same logic could be implemented for query

      SELECT p.eventid,p.objectid,p.clock,p.ns,p.name,p.severity FROM problem p WHERE p.source='0' AND p.object='0' AND NOT EXISTS (SELECT NULL FROM functions f,items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid IN (11,13) WHERE p.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MAX(permission)<2 OR MIN(permission) IS NULL OR MIN(permission)=0) AND NOT EXISTS (SELECT NULL FROM event_suppress es WHERE es.eventid=p.eventid) AND (p.r_eventid IS NULL OR p.r_clock>1645536316) ORDER BY p.eventid DESC LIMIT 1001
      

      Related ZBXs:
      ZBX-7706

      ZBX-12867.

      Attachments

        Activity

          People

            zabbix.dev Zabbix Development Team
            igorbach Igor Gorbach
            Votes:
            1 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated: