[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: Text File query.log     Text File 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,

  • Jasper
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
$sql_from = ''; # correct one

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

Generated at Fri Mar 29 16:15:01 EET 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.