[ZBX-5290] Query regression on "Host status" dashboard widget for huge installations Created: 2012 Jul 09 Updated: 2017 May 30 Resolved: 2012 Jul 10 |
|
Status: | Closed |
Project: | ZABBIX BUGS AND ISSUES |
Component/s: | Frontend (F) |
Affects Version/s: | 2.1.0 |
Fix Version/s: | 2.0.2rc1, 2.1.0 |
Type: | Incident report | Priority: | Major |
Reporter: | Alexey Pustovalov | Assignee: | Unassigned |
Resolution: | Fixed | Votes: | 0 |
Labels: | performance, query, regression | ||
Remaining Estimate: | Not Specified | ||
Time Spent: | Not Specified | ||
Original Estimate: | Not Specified | ||
Environment: |
rev. 28751 |
Description |
mysql> select count from hosts;
----------
---------- mysql> select count from groups;
----------
---------- mysql> select count from hosts_groups;
----------
---------- mysql> explain SELECT DISTINCT h.hostid,h.name,hg.groupid FROM hosts h,hosts_groups hg USE INDEX ( hosts_groups_2) WHERE (hg.groupid IN ('148','25','24','21','147','11','9','26','29','28','14','19','12','18','13','10','16','15','20','17','22','4')) AND hg.hostid=h.hostid AND h.status=0 AND h.hostid BETWEEN 000000000000000 AND 099999999999999;
---
--- This query executes ~0.5seconds. This query does not use indexes for "hosts_groups" table. mysql> explain SELECT DISTINCT h.hostid,h.name,hg.groupid FROM hosts h,hosts_groups hg WHERE (hg.groupid IN ('148','25','24','21','147','11','9','26','29','28','14','19','12','18','13','10','16','15','20','17','22','4')) AND hg.hostid=h.hostid AND h.status=0 AND h.hostid BETWEEN 000000000000000 AND 099999999999999;
---
--- The new query executes ~142seconds. Mysql query optimizer uses "hosts_groups_1". But query selects from "hosts" table almost all of data. So this query executes much longer. |
Comments |
Comment by Alexey Pustovalov [ 2012 Jul 09 ] | ||||||||||||||||||||||||||||||
This is old SQL query: mysql> explain SELECT DISTINCT h.hostid,h.name,hg.groupid FROM hosts h,hosts_groups hg WHERE (hg.groupid IN ('12','13','14','15','24','28','10','11','26','4','18','148','22','16','25','29','21','17','9','20','147','19')) AND hg.hostid=h.hostid AND hg.groupid BETWEEN 000000000000000 AND 099999999999999 AND h.status=0;
---
--- This query executes about 0.43 sec. | ||||||||||||||||||||||||||||||
Comment by Toms (Inactive) [ 2012 Jul 10 ] | ||||||||||||||||||||||||||||||
Fixed in dev. branch: svn://svn.zabbix.com/branches/dev/ZBX-5290 | ||||||||||||||||||||||||||||||
Comment by Pavels Jelisejevs (Inactive) [ 2012 Jul 11 ] | ||||||||||||||||||||||||||||||
TESTED. | ||||||||||||||||||||||||||||||
Comment by Toms (Inactive) [ 2012 Jul 12 ] | ||||||||||||||||||||||||||||||
Fixed in 2.0.2rc1 r28823, 2.1.0 r28824 |