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

Slow query related to 'Problem' table

XMLWordPrintable

    • Icon: Incident report Incident report
    • Resolution: Fixed
    • Icon: Trivial Trivial
    • None
    • 3.4.3
    • API (A), Frontend (F)
    • Sprint 25, Sprint 26

      Hello,

      We just migrate from 2.4.8 to 3.4.3 and we switch from Virtual server to physical server with SAN storage for Mysql DB.

      After some users connexions (Average of 17 users connected) we had slow query in related to view and widget 'Problem'

      This is a kind of request taking around 30 - 40s

      SELECT p.eventid,p.objectid,p.clock,p.ns 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 ('58','61','70','71') WHERE p.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MA:@permission)<2 OR MIN(permission) IS NULL OR MIN(permission)=0) AND (p.r_eventid IS NULL OR p.r_clock>1509091089) ORDER BY p.eventid DESC LIMIT 1001
      
      SELECT p.eventid,p.objectid,p.clock,p.ns 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 ('23','50','67') WHERE p.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MA:@permission)<2 OR MIN(permission) IS NULL OR MIN(permission)=0) AND (p.r_eventid IS NULL OR p.r_clock>1509091078) AND p.eventid<='9851748' ORDER BY p.eventid DESC LIMIT 1001
      

      Our Zabbix instance (32G Ram, 16Core 'E2609', 1To of SAN Drive with FC) iowait fall down to nearly 0

      This zabbix instance host around :

      • 900 hosts
      • 100K Items
      • 50K Triggers

        1. Capture-1.PNG
          Capture-1.PNG
          52 kB
        2. Capture.PNG
          Capture.PNG
          51 kB
        3. server_crash.txt
          42 kB

            zabbix.dev Zabbix Development Team
            landry41 CHRETIEN Landry
            Team A
            Votes:
            3 Vote for this issue
            Watchers:
            11 Start watching this issue

              Created:
              Updated:
              Resolved: