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

Slow problems processing because of slow query with permissions check

    XMLWordPrintable

    Details

    • Type: Problem report
    • Status: Closed
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: 3.2.5
    • Fix Version/s: 3.2.7rc1, 3.4.0alpha1
    • Component/s: Frontend (F)
    • Environment:
      1119188 problems, 1427127 items, 0 - r_clock > 1495715431, 1087336 - r_eventid IS NULL
    • Team:
      Team B
    • Sprint:
      Sprint 8, Sprint 9
    • Story Points:
      1

      Description

      original query:

      mysql> SELECT p.eventid, 
          ->        p.objectid, 
          ->        p.clock, 
          ->        p.ns 
          -> FROM   problem p 
          -> WHERE  p.source = '0' 
          ->        AND p.object = '0' 
          ->        AND EXISTS (SELECT NULL 
          ->                    FROM   functions f, 
          ->                           items i, 
          ->                           hosts_groups hgg 
          ->                           JOIN rights r 
          ->                             ON r.id = hgg.groupid 
          ->                                AND r.groupid IN ( '20', '26', '47' ) 
          ->                    WHERE  p.objectid = f.triggerid 
          ->                           AND f.itemid = i.itemid 
          ->                           AND i.hostid = hgg.hostid 
          ->                    GROUP  BY f.triggerid 
          ->                    HAVING Min(r.permission) > 0 
          ->                           AND Max(r.permission) >= 2) 
          ->        AND EXISTS (SELECT NULL 
          ->                    FROM   functions f, 
          ->                           items i 
          ->                    WHERE  p.objectid = f.triggerid 
          ->                           AND f.itemid = i.itemid 
          ->                           AND i.hostid = '71962') 
          ->        AND ( p.r_eventid IS NULL 
          ->               OR p.r_clock > 1495715431 ) 
          -> ORDER  BY p.eventid DESC 
          -> LIMIT  15001 offset 0;
      Empty set (48.30 sec)
      

      modified SQL queries:

      mysql> SELECT p.eventid, 
          ->        p.objectid, 
          ->        p.clock, 
          ->        p.ns 
          -> FROM   problem p 
          -> WHERE  p.source = '0' 
          ->        AND p.object = '0' 
          ->        AND EXISTS (SELECT NULL 
          ->                    FROM   functions f, 
          ->                           items i, 
          ->                           hosts_groups hgg 
          ->                           JOIN rights r 
          ->                             ON r.id = hgg.groupid 
          ->                                AND r.groupid IN ( '20', '26', '47' ) 
          ->                    WHERE  p.objectid = f.triggerid 
          ->                           AND f.itemid = i.itemid 
          ->                           AND i.hostid = 71962
          ->                           AND i.hostid = hgg.hostid 
          ->                    GROUP  BY f.triggerid 
          ->                    HAVING Min(r.permission) > 0 
          ->                           AND Max(r.permission) >= 2) 
          ->        AND ( p.r_eventid IS NULL 
          ->               OR p.r_clock > 1495715431 ) 
          -> ORDER  BY p.eventid DESC 
          -> LIMIT  15001 offset 0;
      Empty set (8.20 sec)
      
      mysql> SELECT p.eventid, 
          ->        p.objectid, 
          ->        p.clock, 
          ->        p.ns 
          -> FROM   problem p 
          -> WHERE  p.source = '0' 
          ->        AND p.object = '0' 
          ->        AND EXISTS (SELECT NULL 
          ->                    FROM   functions f, 
          ->                           items i, 
          ->                           hosts_groups hgg 
          ->                           JOIN rights r 
          ->                             ON r.id = hgg.groupid 
          ->                                AND r.groupid IN ( '20', '26', '47' ) 
          ->                    WHERE  p.objectid = f.triggerid 
          ->                           AND f.itemid = i.itemid 
          ->                           AND i.hostid = 71962
          ->                           AND i.hostid = hgg.hostid 
          ->                    GROUP  BY f.triggerid 
          ->                    HAVING Min(r.permission) > 0 
          ->                           AND Max(r.permission) >= 2) 
          ->        AND ( p.r_eventid IS NULL 
          ->               OR p.r_clock > 1495715431 ) 
          -> ORDER  BY p.eventid DESC 
          -> LIMIT  15001 offset 0;
      Empty set (8.16 sec)
      
      mysql> SELECT p.eventid, 
          ->        p.objectid, 
          ->        p.clock, 
          ->        p.ns 
          -> FROM   problem p 
          -> WHERE  p.source = '0' 
          ->        AND p.object = '0' 
          ->        AND EXISTS (SELECT NULL 
          ->                    FROM   functions f, 
          ->                           items i, 
          ->                           hosts_groups hgg 
          ->                           JOIN rights r 
          ->                             ON r.id = hgg.groupid 
          ->                                AND r.groupid IN ( '20', '26', '47' ) 
          ->                    WHERE  p.objectid = f.triggerid 
          ->                           AND f.itemid = i.itemid 
          ->                           AND i.hostid = 71962
          ->                           AND i.hostid = hgg.hostid 
          ->                    GROUP  BY f.triggerid 
          ->                    HAVING Min(r.permission) > 0 
          ->                           AND Max(r.permission) >= 2) 
          ->        AND ( p.r_eventid IS NULL 
          ->               OR p.r_clock > 1495715431 ) 
          -> LIMIT  15001 offset 0;
      Empty set (7.57 sec)
      

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            dotneft Alexey Pustovalov
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: