[ZBX-898] Events component on a screen is slow to load due to ineffecient mysql query or queries Created: 2009 May 04 Updated: 2017 May 30 Resolved: 2009 Jun 29 |
|
Status: | Closed |
Project: | ZABBIX BUGS AND ISSUES |
Component/s: | Frontend (F) |
Affects Version/s: | 1.6 |
Fix Version/s: | None |
Type: | Incident report | Priority: | Minor |
Reporter: | Jasper Spit | Assignee: | Alexei Vladishev |
Resolution: | Fixed | Votes: | 0 |
Labels: | None | ||
Remaining Estimate: | Not Specified | ||
Time Spent: | Not Specified | ||
Original Estimate: | Not Specified | ||
Environment: |
RHEL5, MySQL 5.0.45 |
Attachments: | query.log query_output.txt |
Description |
The events component in a screen is slow to generate because of an inefficient query on the MySQL database. An event list of the last 10 items takes about 5 seconds to generate. I checked the MySQL query log and saw that Zabbix executes a very large statement on the database and verified that this statement is the statement that takes a while to finish. See attached query.txt. The entire query is 14kb ! I suspect this could be done more efficiently, to speed up the GUI's response. |
Comments |
Comment by Jasper Spit [ 2009 May 04 ] |
Events component query executed on MySQL backend. |
Comment by Alexei Vladishev [ 2009 May 08 ] |
The query in very fast. I would like to see some evidence that the query is not efficient. Feel free to re-open this issue. Alexei |
Comment by Jasper Spit [ 2009 May 08 ] |
Hi, The query takes between 3 and 5 seconds when I execute it manually: mysql> \. /root/query.log .. 2059 rows in set (4.71 sec) mysql> So every refresh of the dashboard or any screen that has the events history takes at least 3 seconds to generate. I am attaching the mysql output. |
Comment by Jasper Spit [ 2009 May 08 ] |
Query execution output. |
Comment by Alexei Vladishev [ 2009 Jun 27 ] |
Note that the query.log does not contain the full query. It is truncated! It must contain additional filter conditions at the end: AND t.triggerid=f.triggerid AND f.itemid=i.itemid AND i.hostid=h.hostid AND h.status=0 I still think there is nothing to fix. Alexei |
Comment by Jasper Spit [ 2009 Jun 27 ] |
Hi Alexei, I recaptured the query from the mysql slow query log last week, I'll attach it when I am at work again. The responsetime of the query has gone up to more than 10 seconds now, it is so bad I have had to tell my fellow Zabbix admins at work not to use it because it hammers the database too much. The response of all other Zabbix queries is fine. Regards,
|
Comment by Alexei Vladishev [ 2009 Jun 29 ] |
Hi Jasper, Thanks for the additional details. We finally found what was wrong. The problem is fixed in pre-1.6.6 and trunk. For a quick fix replace line 360 of file frontends/php/include/events.inc.php $sql_from = ', hosts_groups hg '; #original line Thanks for your report! Alexei |
Comment by Jasper Spit [ 2009 Jun 29 ] |
Great ! So it did an extra unneeded scan on that table ? Thanks for the fix. |
Comment by Alexei Vladishev [ 2009 Jun 29 ] |
No, it had a reference to an unused table. :-0 Alexei |
Comment by richlv [ 2009 Sep 15 ] |
closing all resolved issues |