[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
PHP 5.5
Apache 2.2
RedHat 6


Attachments: Text File ZABBIX_explain.txt     Text File debug_3_0_4_user_last_issue.txt     Text File zabbix_debug_2_4_mysql.txt     Text File zabbix_explain_5queries.txt     JPEG File zabbix_oracle_em.jpg     JPEG File zabbixslow1.jpg    
Issue Links:
Duplicate
is duplicated by ZBX-11133 CLONE - Zabbix 3.0.4 dashboard slow i... Closed

 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
Beza



 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
Number of items (enabled/disabled/not supported) 31618 28983 / 2416 / 219
Number of triggers (enabled/disabled [problem/ok]) 12133 11254 / 879 [21 / 11233]

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.

Generated at Thu Mar 28 23:17:30 EET 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.