-
Incident report
-
Resolution: Fixed
-
Blocker
-
2.2.8, 2.4.3
Current query is terrible slow on installations with a lot of hosts, groups:
mysql> 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)); +---------+-------------------------------------------------------------------------+ 27 rows in set (2.59 sec)
New query is much faster:
mysql> SELECT SQL_NO_CACHE g.groupid,g.name FROM groups g WHERE EXISTS (SELECT NULL FROM items i,graphs_items gi,graphs g,hosts h,hosts_groups hg WHERE g.groupid=hg.groupid AND h.hostid=i.hostid AND i.itemid=gi.itemid AND gi.graphid=g.graphid AND g.flags IN (0,4) AND h.hostid=hg.hostid AND h.status IN (0,1)); +---------+-------------------------------------------------------------------------+ 27 rows in set (0.00 sec)
and has better query plan:
+----+--------------------+-------+--------+--------------------------------+----------------+---------+-------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+--------+--------------------------------+----------------+---------+-------------------+------+--------------------------+ | 1 | PRIMARY | g | index | NULL | groups_1 | 194 | NULL | 34 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | hg | ref | hosts_groups_1,hosts_groups_2 | hosts_groups_2 | 8 | zabbix.g.groupid | 6018 | | | 2 | DEPENDENT SUBQUERY | h | eq_ref | PRIMARY,hosts_2 | PRIMARY | 8 | zabbix.hg.hostid | 1 | Using where | | 2 | DEPENDENT SUBQUERY | i | ref | PRIMARY,items_1 | items_1 | 8 | zabbix.hg.hostid | 50 | 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)
old query plan:
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)); +----+--------------------+-------+--------+--------------------------------+----------------+---------+-------------------+-------+-------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+--------+--------------------------------+----------------+---------+-------------------+-------+-------------------------------------------+ | 1 | PRIMARY | h | range | PRIMARY,hosts_2 | hosts_2 | 4 | NULL | 15434 | Using where; Using index; Using temporary | | 1 | PRIMARY | hg | ref | hosts_groups_1,hosts_groups_2 | hosts_groups_1 | 8 | zabbix.h.hostid | 1 | Using where; Using index | | 1 | PRIMARY | g | eq_ref | PRIMARY | PRIMARY | 8 | zabbix.hg.groupid | 1 | | | 2 | DEPENDENT SUBQUERY | i | ref | PRIMARY,items_1 | items_1 | 8 | zabbix.hg.hostid | 50 | 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)