[ZBX-6296] Monitoring > Latest Data is slow on large setups Created: 2013 Feb 20  Updated: 2018 Nov 22  Resolved: 2014 Jan 31

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Frontend (F)
Affects Version/s: None
Fix Version/s: 2.2.2rc1, 2.3.0

Type: Incident report Priority: Major
Reporter: Toms (Inactive) Assignee: Unassigned
Resolution: Fixed Votes: 2
Labels: latestdata, performance
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: Text File zabbix_latest_data_query.txt     Text File zabbix_latest_data_query_zabbix_22rc1.txt    
Issue Links:
Duplicate

 Description   

On large setups Latest Data page is slow. There is at least one case with page generation in ~17 seconds;



 Comments   
Comment by vairel [ 2013 Jun 06 ]

in latest.php

//$available_hosts = $pageFilter->hostsSelected ? array_keys($pageFilter->hosts) : array();
if ( $pageFilter->hostid > 0 ) $available_hosts = array( $pageFilter->hostid );
else $available_hosts = $pageFilter->hostsSelected ? array_keys($pageFilter->hosts) : array();

Comment by Alexander Vladishev [ 2013 Nov 13 ]

Huge number of SQL statements in the "Latest data" screen:

SQL (0.000694): SELECT DISTINCT h.hostid FROM hosts h,items i WHERE h.flags IN (0,4) AND i.itemid='25830' AND h.hostid=i.hostid AND h.status IN (0,1,3)
latest.php:204 → itemName() → resolveItemKeyMacros() → CAPIObject->resolveItem() → CAPIObject->__call() → czbxrpc::call() → czbxrpc::callAPI() → call_user_func() → CUserMacro->resolveItem() → CUserMacro->getMacros() → CHost->get() → DBselect() in api/classes/CHost.php:430

SQL (0.000734): SELECT hm.* FROM hostmacro hm WHERE hm.hostid='10062'
latest.php:204 → itemName() → resolveItemKeyMacros() → CAPIObject->resolveItem() → CAPIObject->__call() → czbxrpc::call() → czbxrpc::callAPI() → call_user_func() → CUserMacro->resolveItem() → CUserMacro->getMacros() → CUserMacro->get() → DBselect() in api/classes/CUserMacro.php:230

SQL (0.000712): SELECT DISTINCT h.hostid,h.templateid FROM hosts h,hosts_templates ht WHERE h.status=3 AND ht.hostid='10062' AND h.hostid=ht.templateid
latest.php:204 → itemName() → resolveItemKeyMacros() → CAPIObject->resolveItem() → CAPIObject->__call() → czbxrpc::call() → czbxrpc::callAPI() → call_user_func() → CUserMacro->resolveItem() → CUserMacro->getMacros() → CTemplate->get() → DBselect() in api/classes/CTemplate.php:313

SQL (0.00067): SELECT hm.* FROM hostmacro hm WHERE hm.hostid IN ('10052','10116','10156','10157')
latest.php:204 → itemName() → resolveItemKeyMacros() → CAPIObject->resolveItem() → CAPIObject->__call() → czbxrpc::call() → czbxrpc::callAPI() → call_user_func() → CUserMacro->resolveItem() → CUserMacro->getMacros() → CUserMacro->get() → DBselect() inapi/classes/CUserMacro.php:230
Comment by Dimitri Bellini [ 2013 Nov 15 ]

I also notice the same behaviour in my big installation and using Zabbix Stable 2.2 with a hige single Host (is a switch with 2287 items).
I have attached a file with the query.
The frontend is not useable
If you need some other debug log please ask.
Regards,

Comment by Dimitri Bellini [ 2013 Nov 15 ]

Attached:File with Zabbix Latest Data slow Query

Comment by Eduards Samersovs (Inactive) [ 2013 Dec 03 ]

Fixed in development branch svn://svn.zabbix.com/branches/dev/ZBX-6296

Comment by Dimitri Bellini [ 2013 Dec 04 ]

Update file after install zabbix 2.2rc1

Comment by Dimitri Bellini [ 2013 Dec 04 ]

I have tested the new Zabbix 2.2.1rc1 but i will still suffer of the slow query time using the frontend "Latest Data" page.
I have attached the Debug log.
Please ask me for every other detail.

Comment by Oleksii Zagorskyi [ 2013 Dec 04 ]

Dimitri, you should not test Zabbix 2.2.1rc1 until it will not be closed because current changes are available only in development SVN branch.
So you have just tested the same related code as 2.2.0.

You can see changes here https://www.zabbix.org/websvn/wsvn/zabbix.com/branches/dev/ZBX-6296/?op=log&isdir=1&

Comment by Oleksii Zagorskyi [ 2013 Dec 04 ]

