[ZBX-8895] Slow query while using triggers info widget on a screen Created: 2014 Oct 14  Updated: 2017 May 30  Resolved: 2014 Oct 21

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Frontend (F)
Affects Version/s: 2.2.6
Fix Version/s: 2.2.10rc1, 2.4.5rc1, 2.5.0

Type: Incident report Priority: Major
Reporter: Alexey Pustovalov Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: screen, trigger
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

MySQL

Attachments: File ZBX-8895.patch    
Issue Links:
Duplicate

 Description   

Zabbix uses the next query for getting overview by trigger priority and status:

mysql> explain SELECT t.priority,t.value,count(DISTINCT t.triggerid) AS cnt FROM triggers t WHERE t.triggerid BETWEEN '13040' AND '13045' OR t.triggerid BETWEEN '26416' AND '26421' OR t.triggerid BETWEEN '26659' AND '26664' OR t.triggerid BETWEEN '26692' AND '26697' GROUP BY t.priority,t.value;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | t     | range | PRIMARY       | PRIMARY | 8       | NULL |    4 | Using where; Using filesort |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------------+
1 row in set (0.01 sec)

The query is very slow when an installation has more than 1 million triggers.

 Comments   
Comment by Marc [ 2014 Oct 14 ]

Reminds me a bit of ZBX-6557

Comment by Krists Krigers (Inactive) [ 2014 Oct 21 ]

Could You add an index (triggerid, priority, value) and then check how much time the query runs?
Also please try these and report how much time it takes:
1) 

SELECT t.priority, t.value, COUNT(t.triggerid) AS cnt FROM triggers t WHERE t.triggerid BETWEEN '13040' AND '13045' OR t.triggerid BETWEEN '26416' AND '26421' OR t.triggerid BETWEEN '26659' AND '26664' OR t.triggerid BETWEEN '26692' AND '26697' GROUP BY t.priority, t.value;

2) 

SELECT t.priority, t.value, COUNT(t.triggerid) AS cnt FROM triggers t WHERE t.triggerid BETWEEN '13040' AND '13045' OR t.triggerid BETWEEN '26416' AND '26421' OR t.triggerid BETWEEN '26659' AND '26664' OR t.triggerid BETWEEN '26692' AND '26697' AND t.value = 1 GROUP BY t.priority;

3) SELECT t.priority, t.value, COUNT(t.triggerid) AS cnt FROM triggers t WHERE t.triggerid BETWEEN '13040' AND '13045' OR t.triggerid BETWEEN '26416' AND '26421' OR t.triggerid BETWEEN '26659' AND '26664' OR t.triggerid BETWEEN '26692' AND '26697' AND t.value = 2 GROUP BY t.priority;

dotneft

