[ZBX-11116] Zabbix 3.0.4 dashboard slow in Last issues after update Created: 2016 Aug 19 Updated: 2019 Oct 04 Resolved: 2019 Oct 04 |
|
Status: | Closed |
Project: | ZABBIX BUGS AND ISSUES |
Component/s: | Frontend (F) |
Affects Version/s: | 3.0.4 |
Fix Version/s: | None |
Type: | Problem report | Priority: | Major |
Reporter: | Bezaleel Ramos | Assignee: | Zabbix Development Team |
Resolution: | Won't fix | Votes: | 6 |
Labels: | database, frontend, oracle, php, redhat | ||
Remaining Estimate: | Not Specified | ||
Time Spent: | Not Specified | ||
Original Estimate: | Not Specified | ||
Environment: |
Oracle 11g |
Attachments: | ZABBIX_explain.txt debug_3_0_4_user_last_issue.txt zabbix_debug_2_4_mysql.txt zabbix_explain_5queries.txt zabbix_oracle_em.jpg zabbixslow1.jpg | ||||||||
Issue Links: |
|
Description |
Hello, We updated the Zabbix from 2.2.12 to 3.0.4 and we're realizing that dashboard this slow to show the "last 20 issues" The response time of page is fast (1s) , but when show the "Last 20 issues", takes about 30s - 1min Any idea ? Thanks |
Comments |
Comment by Aleksandrs Saveljevs [ 2016 Aug 22 ] |
How quickly did it load in Zabbix 2.2.12? How large is your installation? If you enable debug mode for your frontend user, could you please show the debug output for the "Last 20 issues" widget? |
Comment by Bezaleel Ramos [ 2016 Aug 23 ] |
Hello Aleksandrs Saveljevs, How quickly did it load in Zabbix 2.2.12? At version 2.2.12 the load dashboard "Last 20 issues" was 15s. How large is your installation? What do you mean large installation? amount of hosts or size Database? Amount hosts: Number of hosts (enabled/disabled/templates) 2082 1650 / 323 / 109 Follow attached file with debug user Thanks Aleksandrs Saveljevs |
Comment by Aleksandrs Saveljevs [ 2016 Aug 23 ] |
Thank you! According to the debug log, the following queries are slow: SQL (88.935793): SELECT * FROM (SELECT t.triggerid,t.expression,t.description,t.url,t.priority,t.lastchange,t.comments,t.error,t.state 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 ('13','25') 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.priority IN ('2','3','4','5') AND t.value='1' AND t.flags IN ('0','4') ORDER BY t.lastchange DESC) WHERE rownum BETWEEN 0 AND 40 zabbix.php:21 → require_once() → ZBase->run() → ZBase->processRequest() → CView->getOutput() → include() → 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:987 SQL (112.245468): SELECT COUNT(DISTINCT t.triggerid) AS rowscount 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 ('13','25') 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.priority IN ('2','3','4','5') AND t.value='1' AND t.flags IN ('0','4') zabbix.php:21 → require_once() → ZBase->run() → ZBase->processRequest() → CView->getOutput() → include() → 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:425 |
Comment by Aleksandrs Saveljevs [ 2016 Aug 23 ] |
Could you please attach explanations of these queries and your database schema? |
Comment by richlv [ 2016 Aug 24 ] |
bezarsnba, i believe asaveljevs meant something like this : https://docs.oracle.com/cd/B28359_01/server.111/b28274/ex_plan.htm |
Comment by Bezaleel Ramos [ 2016 Aug 24 ] |
Hello, I'm sorry Follow attached Thans |
Comment by Bezaleel Ramos [ 2016 Aug 24 ] |
Hi, This query make one FULL table scan at table "RIGHTS". Why this execution query so many times? At ZABBIX's forum exists one person with the same problem like below: https://www.zabbix.com/forum/showthread.php?p=187998#post187998 Follow attached of dashboard enterprise Manager Thanks |
Comment by Aleksandrs Saveljevs [ 2016 Aug 29 ] |
The linked forum thread says that the issue appeared after upgrading from 3.0.3 to 3.0.4 for the other person. If you downgrade your frontend to 3.0.3, does it make it better? |
Comment by Bezaleel Ramos [ 2016 Aug 29 ] |
Hi Aleksandrs Saveljevs I did downgrade frontend to 3.0.3 but not solved, It's very slow the dashboard,Triggers and events for no-super-admin. I enable the debug mode for frontend user, the following queries are slow: SQL (149.686897): SELECT * FROM (SELECT t.triggerid,t.expression,t.description,t.url,t.priority,t.lastchange,t.comments,t.error,t.state 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 ('13','25') 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.priority IN ('2','3','4','5') AND t.value='1' AND t.flags IN ('0','4') ORDER BY t.lastchange DESC) WHERE rownum BETWEEN 0 AND 40; SQL (541.54619): SELECT COUNT(DISTINCT t.triggerid) AS rowscount 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 ('13','25') 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.priority IN ('2','3','4','5') AND t.value='1' AND t.flags IN ('0','4'); SQL (86.063672): SELECT * FROM (SELECT e.* FROM events e WHERE e.objectid='88450' AND e.object='0' AND e.source='0' AND e.eventid<='59311430' ORDER BY e.clock DESC,e.eventid DESC) WHERE rownum BETWEEN 0 AND 20 SQL (63.177997): SELECT * FROM (SELECT e.* FROM events e WHERE e.objectid='88352' AND e.object='0' AND e.source='0' AND e.eventid<='58345108' ORDER BY e.clock DESC,e.eventid DESC) WHERE rownum BETWEEN 0 AND 20 SQL (52.085826): SELECT * FROM (SELECT e.* FROM events e WHERE e.objectid='30149' AND e.object='0' AND e.source='0' AND e.eventid<='57804982' ORDER BY e.clock DESC,e.eventid DESC) WHERE rownum BETWEEN 0 AND 20 Sorry for creating duplicate problem . Beza |
Comment by Bezaleel Ramos [ 2016 Aug 29 ] |
I enabled the debug mode at Zabbix 2.4.0 in Mysql for verify the queries. Follow attached Beza |
Comment by Aleksandrs Saveljevs [ 2016 Aug 30 ] |
Thank you for all the information! It should greatly help in investigating this issue. |
Comment by Alexander Vladishev [ 2019 Oct 04 ] |
Already fixed in version 4.0 by using new table "problem" instead of "events". Closed as Won't Fix. |