Well, you could test it before it will be closed, it's even would be very good
But you need to take sources from the development SVN branch.
Some instructions you can find here https://www.zabbix.org/wiki/Get_Zabbix and here https://www.zabbix.org/wiki/Compilation_instructions

Comment by Dimitri Bellini [ 2013 Dec 04 ]

Hi guy! sorry i have read the release notes of Zabbix 2.2.1rc1 and i missunderstand this point:

  • ZBX-7373 improved performance of pages that use latest history values; thanks to Kenneth Palmertree and Maksim Tkachenko
    And also the top of this page "Fix Version/s:2.2.1rc1"

Thanks so much i will try SVN.

Comment by Dimitri Bellini [ 2013 Dec 05 ]

I have tested the latest 2.2 svn branch (05/12/2013) compared to the old Zabbix 2.0.9 with the same host configuration and running the "latest data" page i have very huge difference, below detail:

HOST Configuration

  • HOSTNAME01 Applications (7) Items (4422) Triggers (2639) Graphs (198) Discovery (2)

Zabbix 2.0.9

******************** Script profiler ********************
Total time: 1.073098
Total SQL time: 0.563685
SQL count: 44 (selects: 21 | executes: 23)
Peak memory usage: 11.450989M
Memory limit: 512M

Zabbix 2.2 svn (05/12/2013)

******************** Script profiler ********************
Total time: 11.397692
Total SQL time: 4.470916
SQL count: 4556 (selects: 4517 | executes: 39)
Peak memory usage: 103M
Memory limit: 1G

----------

On new 2.2 you can see very huge difference in general but mainly the "Total time" compared to "Total SQL time" is very high.
If you need other info please ask me.
Thanks

Comment by Dimitri Bellini [ 2013 Dec 06 ]

UPDATE: I have also tested the TRUNK (version 06/12/2013) but seems the same performance of yestarday SVN:

******************** Script profiler ********************
Total time: 11.126864
Total SQL time: 4.587335
SQL count: 4544 (selects: 4515 | executes: 29)
Peak memory usage: 102.75M
Memory limit: 1G
Comment by Alexander Vladishev [ 2013 Dec 09 ]

Dimitri,

Under this issue will be fixed problems with resolving of macros in the Monitoring->Latest Data screen.

Your problem is already solved under ZBX-7373. Please leave comments there if the problem remains not resolved.

Thank you!

Comment by Vlad Vnukovsky [ 2013 Dec 10 ]

I did compare the performance of the page Latest data in versions 2.0.8 and 2.2.1. For a host of 200 000 elements result is clearly not in favor of the new version. If the version 2.0.8 I get the result for ~ 30 seconds, the new release download page does not fit in 10 minutes. Obviously, this part of the interface needs further optimization, the query was closed prematurely. Using this part in new version of frontend absolutelly unusable for me.

Comment by Alexander Vladishev [ 2013 Dec 16 ]

Successfully tested!

Comment by Eduards Samersovs (Inactive) [ 2013 Dec 16 ]

Fixed in versions 2.3.0 (trunk) r.41027, 2.2.2rc1 r.41028

Comment by Oleksii Zagorskyi [ 2014 Jan 30 ]

Current changes caused a regression:
If in item name we use positional macro $1-$9 then it will NOT be resolved to item key parameter on simple graph title.
REOPENED

added: the same if a key parameter contains a usermacro. In 2.2.1 it was ok, currently it's not for the graph title.

Eduards Fixed in development branch svn://svn.zabbix.com/branches/dev/ZBX-6296

jelisejev CLOSED.

Comment by Pavels Jelisejevs (Inactive) [ 2014 Feb 04 ]

TESTED.

Comment by Pavels Jelisejevs (Inactive) [ 2014 Feb 04 ]

Fixed in 2.2.2rc2 r42243 and 2.3 r42244.

CLOSED.

Comment by Janis Jansons [ 2014 Mar 13 ]

The Latest Data is very slow using Zabbix 2.2.2. Was great on 2.0.x but now it's almost unusable.

Comment by David [ 2014 May 01 ]

Still seeing performance issues on the Latest Data page in 2.2.3.

Comment by Bruce Reed [ 2014 May 06 ]

Same here. Problem still exists for me in 2.2.3. Adding 20 new hosts via VMware discovery pushed me over the edge again where the latest data page never appears.

Comment by richlv [ 2014 Jun 20 ]

ZBX-7373 aims to improve it further

Comment by Raimo [ 2014 Jul 18 ]

Latest Data is still unusable here on 2.2.4 on a System with 669 hosts and 43813 items with filter [group all/host all], page doesn't even appear.

Comment by dimir [ 2018 Nov 22 ]

Solution proposed here.

Generated at Fri Mar 29 12:18:21 EET 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.