[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: |
|
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.
MySQL 5.0 REOPENED sasha on MySQL 5.5 the new SQL queries run faster
I think we can close the issue. RESOLVED iivs Ok, thanks. | |||||||||||||||
Comment by Ivo Kurzemnieks [ 2016 Apr 07 ] | |||||||||||||||
TESTED | |||||||||||||||
Comment by Alexander Vladishev [ 2016 Apr 07 ] | |||||||||||||||
Fixed in:
| |||||||||||||||
Comment by Alexander Vladishev [ 2016 Apr 07 ] | |||||||||||||||
(4) updated API documentation:
iivs CLOSED | |||||||||||||||
Comment by Oleksii Zagorskyi [ 2016 Dec 29 ] | |||||||||||||||
|