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

Too slow dashboard when big events table

    XMLWordPrintable

    Details

    • Team:
      Team D
    • Sprint:
      Sprint 44, Sprint 45, Sprint 46, Nov 2018, Sprint 47, Dec 2018, Sprint 48, Jan 2019, Sprint 56 (Sep 2019), Sprint 55 (Aug 2019), Sprint 49 (Feb 2019), Sprint 50 (Mar 2019), Sprint 51 (Apr 2019), Sprint 52 (May 2019), Sprint 53 (Jun 2019), Sprint 54 (Jul 2019), Sprint 57 (Oct 2019), Sprint 58 (Nov 2019), Sprint 59 (Dec 2019), Sprint 60 (Jan 2020), Sprint 61 (Feb 2020), Sprint 62 (Mar 2020), Sprint 63 (Apr 2020), Sprint 64 (May 2020), Sprint 65 (Jun 2020), Sprint 66 (Jul 2020), Sprint 67 (Aug 2020), Sprint 68 (Sep 2020), Sprint 69 (Oct 2020), Sprint 70 (Nov 2020), Sprint 71 (Dec 2020), Sprint 72 (Jan 2021), Sprint 73 (Feb 2021), Sprint 74 (Mar 2021)
    • Story Points:
      3

      Description

      Dashboard too slow after upgrade from 3.2.4 to 3.4.1. Mysql process utilized all CPUs.

      In mysql slow log:

      SELECT DISTINCT e.eventid,e.clock,e.ns,e.objectid,er1.r_eventid FROM events e LEFT JOIN event_recovery er1 ON er1.eventid=e.eventid WHERE e.source='0' AND e.object='0'
      AND e.objectid='31590' AND e.eventid<='12599826' AND e.value='1' ORDER BY e.eventid DESC LIMIT 20;
      # [email protected]: zabbix[zabbix] @ localhost []  Id:   489
      # Schema: zabbix2  Last_errno: 0  Killed: 0
      # Query_time: 193.328440  Lock_time: 0.000280  Rows_sent: 20  Rows_examined: 7980737  Rows_affected: 0
      # Bytes_sent: 1274
      

      Execution plan show full table scan for getting only 20 rows from big events table.

      Currently we are fix this behaviour by adding lower limit for eventid in next files.
      include/classes/api/services/CEvent.php

      diff --git a/CEvent.php b/CEvent.php
      index b1c2053..d4ec83a 100644
      --- a/CEvent.php
      +++ b/CEvent.php
      @@ -339,6 +339,8 @@ class CEvent extends CApiService {
      
                      // eventid_till
                      if ($options['eventid_till'] !== null) {
      +                       $eventid_before =(int) $options['eventid_till'] - 10000;
      +                       $sqlParts['where'][] = 'e.eventid>='.zbx_dbstr($eventid_before);
                              $sqlParts['where'][] = 'e.eventid<='.zbx_dbstr($options['eventid_till']);
                      }
      

      include/classes/api/services/CProblem.php

      diff --git a/CProblem.php b/CProblem.php
      index 81bcd8a..6c2cdb8 100644
      --- a/CProblem.php
      +++ b/CProblem.php
      @@ -314,6 +314,8 @@ class CProblem extends CApiService {
      
                      // eventid_till
                      if ($options['eventid_till'] !== null) {
      +                       $eventid_before =(int) $options['eventid_till'] - 10000;
      +                       $sqlParts['where'][] = 'p.eventid>='.zbx_dbstr($eventid_before);
                              $sqlParts['where'][] = 'p.eventid<='.zbx_dbstr($options['eventid_till']);
                      }
      

        Attachments

        1. image-2021-03-19-22-20-46-371.png
          image-2021-03-19-22-20-46-371.png
          112 kB
        2. loader.png
          loader.png
          83 kB
        3. Selection_434.png
          Selection_434.png
          95 kB
        4. tags-after-fix.png
          tags-after-fix.png
          102 kB
        5. tags-before-fix.png
          tags-before-fix.png
          105 kB

          Issue Links

            Activity

              People

              Assignee:
              agriscenko Andrejs Griščenko
              Reporter:
              cwn Denis Ryazanov
              Votes:
              13 Vote for this issue
              Watchers:
              21 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: