Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-9162

Slow query for getting host groups with graphs

    Details

      Description

      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)
      

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              dotneft Alexey Pustovalov
            • Votes:
              3 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: