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

Frontend query runs much faster, if changed

XMLWordPrintable

    • Icon: Incident report Incident report
    • Resolution: Won't fix
    • Icon: Major Major
    • 4.4 (plan)
    • 4.0.5
    • Frontend (F)
    • Percona MySql 8.0.13 on server with 20 cores and 32GB ram
    • Sprint 56 (Sep 2019), Sprint 55 (Aug 2019), Sprint 52 (May 2019), Sprint 53 (Jun 2019), Sprint 54 (Jul 2019), Sprint 57 (Oct 2019), Sprint 58 (Nov 2019), Sprint 59 (Dec 2019), Sprint 60 (Jan 2020), Sprint 61 (Feb 2020), Sprint 62 (Mar 2020), Sprint 63 (Apr 2020), Sprint 64 (May 2020), Sprint 65 (Jun 2020), Sprint 66 (Jul 2020), Sprint 67 (Aug 2020), Sprint 68 (Sep 2020), Sprint 69 (Oct 2020), Sprint 70 (Nov 2020)

      SELECT DISTINCT p.eventid,p.objectid,p.clock,p.ns,p.name,p.severity 
      FROM problem p,functions f,items i,hosts_groups hg 
      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 ('14','30') 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 p.objectid=f.triggerid 
      AND f.itemid=i.itemid 
      AND i.hostid=hg.hostid 
      AND hg.groupid='68' 
      AND NOT EXISTS (SELECT NULL FROM event_suppress es WHERE es.eventid=p.eventid) 
      AND (p.r_eventid IS NULL OR p.r_clock>1552377061) 
      AND p.eventid<='5404925' 
      ORDER BY p.eventid DESC LIMIT 101
      

      15,631 sec.
      4,617 sec.
      6,256 sec.
      6,583 sec.
      12,277 sec.
      00:01:04.7
      38,923 sec.
      5,304 sec.
      33,806 sec.
      38,486 sec.

      SELECT *
      FROM (
      SELECT DISTINCT p.eventid,p.objectid,p.clock,p.ns,p.name,p.severity 
      FROM problem p,functions f,items i,hosts_groups hg 
      WHERE p.source='0' 
      AND p.object='0' 
      AND p.objectid=f.triggerid 
      AND f.itemid=i.itemid 
      AND i.hostid=hg.hostid 
      AND hg.groupid='68' 
      AND NOT EXISTS (SELECT NULL FROM event_suppress es WHERE es.eventid=p.eventid) AND (p.r_eventid IS NULL OR p.r_clock>1552377061) AND p.eventid<='5404925' ) a
      WHERE 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 ('14','30') WHERE a.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) 
      ORDER BY a.eventid DESC LIMIT 101
      

      1,435 sec.
      1,779 sec.
      1,592 sec.
      1,826 sec.
      1,482 sec
      1,638 sec.
      1,419 sec.
      2,091 sec.
      1,903 sec.

        1. CAction.php
          122 kB
          Kaspars Mednis
        2. CAction.php.org
          120 kB
          Kaspars Mednis

            Miks.Kronkalns Miks Kronkalns
            zux Edgars Melveris
            Team B
            Votes:
            4 Vote for this issue
            Watchers:
            12 Start watching this issue

              Created:
              Updated:
              Resolved: