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 A
    • Sprint:
      Sprint 57 (Oct 2019), Sprint 58 (Nov 2019), Sprint 59 (Dec 2019), Sprint 60 (Jan 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:
              3 Vote for this issue
              Watchers:
              12 Start watching this issue

                Dates

                Created:
                Updated: