[ZBX-12133] API event.get performance regression in 3.2 Created: 2017 May 02  Updated: 2024 Jan 23  Resolved: 2024 Jan 23

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: API (A)
Affects Version/s: None
Fix Version/s: None

Type: Problem report Priority: Trivial
Reporter: Dmitrijs Lamberts Assignee: Zabbix Development Team
Resolution: Duplicate Votes: 4
Labels: API
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

MySQL

Issue Links:
Duplicate
is duplicated by ZBX-12954 Slow query related to 'Problem' table Closed
Sprint: Sprint 21, Sprint 22, Sprint 23

 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)


 Comments   
Comment by Dmitrijs Lamberts [ 2017 May 02 ] 
# 3.0 queries executed on Zabbix 3.2 active environment

mysql> SELECT  DISTINCT  e.eventid,e.objectid,e.value,e.clock FROM events e,functions f,items i WHERE 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 i.hostid='200200000022813' AND f.triggerid=e.objectid AND f.itemid=i.itemid AND e.object='0' AND e.source='0' ORDER BY e.clock DESC LIMIT 10 OFFSET 0;
+-----------------+-----------------+-------+------------+
| eventid         | objectid        | value | clock      |
+-----------------+-----------------+-------+------------+
| 100000269062201 | 200200000659034 |     0 | 1491358292 |
| 100000269010543 | 200200000659034 |     1 | 1491354692 |
| 100000195634555 | 200200000994132 |     0 | 1485862892 |
| 100000195634533 | 200200000994133 |     0 | 1485862892 |
| 100000195634557 | 200200000994134 |     0 | 1485862892 |
| 100000195634535 | 200200000994135 |     0 | 1485862892 |
| 100000195619392 | 200200000994140 |     0 | 1485861692 |
| 100000195619393 | 200200000994141 |     0 | 1485861692 |
| 100000195619406 | 200200000994136 |     0 | 1485861692 |
| 100000195619408 | 200200000994137 |     0 | 1485861692 |
+-----------------+-----------------+-------+------------+
10 rows in set (0.06 sec)

# 3.2 queries executed on Zabbix 3.2 active environment

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 (13.36 sec)

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.itemidAND 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 (24.10 sec)
Comment by Alexander Vladishev [ 2017 Jun 01 ]

Fixed in:

  • pre-3.2.7 r68759
  • pre-3.4.0 r68760
Comment by Stefan Priebe [ 2017 Nov 05 ]

I'm seeing the same now after upgrading from 3.2.8 to 3.4.3.

We have some screens which show the open host group issues up to 100. For each problem it executed something like "select eventid FROM events WHERE objectid='402957'" and this query takes 0,5s so whole page loads in 30-50s.

EXPLAIN select eventid FROM events WHERE objectid='402957';
-------------------------------------------------------------------------------------------+

id select_type table type possible_keys key key_len ref rows Extra

-------------------------------------------------------------------------------------------+

1 SIMPLE events index NULL events_1 20 NULL 3385303 Using where; Using index

-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Comment by Rostislav Palivoda (Inactive) [ 2017 Nov 06 ]

Then reopen

Comment by Stefan Priebe [ 2017 Nov 06 ]

I'm sorry can't find a button to reopen.

Comment by Stefan Priebe [ 2017 Nov 07 ]

PHP Profiling shows in my case:
153 calls of CEvent::get => 153 calls of CEvent::addRelatedObjects
which takes 28s while 28s is DBSelect with 312 calls

on the mysql side i see select eventid FROM events WHERE objectid='X'; which took 0.5s per query for 3,5 million rows

Comment by psychomoise [ 2018 Jul 05 ]

you should check what I have found on ZBX-13789 to see if it helps

Comment by Stefan Priebe [ 2018 Jul 11 ]

Thanks @psychomoise but how could those indexes help if my problem is the events table? Your indexes are not events related.

Comment by Alexander Vladishev [ 2024 Jan 23 ]

Fixed as part of ZBXNEXT-5878.

Generated at Sun Aug 03 03:41:06 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.