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

Slow query related to 'Problem' table

    XMLWordPrintable

Details

    • Incident report
    • Status: Need info
    • Trivial
    • Resolution: Unresolved
    • 3.4.3
    • None
    • API (A), Frontend (F)
    • Team A
    • Sprint 25, Sprint 26

    Description

      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

      Attachments

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

        Issue Links

          Activity

            People

              Unassigned Unassigned
              landry41 CHRETIEN Landry
              Votes:
              3 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

                Created:
                Updated: