[ZBX-9850] Slow Latest 20 issues for a lot of PROBLEM triggers with filtering by host groups Created: 2015 Sep 04  Updated: 2017 Sep 21  Resolved: 2017 Sep 21

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Frontend (F)
Affects Version/s: 2.4.6
Fix Version/s: None

Type: Incident report Priority: Blocker
Reporter: Alexey Pustovalov Assignee: Unassigned
Resolution: Won't fix Votes: 1
Labels: dashboard, performance, widget
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate

 Description   

Currently Zabbix makes a few same queries like:

SQL (36.204838): SELECT DISTINCT t.triggerid,t.state,t.error,t.url,t.expression,t.description,t.priority,t.lastchange FROM triggers t,functions f,items i,hosts_groups hg WHERE NOT EXISTS (SELECT NULL FROM functions f,items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid IN ('100100000000011','100100000000030','100100000000066','100100000000136') WHERE t.triggerid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MAX(permission)<'2' OR MIN(permission) IS NULL OR MIN(permission)=0) AND hg.hostid=i.hostid AND f.triggerid=t.triggerid AND f.itemid=i.itemid AND hg.groupid IN ('100100000000784','100100000000785','100100000000787','100100000001627','100100000001628') AND NOT EXISTS (SELECT NULL FROM functions f,items i,hosts h WHERE t.triggerid=f.triggerid AND f.itemid=i.itemid AND i.hostid=h.hostid AND (i.status<>0 OR h.status<>0)) AND t.status=0 AND t.priority BETWEEN '0' AND '5' AND t.value='1' AND t.flags IN ('0','4') ORDER BY t.lastchange DESC LIMIT 40 OFFSET 0
dashboard.php:145 ? make_latest_issues() ? CFrontendApiWrapper->get() ? CApiWrapper->__call() ? CFrontendApiWrapper->callMethod() ? CApiWrapper->callMethod() ? CFrontendApiWrapper->callClientMethod() ? CLocalApiClient->callMethod() ? call_user_func_array() ? CTrigger->get() ? CApiService->customFetch() ? DBselect() in ./include/classes/api/CApiService.php:984

and 

SQL (38.311494): SELECT DISTINCT COUNT(DISTINCT t.triggerid) AS rowscount FROM triggers t,functions f,items i,hosts_groups hg WHERE NOT EXISTS (SELECT NULL FROM functions f,items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid IN ('100100000000011','100100000000030','100100000000066','100100000000136') WHERE t.triggerid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MAX(permission)<'2' OR MIN(permission) IS NULL OR MIN(permission)=0) AND hg.hostid=i.hostid AND f.triggerid=t.triggerid AND f.itemid=i.itemid AND hg.groupid IN ('100100000000784','100100000000785','100100000000787','100100000001627','100100000001628') AND NOT EXISTS (SELECT NULL FROM functions f,items i,hosts h WHERE t.triggerid=f.triggerid AND f.itemid=i.itemid AND i.hostid=h.hostid AND (i.status<>0 OR h.status<>0)) AND t.status=0 AND t.priority BETWEEN '0' AND '5' AND t.value='1' AND t.flags IN ('0','4')
dashboard.php:145 ? make_latest_issues() ? CFrontendApiWrapper->get() ? CApiWrapper->__call() ? CFrontendApiWrapper->callMethod() ? CApiWrapper->callMethod() ? CFrontendApiWrapper->callClientMethod() ? CLocalApiClient->callMethod() ? call_user_func_array() ? CTrigger->get() ? DBselect() in ./include/classes/api/services/CTrigger.php:428

In case of more than 5000 triggers in PROBLEM, these queries can take long time.

 Comments   
Comment by Oleg Ivanivskyi [ 2016 Feb 21 ]

The mysqlslap benchmarks for the first SQL query from the description:
1 concurrent user:

Average number of seconds to run all queries: 25.088 seconds
Minimum number of seconds to run all queries: 25.088 seconds
Maximum number of seconds to run all queries: 25.088 seconds
Number of clients running queries: 1
Average number of queries per client: 1

5 concurrent users:

Average number of seconds to run all queries: 108.814 seconds
Minimum number of seconds to run all queries: 108.814 seconds
Maximum number of seconds to run all queries: 108.814 seconds
Number of clients running queries: 5
Average number of queries per client: 1

10 concurrent users:

Average number of seconds to run all queries: 271.470 seconds
Minimum number of seconds to run all queries: 271.470 seconds
Maximum number of seconds to run all queries: 271.470 seconds
Number of clients running queries: 10
Average number of queries per client: 1

Comment by Oleg Ivanivskyi [ 2016 May 12 ]

Query without single quotes works in many times faster (e.g. 10 seconds instead of 5 minutes):

SELECT SQL_NO_CACHE DISTINCT t.triggerid,t.priority,t.state,t.description,t.error,t.value,t.lastchange,t.expression FROM triggers t,functions f,items i,hosts_groups hg WHERE NOT EXISTS (SELECT NULL FROM functions f,items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid IN (41,53,55) WHERE t.triggerid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MAX(permission)<2 OR MIN(permission) IS NULL OR MIN(permission)=0) AND hg.hostid=i.hostid AND f.triggerid=t.triggerid AND f.itemid=i.itemid AND hg.groupid IN (188,759,764,855,926,991,1012,1019,1024,1028,1045,1060,1061,1066,1067,1078,1290,1293,1295,1300,1303,1308,1341,1354,1468,1471,1501,1507,1515,1705,1722,1726,1822,1832,1840,1926,1946,2006,2026,2095,2332,2334,2384,2386,2504,2509,2526,2558,2563,2569,2712,2778,2811,2891,2913,2920,2958,3036) AND NOT EXISTS (SELECT NULL FROM functions f,items i,hosts h WHERE t.triggerid=f.triggerid AND f.itemid=i.itemid AND i.hostid=h.hostid AND (i.status<>0 OR h.status<>0)) AND t.status=0 AND t.value=1 AND t.flags IN (0,4) ORDER BY t.lastchange DESC

MySQL 5.6.30

Comment by Aleksandrs Saveljevs [ 2016 Jul 26 ]

See also ZBX-11011 for the quoteless query suggestion.

Comment by Alexander Vladishev [ 2017 Sep 21 ]

I confirm this issue on MySQL 5.6.30. SQL statements with single quotes around numbers works in many times slower on this version of MySQL.

On MySQL 5.7.19 SQL queries with and without quotes are executes equally fast.

This is more like a MySQL problem.

Comment by Alexander Vladishev [ 2017 Sep 21 ]

I close this issue as "Won't Fix".

Generated at Sun Aug 03 03:41:05 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.