Loading...

XML

Word

Printable

Type: Problem report
Resolution: Duplicate
Priority: Trivial
Fix Version/s: None
Affects Version/s: None
Component/s: API (A)
Labels:
- API
Environment:
MySQL

Sprint:
Sprint 21, Sprint 22, Sprint 23

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)

is duplicated by

ZBX-12954 Slow query related to 'Problem' table

Closed

Assignee:: Zabbix Development Team

Reporter:: Dmitrijs Lamberts

Votes:: 4 Vote for this issue

Watchers:: 11 Start watching this issue

Created:: 2017 May 02 21:31

Updated:: 2024 Jan 23 13:38

Resolved:: 2024 Jan 23 13:38

Details

Description

Attachments

Issue Links

Activity

People

Dates