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

API event.get performance regression in 3.2

    XMLWordPrintable

    Details

    • Type: Problem report
    • Status: Reopened
    • Priority: Trivial
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: API (A)
    • Labels:
    • Environment:
      MySQL
    • Sprint:
      Sprint 21, Sprint 22, Sprint 23, Support Team Backlog
    • Story Points:
      1

      Description

      Attached results are gathered from 3 different environments.

      • MySQL database Zabbix 3.0
      • MySQL database after upgrading to Zabbix 3.2 but with millions of event records
      • MySQL database after upgrading to Zabbix 3.2 but with only 1 day of event records where source != 0
      mysql-common-5.6       5.6.33-0ubuntu0.14.04.1  all
      mysql-server-5.6       5.6.33-0ubuntu0.14.04.1  amd64
      mysql-server-core-5.6  5.6.33-0ubuntu0.14.04.1  amd64
      
      # mysql -V
      mysql  Ver 14.14 Distrib 5.6.33, for debian-linux-gnu (x86_64) using  EditLine wrapper
      
      # cat /etc/lsb-release 
      DISTRIB_ID=Ubuntu
      DISTRIB_RELEASE=14.04
      DISTRIB_CODENAME=trusty
      DISTRIB_DESCRIPTION="Ubuntu 14.04.5 LTS"
      
      ## 3.0 environment (passive)
      
      ### Records in event table
      
      mysql> select count(*), source from events group by source;
      +----------+--------+
      | count(*) | source |
      +----------+--------+
      |  2347394 |      0 |
      |   257714 |      2 |
      | 99983311 |      3 |
      +----------+--------+
      3 rows in set (22.72 sec)
      
      ### Normal user
      
      mysql> SELECT   e.eventid,e.objectid,e.value,e.clock FROM events e WHERE e.source='0' AND e.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 ('100100000000020','100100000000028') WHERE e.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 e.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid='200200000022813') ORDER BY e.clock DESC LIMIT 10 OFFSET 0;
      +-----------------+-----------------+-------+------------+
      | eventid         | objectid        | value | clock      |
      +-----------------+-----------------+-------+------------+
      | 100000195634557 | 200200000994134 |     0 | 1485862892 |
      | 100000195634555 | 200200000994132 |     0 | 1485862892 |
      | 100000195634535 | 200200000994135 |     0 | 1485862892 |
      | 100000195634533 | 200200000994133 |     0 | 1485862892 |
      | 100000195619408 | 200200000994137 |     0 | 1485861692 |
      | 100000195619406 | 200200000994136 |     0 | 1485861692 |
      | 100000195619393 | 200200000994141 |     0 | 1485861692 |
      | 100000195619392 | 200200000994140 |     0 | 1485861692 |
      | 100000195611898 | 200200000994139 |     0 | 1485861092 |
      | 100000195611896 | 200200000994138 |     0 | 1485861092 |
      +-----------------+-----------------+-------+------------+
      10 rows in set (13.16 sec)
      
      ### Super admin
      
      mysql> SELECT   e.eventid,e.objectid,e.value,e.clock FROM events e WHERE e.source='0' AND e.object='0' AND EXISTS (SELECT NULL FROM functions f,items i WHERE e.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid='200200000022813') ORDER BY e.clock DESC LIMIT 10 OFFSET 0;
          +-----------------+-----------------+-------+------------+
          | eventid         | objectid        | value | clock      |
          +-----------------+-----------------+-------+------------+
          | 100000195634557 | 200200000994134 |     0 | 1485862892 |
          | 100000195634555 | 200200000994132 |     0 | 1485862892 |
          | 100000195634535 | 200200000994135 |     0 | 1485862892 |
          | 100000195634533 | 200200000994133 |     0 | 1485862892 |
          | 100000195619408 | 200200000994137 |     0 | 1485861692 |
          | 100000195619406 | 200200000994136 |     0 | 1485861692 |
          | 100000195619393 | 200200000994141 |     0 | 1485861692 |
          | 100000195619392 | 200200000994140 |     0 | 1485861692 |
          | 100000195611898 | 200200000994139 |     0 | 1485861092 |
          | 100000195611896 | 200200000994138 |     0 | 1485861092 |
          +-----------------+-----------------+-------+------------+
          10 rows in set (3.49 sec)
      
      ## 3.2 environment (passive, with millions of events)
      
      ### Records in event table
      
      mysql> select count(*), source from events group by source;
      +----------+--------+
      | count(*) | source |
      +----------+--------+
      |  2469825 |      0 |
      |    30116 |      2 |
      | 39857204 |      3 |
      +----------+--------+
      3 rows in set (1 min 5.47 sec)
      
      ### Normal user
      
      mysql> SELECT   e.eventid,e.objectid,e.value,e.clock FROM events e WHERE e.source='0' AND e.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 ('100100000000020','100100000000028') WHERE e.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 e.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid='200200000022813') ORDER BY e.clock DESC LIMIT 10 OFFSET 0;
      +-----------------+-----------------+-------+------------+
      | eventid         | objectid        | value | clock      |
      +-----------------+-----------------+-------+------------+
      | 100000269062201 | 200200000659034 |     0 | 1491358292 |
      | 100000269010543 | 200200000659034 |     1 | 1491354692 |
      | 100000195634557 | 200200000994134 |     0 | 1485862892 |
      | 100000195634555 | 200200000994132 |     0 | 1485862892 |
      | 100000195634535 | 200200000994135 |     0 | 1485862892 |
      | 100000195634533 | 200200000994133 |     0 | 1485862892 |
      | 100000195619408 | 200200000994137 |     0 | 1485861692 |
      | 100000195619406 | 200200000994136 |     0 | 1485861692 |
      | 100000195619393 | 200200000994141 |     0 | 1485861692 |
      | 100000195619392 | 200200000994140 |     0 | 1485861692 |
      +-----------------+-----------------+-------+------------+
      10 rows in set (25.06 sec)
      
      ### Super admin
      
      mysql> SELECT   e.eventid,e.objectid,e.value,e.clock FROM events e WHERE e.source='0' AND e.object='0' AND EXISTS (SELECT NULL FROM functions f,items i WHERE e.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid='200200000022813') ORDER BY e.clock DESC LIMIT 10 OFFSET 0;
      +-----------------+-----------------+-------+------------+
      | eventid         | objectid        | value | clock      |
      +-----------------+-----------------+-------+------------+
      | 100000269062201 | 200200000659034 |     0 | 1491358292 |
      | 100000269010543 | 200200000659034 |     1 | 1491354692 |
      | 100000195634557 | 200200000994134 |     0 | 1485862892 |
      | 100000195634555 | 200200000994132 |     0 | 1485862892 |
      | 100000195634535 | 200200000994135 |     0 | 1485862892 |
      | 100000195634533 | 200200000994133 |     0 | 1485862892 |
      | 100000195619408 | 200200000994137 |     0 | 1485861692 |
      | 100000195619406 | 200200000994136 |     0 | 1485861692 |
      | 100000195619393 | 200200000994141 |     0 | 1485861692 |
      | 100000195619392 | 200200000994140 |     0 | 1485861692 |
      +-----------------+-----------------+-------+------------+
      10 rows in set (5.13 sec)
      
      ## 3.2 (active, with events source != 0 for only one day)
      
      ### Records in event table
      
      mysql> select count(*), source from events group by source;
      +----------+--------+
      | count(*) | source |
      +----------+--------+
      |  2474692 |      0 |
      |     4041 |      2 |
      |  1388979 |      3 |
      +----------+--------+
      3 rows in set (0.90 sec)
      
      ### Normal user
      
      mysql> SELECT   e.eventid,e.objectid,e.value,e.clock FROM events e WHERE e.source='0' AND e.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 ('100100000000020','100100000000028') WHERE e.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hgg.hostid GROUP BY f.triggeridHAVING MIN(r.permission)>0 AND MAX(r.permission)>='2') AND EXISTS (SELECT NULL FROM functions f,items i WHERE e.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid='200200000022813') ORDER BY e.clock DESC LIMIT 10 OFFSET 0;
      +-----------------+-----------------+-------+------------+
      | eventid         | objectid        | value | clock      |
      +-----------------+-----------------+-------+------------+
      | 100000269062201 | 200200000659034 |     0 | 1491358292 |
      | 100000269010543 | 200200000659034 |     1 | 1491354692 |
      | 100000195634557 | 200200000994134 |     0 | 1485862892 |
      | 100000195634555 | 200200000994132 |     0 | 1485862892 |
      | 100000195634535 | 200200000994135 |     0 | 1485862892 |
      | 100000195634533 | 200200000994133 |     0 | 1485862892 |
      | 100000195619408 | 200200000994137 |     0 | 1485861692 |
      | 100000195619406 | 200200000994136 |     0 | 1485861692 |
      | 100000195619393 | 200200000994141 |     0 | 1485861692 |
      | 100000195619392 | 200200000994140 |     0 | 1485861692 |
      +-----------------+-----------------+-------+------------+
      10 rows in set (30.81 sec)
      
      ### Super admin
      
      mysql> SELECT   e.eventid,e.objectid,e.value,e.clock FROM events e WHERE e.source='0' AND e.object='0' AND EXISTS (SELECT NULL FROM functions f,items i WHERE e.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid='200200000022813') ORDER BY e.clock DESC LIMIT 10 OFFSET 0;
      +-----------------+-----------------+-------+------------+
      | eventid         | objectid        | value | clock      |
      +-----------------+-----------------+-------+------------+
      | 100000269062201 | 200200000659034 |     0 | 1491358292 |
      | 100000269010543 | 200200000659034 |     1 | 1491354692 |
      | 100000195634557 | 200200000994134 |     0 | 1485862892 |
      | 100000195634555 | 200200000994132 |     0 | 1485862892 |
      | 100000195634535 | 200200000994135 |     0 | 1485862892 |
      | 100000195634533 | 200200000994133 |     0 | 1485862892 |
      | 100000195619408 | 200200000994137 |     0 | 1485861692 |
      | 100000195619406 | 200200000994136 |     0 | 1485861692 |
      | 100000195619393 | 200200000994141 |     0 | 1485861692 |
      | 100000195619392 | 200200000994140 |     0 | 1485861692 |
      +-----------------+-----------------+-------+------------+
      10 rows in set (5.85 sec)
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              sasha Alexander Vladishev
              Reporter:
              dmitrijs.lamberts Dmitrijs Lamberts
              Votes:
              4 Vote for this issue
              Watchers:
              10 Start watching this issue

                Dates

                Created:
                Updated: