[ZBX-9162] Slow query for getting host groups with graphs Created: 2014 Dec 23  Updated: 2017 May 30  Resolved: 2016 Apr 07

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Frontend (F)
Affects Version/s: 2.2.8, 2.4.3
Fix Version/s: 2.2.12rc1, 3.0.2rc1, 3.2.0alpha1

Type: Incident report Priority: Blocker
Reporter: Alexey Pustovalov Assignee: Unassigned
Resolution: Fixed Votes: 3
Labels: performance, sql
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate

 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)


 Comments   
Comment by Alexander Vladishev [ 2014 Dec 29 ]

Similar issue: ZBX-7194

Comment by Gunars Pujats (Inactive) [ 2016 Mar 23 ]

(1) No translation strings changed.

sasha CLOSED

Comment by Gunars Pujats (Inactive) [ 2016 Mar 23 ]

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

Comment by Alexander Vladishev [ 2016 Mar 29 ]

(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=...
                                                                 ^]

gunarspujats RESOLVED in r59176

sasha CLOSED

Comment by Alexander Vladishev [ 2016 Apr 01 ]

(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

sasha RESOLVED in r59239

iivs 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

sasha 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

iivs Ok, thanks.
CLOSED

Comment by Ivo Kurzemnieks [ 2016 Apr 07 ]

TESTED

Comment by Alexander Vladishev [ 2016 Apr 07 ]

Fixed in:

  • pre-2.2.12 r59334
  • pre-3.0.2 r59335
  • pre-3.1.0 (trunk) r59336
Comment by Alexander Vladishev [ 2016 Apr 07 ]

(4) updated API documentation:

iivs CLOSED

Comment by Oleksii Zagorskyi [ 2016 Dec 29 ]

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

Generated at Fri Mar 29 13:36:03 EET 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.