-
Problem report
-
Resolution: Cannot Reproduce
-
Critical
-
2.2.15, 3.0.6
-
Sprint 4, Sprint 5, Sprint 6, Sprint 7, Sprint 8, Sprint 9, Sprint 10, Sprint 11, Sprint 12, Sprint 13, Sprint 14, Sprint 15, Sprint 16, Sprint 17, Sprint 46, Nov 2018
-
1
Middle/big zabbix installation has been upgraded from 2.2.9 to 3.0.6 and one performance issue appeared.
When trying to open pages on Monitoring menu (at least for Triggers, Graphs) - page appears with a big delay - 90+ seconds.
When page is loading, such SQL is executed, its explain:
mysql> explain SELECT g.groupid,g.name FROM groups g WHERE EXISTS (SELECT NULL FROM hosts h,items i,graphs_items gi,graphs gr use index (PRIMARY),hosts_groups hg WHERE hg.hostid=h.hostid AND h.status IN ('0','1') AND hg.hostid=i.hostid AND i.itemid=gi.itemid AND gi.graphid=gr.graphid AND gr.flags IN ('0','4') AND g.groupid=hg.groupid) ORDER BY g.name;
+----+--------------------+-------+--------+-------------------------------+----------------+---------+----------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+-------------------------------+----------------+---------+----------------+------+--------------------------+
| 1 | PRIMARY | g | index | NULL | groups_1 | 194 | NULL | 7 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | hg | ref | hosts_groups_1,hosts_groups_2 | hosts_groups_2 | 8 | 3.0.g.groupid | 5 | |
| 2 | DEPENDENT SUBQUERY | h | eq_ref | PRIMARY,hosts_2 | PRIMARY | 8 | 3.0.hg.hostid | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | i | ref | PRIMARY,items_1 | items_1 | 8 | 3.0.hg.hostid | 9 | Using index |
| 2 | DEPENDENT SUBQUERY | gi | ref | graphs_items_1,graphs_items_2 | graphs_items_1 | 8 | 3.0.i.itemid | 1 | |
| 2 | DEPENDENT SUBQUERY | gr | eq_ref | PRIMARY | PRIMARY | 8 | 3.0.gi.graphid | 1 | Using where |
+----+--------------------+-------+--------+-------------------------------+----------------+---------+----------------+------+--------------------------+
6 rows in set (0.00 sec)
it returns 304 groups.
This SQL is for Graphs page. For triggers page its similar and also takes much time.
There was a change ZBX-9162 (commited to 2.2.12, 3.0.2, 3.2.0), which changed considered SQL.
Trying to understand what could be a reason of the regression I tried revision 59333 (before the fix) of svn://svn.zabbix.com/branches/3.0/frontends/php/include/classes/api/services/CHostGroup.php -r 59333 and it immediately resolved the performance regression.
(yes, there were other further changes after r59333, but for a quick test just reverting file revision is ok).
The older SQL takes less than a second.
mysqld --version mysqld Ver 5.5.38-MariaDB-wsrep-log for Linux on x86_64 (MariaDB Server, wsrep_25.10.r3997)
Indexes of involved tables were checked - they are the same as in official schema.
More debug info from the PROD installation and MY TEST Debian workstation (not sure why explains are different sometimes) :
PROD:
MariaDB [zabbix]> explain SELECT g.groupid,g.name FROM groups g WHERE EXISTS (SELECT NULL FROM hosts h,items i,graphs_items gi,graphs gr use index (PRIMARY),hosts_groups hg WHERE hg.hostid=h.hostid AND h.status IN ('0','1') AND hg.hostid=i.hostid AND i.itemid=gi.itemid AND gi.graphid=gr.graphid AND gr.flags IN ('0','4') AND g.groupid=hg.groupid) ORDER BY g.name;
+------+--------------------+-------+--------+-------------------------------+----------------+---------+----------------------------------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+-------+--------+-------------------------------+----------------+---------+----------------------------------+-------+--------------------------+
| 1 | PRIMARY | g | index | NULL | groups_1 | 66 | NULL | 366 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | gr | ALL | PRIMARY | NULL | NULL | NULL | 48442 | Using where |
| 2 | DEPENDENT SUBQUERY | gi | ref | graphs_items_1,graphs_items_2 | graphs_items_2 | 8 | zabbix.gr.graphid | 1 | |
| 2 | DEPENDENT SUBQUERY | i | eq_ref | PRIMARY,items_1 | PRIMARY | 8 | zabbix.gi.itemid | 1 | |
| 2 | DEPENDENT SUBQUERY | hg | eq_ref | hosts_groups_1,hosts_groups_2 | hosts_groups_1 | 16 | zabbix.i.hostid,zabbix.g.groupid | 1 | Using index |
| 2 | DEPENDENT SUBQUERY | h | eq_ref | PRIMARY,hosts_2 | PRIMARY | 8 | zabbix.i.hostid | 1 | Using where |
+------+--------------------+-------+--------+-------------------------------+----------------+---------+----------------------------------+-------+--------------------------+
6 rows in set (0.00 sec)
MY TEST:
mysqld --version
mysqld Ver 5.5.53-0+deb8u1 for debian-linux-gnu on x86_64 ((Debian))
mysql> explain SELECT g.groupid,g.name FROM groups g WHERE EXISTS (SELECT NULL FROM hosts h,items i,graphs_items gi,graphs gr use index (PRIMARY),hosts_groups hg WHERE hg.hostid=h.hostid AND h.status IN ('0','1') AND hg.hostid=i.hostid AND i.itemid=gi.itemid AND gi.graphid=gr.graphid AND gr.flags IN ('0','4') AND g.groupid=hg.groupid) ORDER BY g.name;
+----+--------------------+-------+--------+-------------------------------+----------------+---------+----------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+-------------------------------+----------------+---------+----------------+------+--------------------------+
| 1 | PRIMARY | g | index | NULL | groups_1 | 194 | NULL | 7 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | hg | ref | hosts_groups_1,hosts_groups_2 | hosts_groups_2 | 8 | 3.0.g.groupid | 5 | |
| 2 | DEPENDENT SUBQUERY | h | eq_ref | PRIMARY,hosts_2 | PRIMARY | 8 | 3.0.hg.hostid | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | i | ref | PRIMARY,items_1 | items_1 | 8 | 3.0.hg.hostid | 9 | Using index |
| 2 | DEPENDENT SUBQUERY | gi | ref | graphs_items_1,graphs_items_2 | graphs_items_1 | 8 | 3.0.i.itemid | 1 | |
| 2 | DEPENDENT SUBQUERY | gr | eq_ref | PRIMARY | PRIMARY | 8 | 3.0.gi.graphid | 1 | Using where |
+----+--------------------+-------+--------+-------------------------------+----------------+---------+----------------+------+--------------------------+
6 rows in set (0.00 sec)
Just in case - subquery of the SQL (without explain, on the PROD gives ~400k NULL lines in less than one second):
PROD:
MariaDB [zabbix]> explain SELECT NULL FROM hosts h,items i,graphs_items gi,graphs gr use index (PRIMARY),hosts_groups hg WHERE hg.hostid=h.hostid AND h.status IN ('0','1') AND hg.hostid=i.hostid AND i.itemid=gi.itemid AND gi.graphid=gr.graphid AND gr.flags IN ('0','4');
+------+-------------+-------+--------+-------------------------------+----------------+---------+-------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+-------------------------------+----------------+---------+-------------------+-------+-------------+
| 1 | SIMPLE | gr | ALL | PRIMARY | NULL | NULL | NULL | 48442 | Using where |
| 1 | SIMPLE | gi | ref | graphs_items_1,graphs_items_2 | graphs_items_2 | 8 | zabbix.gr.graphid | 1 | |
| 1 | SIMPLE | i | eq_ref | PRIMARY,items_1 | PRIMARY | 8 | zabbix.gi.itemid | 1 | |
| 1 | SIMPLE | hg | ref | hosts_groups_1 | hosts_groups_1 | 8 | zabbix.i.hostid | 1 | Using index |
| 1 | SIMPLE | h | eq_ref | PRIMARY,hosts_2 | PRIMARY | 8 | zabbix.i.hostid | 1 | Using where |
+------+-------------+-------+--------+-------------------------------+----------------+---------+-------------------+-------+-------------+
5 rows in set (0.00 sec)
MY TEST:
mysql> explain SELECT NULL FROM hosts h,items i,graphs_items gi,graphs gr use index (PRIMARY),hosts_groups hg WHERE hg.hostid=h.hostid AND h.status IN ('0','1') AND hg.hostid=i.hostid AND i.itemid=gi.itemid AND gi.graphid=gr.graphid AND gr.flags IN ('0','4');
+----+-------------+-------+--------+-------------------------------+----------------+---------+----------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------------+----------------+---------+----------------+------+--------------------------+
| 1 | SIMPLE | h | range | PRIMARY,hosts_2 | hosts_2 | 4 | NULL | 11 | Using where; Using index |
| 1 | SIMPLE | hg | ref | hosts_groups_1 | hosts_groups_1 | 8 | 3.0.h.hostid | 1 | Using index |
| 1 | SIMPLE | i | ref | PRIMARY,items_1 | items_1 | 8 | 3.0.h.hostid | 9 | Using index |
| 1 | SIMPLE | gi | ref | graphs_items_1,graphs_items_2 | graphs_items_1 | 8 | 3.0.i.itemid | 1 | |
| 1 | SIMPLE | gr | eq_ref | PRIMARY | PRIMARY | 8 | 3.0.gi.graphid | 1 | Using where |
+----+-------------+-------+--------+-------------------------------+----------------+---------+----------------+------+--------------------------+
5 rows in set (0.00 sec)
The older SQL:
PROD: MariaDB [zabbix]> explain SELECT DISTINCT g.groupid,g.name FROM groups g,hosts_groups hg,hosts h WHERE g.groupid=hg.groupid AND h.hostid=hg.hostid AND h.status IN (0,1) AND EXISTS (SELECT NULL FROM items i,graphs_items gi,graphs g WHERE hg.hostid=i.hostid AND i.itemid=gi.itemid AND gi.graphid=g.graphid AND g.flags IN (0,4)) ORDER BY g.name; +------+--------------------+-------+--------+-------------------------------+----------------+---------+-------------------+-------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------------+-------+--------+-------------------------------+----------------+---------+-------------------+-------+----------------------------------------------+ | 1 | PRIMARY | g | index | PRIMARY | groups_1 | 66 | NULL | 366 | Using index; Using temporary; Using filesort | | 1 | PRIMARY | hg | ref | hosts_groups_1,hosts_groups_2 | hosts_groups_2 | 8 | zabbix.g.groupid | 11 | Using where; Distinct | | 1 | PRIMARY | h | eq_ref | PRIMARY,hosts_2 | PRIMARY | 8 | zabbix.hg.hostid | 1 | Using where; Distinct | | 2 | DEPENDENT SUBQUERY | i | ref | PRIMARY,items_1 | items_1 | 8 | zabbix.hg.hostid | 28446 | Using index | | 2 | DEPENDENT SUBQUERY | gi | ref | graphs_items_1,graphs_items_2 | graphs_items_1 | 8 | zabbix.i.itemid | 1 | | | 2 | DEPENDENT SUBQUERY | g | eq_ref | PRIMARY | PRIMARY | 8 | zabbix.gi.graphid | 1 | Using where | +------+--------------------+-------+--------+-------------------------------+----------------+---------+-------------------+-------+----------------------------------------------+ 6 rows in set (0.00 sec) MY TEST: mysql> explain SELECT DISTINCT g.groupid,g.name FROM groups g,hosts_groups hg,hosts h WHERE g.groupid=hg.groupid AND h.hostid=hg.hostid AND h.status IN (0,1) AND EXISTS (SELECT NULL FROM items i,graphs_items gi,graphs g WHERE hg.hostid=i.hostid AND i.itemid=gi.itemid AND gi.graphid=g.graphid AND g.flags IN (0,4)) ORDER BY g.name; +----+--------------------+-------+--------+-------------------------------+----------------+---------+----------------+------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+--------+-------------------------------+----------------+---------+----------------+------+-----------------------------------------------------------+ | 1 | PRIMARY | h | range | PRIMARY,hosts_2 | hosts_2 | 4 | NULL | 11 | Using where; Using index; Using temporary; Using filesort | | 1 | PRIMARY | hg | ref | hosts_groups_1,hosts_groups_2 | hosts_groups_1 | 8 | 3.0.h.hostid | 1 | Using where; Using index | | 1 | PRIMARY | g | eq_ref | PRIMARY | PRIMARY | 8 | 3.0.hg.groupid | 1 | | | 2 | DEPENDENT SUBQUERY | i | ref | PRIMARY,items_1 | items_1 | 8 | 3.0.hg.hostid | 9 | Using index | | 2 | DEPENDENT SUBQUERY | gi | ref | graphs_items_1,graphs_items_2 | graphs_items_1 | 8 | 3.0.i.itemid | 1 | | | 2 | DEPENDENT SUBQUERY | g | eq_ref | PRIMARY | PRIMARY | 8 | 3.0.gi.graphid | 1 | Using where | +----+--------------------+-------+--------+-------------------------------+----------------+---------+----------------+------+-----------------------------------------------------------+ 6 rows in set (0.02 sec)
- is duplicated by
-
ZBX-13073 Monitoring -> Triggers page is very slow on MariaDB >= 5.3.3
-
- Closed
-