| triggers | CREATE TABLE `triggers` (
  `triggerid` bigint(20) unsigned NOT NULL,
  `expression` varchar(255) NOT NULL DEFAULT '',
  `description` varchar(255) NOT NULL DEFAULT '',
  `url` varchar(255) NOT NULL DEFAULT '',
  `status` int(11) NOT NULL DEFAULT '0',
  `value` int(11) NOT NULL DEFAULT '0',
  `priority` int(11) NOT NULL DEFAULT '0',
  `lastchange` int(11) NOT NULL DEFAULT '0',
  `comments` text NOT NULL,
  `error` varchar(128) NOT NULL DEFAULT '',
  `templateid` bigint(20) unsigned DEFAULT NULL,
  `type` int(11) NOT NULL DEFAULT '0',
  `value_flags` int(11) NOT NULL DEFAULT '0',
  `flags` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`triggerid`),
  KEY `triggers_1` (`status`),
  KEY `c_triggers_1` (`templateid`),
  KEY `triggers_2` (`value`,`lastchange`),
  KEY `triggers_3` (`lastchange`),
  KEY `triggers_10` (`triggerid`,`priority`,`value`),
  CONSTRAINT `c_triggers_1` FOREIGN KEY (`templateid`) REFERENCES `triggers` (`triggerid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+---------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

1.

mysql> explain SELECT t.priority, t.value, COUNT(t.triggerid) AS cnt FROM triggers t WHERE t.triggerid BETWEEN '13040' AND '13045' OR t.triggerid BETWEEN '26416' AND '26421' OR t.triggerid BETWEEN '26659' AND '26664' OR t.triggerid BETWEEN '26692' AND '26697' GROUP BY t.priority, t.value;
+----+-------------+-------+-------+---------------------+-------------+---------+------+------+-----------------------------------------------------------+
| id | select_type | table | type  | possible_keys       | key         | key_len | ref  | rows | Extra                                                     |
+----+-------------+-------+-------+---------------------+-------------+---------+------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | t     | range | PRIMARY,triggers_10 | triggers_10 | 8       | NULL |    4 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+-------+-------+---------------------+-------------+---------+------+------+-----------------------------------------------------------+
1 row in set (0.01 sec)

result:
+----------+-------+------+
5 rows in set (0.01 sec)

2.

mysql> explain SELECT t.priority, t.value, COUNT(t.triggerid) AS cnt FROM triggers t WHERE t.triggerid BETWEEN '13040' AND '13045' OR t.triggerid BETWEEN '26416' AND '26421' OR t.triggerid BETWEEN '26659' AND '26664' OR t.triggerid BETWEEN '26692' AND '26697' AND t.value = 1 GROUP BY t.priority;
+----+-------------+-------+-------+--------------------------------+-------------+---------+------+------+-----------------------------------------------------------+
| id | select_type | table | type  | possible_keys                  | key         | key_len | ref  | rows | Extra                                                     |
+----+-------------+-------+-------+--------------------------------+-------------+---------+------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | t     | range | PRIMARY,triggers_2,triggers_10 | triggers_10 | 8       | NULL |    4 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+-------+-------+--------------------------------+-------------+---------+------+------+-----------------------------------------------------------+
1 row in set (0.00 sec)

result:
+----------+-------+------+
5 rows in set (0.01 sec)

3.

mysql> explain  SELECT t.priority, t.value, COUNT(t.triggerid) AS cnt FROM triggers t WHERE t.triggerid BETWEEN '13040' AND '13045' OR t.triggerid BETWEEN '26416' AND '26421' OR t.triggerid BETWEEN '26659' AND '26664' OR t.triggerid BETWEEN '26692' AND '26697' AND t.value = 2 GROUP BY t.priority;
+----+-------------+-------+-------+--------------------------------+-------------+---------+------+------+-----------------------------------------------------------+
| id | select_type | table | type  | possible_keys                  | key         | key_len | ref  | rows | Extra                                                     |
+----+-------------+-------+-------+--------------------------------+-------------+---------+------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | t     | range | PRIMARY,triggers_2,triggers_10 | triggers_10 | 8       | NULL |    4 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+-------+-------+--------------------------------+-------------+---------+------+------+-----------------------------------------------------------+
1 row in set (0.00 sec)

result:
+----------+-------+-------+
5 rows in set (0.20 sec)
Comment by Krists Krigers (Inactive) [ 2014 Oct 21 ]

Fixed in r50068, branch svn://svn.zabbix.com/branches/dev/ZBX-8895 (copy of branches/2.2).

Comment by Krists Krigers (Inactive) [ 2014 Oct 21 ]

Test patch

Comment by Alexander Vladishev [ 2015 Jul 01 ]

Successfully tested!

Comment by Oleg Egorov (Inactive) [ 2015 Jul 02 ]

FIXED IN 2.2.10rc1 r54248, 2.4.6rc1 r54249, 2.5.0(trunk) r54264

Comment by Oleg Egorov (Inactive) [ 2015 Jul 02 ]

CLOSED

Generated at Sat Apr 27 02:04:01 EEST 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.