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

Optimize SQL to "problem" page

    XMLWordPrintable

    Details

    • Team:
      Team B
    • Sprint:
      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)

      Description

      We suggest to optimize SQLs, where permission is checked:
      For example:

      SELECT * FROM (SELECT DISTINCT p.eventid,p.objectid,p.clock,p.ns,p.name,p.severity FROM problem p,functions f,items i 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 (18,28,44,62,76,120,148) 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 IN (21496,21501,21506,21507,21508,21509,21510,21511,21521,21524,21528,21531,21907,30532,31249,31250,32555,32679,32680,33533,33534,33535,33536,33537,33538,33539,34167,34683,36197,36198,36199,36200,42649) AND p.severity IN (3,4) AND p.r_eventid IS NULL ORDER BY p.eventid DESC) WHERE rownum BETWEEN 0 AND 501
      

      this SQL takes more than a minute and on bit sets od data - even longer, 5-10 minutes

      Optimized version:

      SELECT *
        FROM (
                SELECT DISTINCT p.eventid,
                              p.objectid,
                              p.clock,
                              p.ns,
                              p.name,
                              p.severity
                FROM
                  problem p
                    inner join functions f on
                          p.objectid = f.triggerid
                          and p.source = 0
                          and p.object = 0
                    inner join items i on
                          f.itemid = i.itemid
                          AND i.hostid IN (21496,21501,21506,21507,21508,21509,21510,21511,21521,21524,21528,21531,21907,30532,31249,31250,32555,32679,32680,33533,33534,33535,33536,33537,33538,33539,34167,34683,36197,36198,36199,36200,42649)
                          AND p.severity IN (3, 4)
                          AND p.r_eventid IS NULL
               WHERE 
                  NOT EXISTS (
                    SELECT
                        null
                        FROM 
                          functions f
                            inner join items i on f.itemid = i.itemid
                            inner join hosts_groups hgg on i.hostid = hgg.hostid
                            LEFT JOIN rights r ON r.id = hgg.groupid AND r.groupid IN (18, 28, 44, 62, 76, 120, 148)
                       WHERE
                         p.objectid = f.triggerid
                         and nvl(r.permission,0) = 0
                 )
                 and exists (
                    SELECT
                        null
                        FROM 
                          functions f
                            inner join items i on f.itemid = i.itemid
                            inner join hosts_groups hgg on i.hostid = hgg.hostid
                            LEFT JOIN rights r ON r.id = hgg.groupid AND r.groupid IN (18, 28, 44, 62, 76, 120, 148)
                       WHERE
                         p.objectid = f.triggerid
                         and nvl(r.permission,0) in (2,3)
                 )
               ORDER BY p.eventid DESC
        )
      WHERE
        rownum BETWEEN 0 AND 501
      

      What is optimized:
      In original query, this block:

      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 (18,28,44,62,76,120,148) 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)
      

      replace by:

                  NOT EXISTS (
                    SELECT
                        null
                        FROM 
                          functions f
                            inner join items i on f.itemid = i.itemid
                            inner join hosts_groups hgg on i.hostid = hgg.hostid
                            LEFT JOIN rights r ON r.id = hgg.groupid AND r.groupid IN (18, 28, 44, 62, 76, 120, 148)
                       WHERE
                         p.objectid = f.triggerid
                         and nvl(r.permission,0) = 0
                 )
                 and exists (
                    SELECT
                        null
                        FROM 
                          functions f
                            inner join items i on f.itemid = i.itemid
                            inner join hosts_groups hgg on i.hostid = hgg.hostid
                            LEFT JOIN rights r ON r.id = hgg.groupid AND r.groupid IN (18, 28, 44, 62, 76, 120, 148)
                       WHERE
                         p.objectid = f.triggerid
                         and nvl(r.permission,0) in (2,3)
                 )
      

      This change decreased time to 5 seconds.

      We use Oracle as zabbix db.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              zabbix.dev Zabbix Development Team
              Reporter:
              zalex_ua Oleksii Zagorskyi
              Votes:
              5 Vote for this issue
              Watchers:
              13 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: