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

Slow query related to 'Problem' table

    XMLWordPrintable

    Details

    • Type: Incident report
    • Status: Need info
    • Priority: Trivial
    • Resolution: Unresolved
    • Affects Version/s: 3.4.3
    • Fix Version/s: None
    • Component/s: API (A), Frontend (F)
    • Labels:
    • Team:
      Team A
    • Sprint:
      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

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

                Dates

                Created:
                Updated: