-
Change Request
-
Resolution: Unresolved
-
Minor
-
None
-
3.4.8
-
None
Steps to reproduce:
- Open Overview or Triggers page from regular user account.
- Select Group from drop-down list
Result:
Slow webpage response
Expected:
For Zabbix Super Admins this is much faster.
I think it's similar to ------ZBX-12225
Overview page
******************** Script profiler ********************
Total time: 26.716641
Total SQL time: 23.468303
SQL count: 1122 (selects: 1118 | executes: 4)
Problem sql query:
SQL (21.215202): SELECT DISTINCT t.triggerid,t.priority,t.value 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 ('11','13','21') 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='26' 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.flags IN ('0','4')}}
{{overview.php:218 → CView->render() → include() → getItemsDataOverview() → CApiWrapper->__call() → CFrontendApiWrapper->callMethod() → CApiWrapper->callMethod() → CFrontendApiWrapper->callClientMethod() → CLocalApiClient->callMethod() → CTrigger->get() → CApiService->customFetch() → DBselect() in include/classes/api/CApiService.php:1054
This query is very slow. I don't really understand how this query works, but if I just add hg.groupid='26' as in ------ZBX-12225
SELECT DISTINCT t.triggerid,t.priority,t.value 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 ('11','13','21') WHERE t.triggerid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hgg.hostid AND hg.groupid='26' 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='26' 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.flags IN ('0','4')
Triggers page
******************** Script profiler ********************
Total time: 28.959818
Total SQL time: 26.564391
SQL count: 529 (selects: 525 | executes: 4)
Slow sql query:
SQL (24.352859): SELECT * FROM (SELECT DISTINCT t.triggerid,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 ('11','13','21') 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='29' 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.flags IN ('0','4') ORDER BY t.lastchange) WHERE rownum BETWEEN 0 AND 2002
tr_status.php:259 → CApiWrapper->__call() → CFrontendApiWrapper->callMethod() → CApiWrapper->callMethod() → CFrontendApiWrapper->callClientMethod() → CLocalApiClient->callMethod() → CTrigger->get() → CApiService->customFetch() → DBselect() in include/classes/api/CApiService.php:1022
Same I could significantly speed up this query by adding groupid to block
SELECT * FROM (SELECT DISTINCT t.triggerid,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 ('11','13','21') WHERE t.triggerid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hgg.hostid AND hg.groupid='29' 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='29' 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.flags IN ('0','4') ORDER BY t.lastchange) WHERE rownum BETWEEN 0 AND 2002
Triggers all groups
******************** Script profiler ********************
Total time: 30.227013
Total SQL time: 28.09208
SQL count: 468 (selects: 464 | executes: 4)
Slow query:
SQL (24.656316): SELECT * FROM (SELECT t.triggerid,t.lastchange FROM triggers t 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 ('11','13','21') 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 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.flags IN ('0','4') ORDER BY t.lastchange) WHERE rownum BETWEEN 0 AND 2002
tr_status.php:259 → CApiWrapper->__call() → CFrontendApiWrapper->callMethod() → CApiWrapper->callMethod() → CFrontendApiWrapper->callClientMethod() → CLocalApiClient->callMethod() → CTrigger->get() → CApiService->customFetch() → DBselect() in include/classes/api/CApiService.php:1022
But I don't know how to improve speed for this query.