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)
      

        Activity

        Hide
        Alexander Vladishev added a comment -

        Similar issue: ZBX-7194

        Show
        Alexander Vladishev added a comment - Similar issue: ZBX-7194
        Hide
        Gunars Pujats (Inactive) added a comment - - edited

        (1) No translation strings changed.

        Alexander Vladishev CLOSED

        Show
        Gunars Pujats (Inactive) added a comment - - edited (1) No translation strings changed. Alexander Vladishev CLOSED
        Hide
        Gunars Pujats (Inactive) added a comment -

        RESOLVED in development branch svn://svn.zabbix.com/branches/dev/ZBX-9162

        Show
        Gunars Pujats (Inactive) added a comment - RESOLVED in development branch svn://svn.zabbix.com/branches/dev/ZBX-9162
        Hide
        Alexander Vladishev added a comment - - edited

        (2) SQL errors on Monitoring -> Graphs page:

            pg_query(): Query failed: ERROR:  column g.groupid does not exist
            LINE 1: ...s_items gi,graphs g,hosts h,hosts_groups hg WHERE g.groupid=...
                                                                         ^ [charts.php:82 → CPageFilter->__construct() → CPageFilter->_initGroups() → CAPIObject->get() → CAPIObject->__call() → czbxrpc::call() → czbxrpc::callAPI() → call_user_func() → CHostGroup->get() → DBselect() → pg_query() in include/db.inc.php:384]
            Error in query [SELECT   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))] [ERROR:  column g.groupid does not exist
            LINE 1: ...s_items gi,graphs g,hosts h,hosts_groups hg WHERE g.groupid=...
                                                                         ^]
        

        Gunars Pujats RESOLVED in r59176

        Alexander Vladishev CLOSED

        Show
        Alexander Vladishev added a comment - - edited (2) SQL errors on Monitoring -> Graphs page: pg_query(): Query failed: ERROR: column g.groupid does not exist LINE 1: ...s_items gi,graphs g,hosts h,hosts_groups hg WHERE g.groupid=... ^ [charts.php:82 → CPageFilter->__construct() → CPageFilter->_initGroups() → CAPIObject->get() → CAPIObject->__call() → czbxrpc::call() → czbxrpc::callAPI() → call_user_func() → CHostGroup->get() → DBselect() → pg_query() in include/db.inc.php:384] Error in query [SELECT 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))] [ERROR: column g.groupid does not exist LINE 1: ...s_items gi,graphs g,hosts h,hosts_groups hg WHERE g.groupid=... ^] Gunars Pujats RESOLVED in r59176 Alexander Vladishev CLOSED
        Hide
        Alexander Vladishev added a comment - - edited

        (3) other options also must be improved: monitored_hosts, real_hosts, templated_hosts, not_proxy_hosts, with_hosts_and_templates, with_items, with_monitored_items, with_simple_graph_items, with_triggers, with_monitored_triggers, with_httptests, with_monitored_httptests, with_graphs, with_applications

        Alexander Vladishev RESOLVED in r59239

        Ivo Kurzemnieks Can you take a look at two options templated_hosts and with_monitored_triggers one more time. They both work slower right now. At least for me.

        • templated_hosts
          • old SQL:
            SELECT SQL_NO_CACHE DISTINCT g.groupid FROM groups g,hosts_groups hg,hosts h WHERE hg.groupid=g.groupid AND h.hostid=hg.hostid AND h.status=3
            

            Mostly 0.002s, but sometimes 0.337s (1 time out of 6-17)

          • new SQL:
            SELECT SQL_NO_CACHE g.groupid FROM groups g WHERE EXISTS (SELECT NULL FROM hosts h,hosts_groups hg WHERE hg.hostid=h.hostid AND h.status='3' AND g.groupid=hg.groupid)
            

            0.362s

        • with_monitored_triggers
          • old SQL:
            SELECT SQL_NO_CACHE DISTINCT g.groupid FROM groups g,hosts_groups hg WHERE g.groupid=hg.groupid AND EXISTS (SELECT NULL FROM items i,hosts h,functions f,triggers t WHERE hg.hostid=i.hostid AND i.hostid=h.hostid AND i.itemid=f.itemid AND f.triggerid=t.triggerid AND h.status=0 AND i.status=0 AND t.status=0 AND t.flags IN (0,4))
            

            8.130s

          • new SQL:
            SELECT SQL_NO_CACHE g.groupid FROM groups g WHERE EXISTS (SELECT NULL FROM items i,hosts h,functions f,triggers t,hosts_groups hg WHERE hg.hostid=i.hostid AND hg.hostid=h.hostid AND i.itemid=f.itemid AND f.triggerid=t.triggerid AND h.status='0' AND i.status='0' AND t.status='0' AND t.flags IN ('0','4') AND g.groupid=hg.groupid)
            

            17.874s

        MySQL 5.0

        REOPENED

        Alexander Vladishev on MySQL 5.5 the new SQL queries run faster

          old new
        real_hosts 2.85 0.03
        templated_hosts 0.00 0.17
        with_monitored_triggers 0.08 0.05
        real_hosts + with_graphs 2.99 0.07

        I think we can close the issue.

        RESOLVED

        Ivo Kurzemnieks Ok, thanks.
        CLOSED

        Show
        Alexander Vladishev added a comment - - edited (3) other options also must be improved: monitored_hosts, real_hosts, templated_hosts, not_proxy_hosts, with_hosts_and_templates, with_items, with_monitored_items, with_simple_graph_items, with_triggers, with_monitored_triggers, with_httptests, with_monitored_httptests, with_graphs, with_applications Alexander Vladishev RESOLVED in r59239 Ivo Kurzemnieks Can you take a look at two options templated_hosts and with_monitored_triggers one more time. They both work slower right now. At least for me. templated_hosts old SQL: SELECT SQL_NO_CACHE DISTINCT g.groupid FROM groups g,hosts_groups hg,hosts h WHERE hg.groupid=g.groupid AND h.hostid=hg.hostid AND h.status=3 Mostly 0.002s, but sometimes 0.337s (1 time out of 6-17) new SQL: SELECT SQL_NO_CACHE g.groupid FROM groups g WHERE EXISTS (SELECT NULL FROM hosts h,hosts_groups hg WHERE hg.hostid=h.hostid AND h.status='3' AND g.groupid=hg.groupid) 0.362s with_monitored_triggers old SQL: SELECT SQL_NO_CACHE DISTINCT g.groupid FROM groups g,hosts_groups hg WHERE g.groupid=hg.groupid AND EXISTS (SELECT NULL FROM items i,hosts h,functions f,triggers t WHERE hg.hostid=i.hostid AND i.hostid=h.hostid AND i.itemid=f.itemid AND f.triggerid=t.triggerid AND h.status=0 AND i.status=0 AND t.status=0 AND t.flags IN (0,4)) 8.130s new SQL: SELECT SQL_NO_CACHE g.groupid FROM groups g WHERE EXISTS (SELECT NULL FROM items i,hosts h,functions f,triggers t,hosts_groups hg WHERE hg.hostid=i.hostid AND hg.hostid=h.hostid AND i.itemid=f.itemid AND f.triggerid=t.triggerid AND h.status='0' AND i.status='0' AND t.status='0' AND t.flags IN ('0','4') AND g.groupid=hg.groupid) 17.874s MySQL 5.0 REOPENED Alexander Vladishev on MySQL 5.5 the new SQL queries run faster   old new real_hosts 2.85 0.03 templated_hosts 0.00 0.17 with_monitored_triggers 0.08 0.05 real_hosts + with_graphs 2.99 0.07 I think we can close the issue. RESOLVED Ivo Kurzemnieks Ok, thanks. CLOSED
        Hide
        Ivo Kurzemnieks added a comment -

        TESTED

        Show
        Ivo Kurzemnieks added a comment - TESTED
        Hide
        Alexander Vladishev added a comment -

        Fixed in:

        • pre-2.2.12 r59334
        • pre-3.0.2 r59335
        • pre-3.1.0 (trunk) r59336
        Show
        Alexander Vladishev added a comment - Fixed in: pre-2.2.12 r59334 pre-3.0.2 r59335 pre-3.1.0 (trunk) r59336
        Show
        Alexander Vladishev added a comment - - edited (4) updated API documentation: https://www.zabbix.com/documentation/2.2/manual/api/changes_2.2#hostgroup https://www.zabbix.com/documentation/3.0/manual/api/changes_3.0#hostgroup Ivo Kurzemnieks CLOSED
        Hide
        Oleksiy Zagorskyi added a comment -

        ZBX-11654 complains that current change caused a performance regression.

        Show
        Oleksiy Zagorskyi added a comment - ZBX-11654 complains that current change caused a performance regression.

          People

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

            Dates

            • Created:
              Updated:
              Resolved: