ZABBIX BUGS AND ISSUES
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-6296

Monitoring > Latest Data is slow on large setups

    Details

      Description

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

      1. zabbix_latest_data_query_zabbix_22rc1.txt
        3.64 MB
        Dimitri Bellini
      2. zabbix_latest_data_query.txt
        194 kB
        Dimitri Bellini

        Activity

        Toms created issue -
        Toms made changes -
        Field Original Value New Value
        Issue Type New Feature [ 2 ] Improvement [ 4 ]
        Hide
        vairel added a comment - - edited

        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();

        Show
        vairel added a comment - - edited 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();
        Alexei Vladishev made changes -
        Labels performance
        Zabbix ID NA RTD
        Hide
        Alexander Vladishev added a comment -

        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
        
        Show
        Alexander Vladishev added a comment - 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
        Alexander Vladishev made changes -
        Description On large setups Latest Data page is slow. There is at least one case with page generation in ~17 seconds;

        Initial spec: https://zabbix.org/wiki/Docs/specs/ZBX-869
        On large setups Latest Data page is slow. There is at least one case with page generation in ~17 seconds;
        Eduards Samersovs (Inactive) made changes -
        Assignee Eduards Samersovs [ eduards ]
        Hide
        Dimitri Bellini added a comment -

        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,

        Show
        Dimitri Bellini added a comment - 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,
        Hide
        Dimitri Bellini added a comment - - edited

        Attached:File with Zabbix Latest Data slow Query

        Show
        Dimitri Bellini added a comment - - edited Attached:File with Zabbix Latest Data slow Query
        Dimitri Bellini made changes -
        Attachment zabbix_latest_data_query.txt [ 25326 ]
        Oleksiy Zagorskyi made changes -
        Link This issue is duplicated by ZBXNEXT-2019 [ ZBXNEXT-2019 ]
        Oleksiy Zagorskyi made changes -
        Link This issue is duplicated by ZBXNEXT-2019 [ ZBXNEXT-2019 ]
        Oleksiy Zagorskyi made changes -
        Link This issue is duplicated by ZBXNEXT-2013 [ ZBXNEXT-2013 ]
        Oleksiy Zagorskyi made changes -
        Link This issue is duplicated by RBA-47 [ RBA-47 ]
        Oleksiy Zagorskyi made changes -
        Labels performance latestdata performance
        Oleksiy Zagorskyi made changes -
        Link This issue is duplicated by ANT-6 [ ANT-6 ]
        Alexei Vladishev made changes -
        Fix Version/s 2.2.1 [ 12002 ]
        Hide
        Eduards Samersovs (Inactive) added a comment -

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

        Show
        Eduards Samersovs (Inactive) added a comment - Fixed in development branch svn://svn.zabbix.com/branches/dev/ZBX-6296
        Eduards Samersovs (Inactive) made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Assignee Eduards Samersovs [ eduards ] Alexander Vladishev [ sasha ]
        Fix Version/s 2.2.1rc1 [ 12001 ]
        Fix Version/s 2.3.0 (trunk) [ 12006 ]
        Fix Version/s 2.2.1 [ 12002 ]
        Resolution Fixed [ 1 ]
        Hide
        Dimitri Bellini added a comment -

        Update file after install zabbix 2.2rc1

        Show
        Dimitri Bellini added a comment - Update file after install zabbix 2.2rc1
        Dimitri Bellini made changes -
        Hide
        Dimitri Bellini added a comment -

        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.

        Show
        Dimitri Bellini added a comment - 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.
        Hide
        Oleksiy Zagorskyi added a comment -

        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&

        Show
        Oleksiy Zagorskyi added a comment - 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&
        Hide
        Oleksiy Zagorskyi added a comment -

        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

        Show
        Oleksiy Zagorskyi added a comment - 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
        Hide
        Dimitri Bellini added a comment -

        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.

        Show
        Dimitri Bellini added a comment - 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.
        Hide
        Dimitri Bellini added a comment - - edited

        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

        Show
        Dimitri Bellini added a comment - - edited 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
        Hide
        Dimitri Bellini added a comment - - edited

        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
        
        Show
        Dimitri Bellini added a comment - - edited 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
        Hide
        Alexander Vladishev added a comment -

        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!

        Show
        Alexander Vladishev added a comment - 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!
        Hide
        Vlad Vnukovsky added a comment -

        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.

        Show
        Vlad Vnukovsky added a comment - 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.
        Hide
        Alexander Vladishev added a comment -

        Successfully tested!

        Show
        Alexander Vladishev added a comment - Successfully tested!
        Alexander Vladishev made changes -
        Status Resolved [ 5 ] Tested [ 10002 ]
        Assignee Alexander Vladishev [ sasha ] Eduards Samersovs [ eduards ]
        Hide
        Eduards Samersovs (Inactive) added a comment -

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

        Show
        Eduards Samersovs (Inactive) added a comment - Fixed in versions 2.3.0 (trunk) r.41027, 2.2.2rc1 r.41028
        Eduards Samersovs (Inactive) made changes -
        Status Tested [ 10002 ] Closed [ 6 ]
        Assignee Eduards Samersovs [ eduards ]
        Fix Version/s 2.2.2rc1 [ 12011 ]
        Fix Version/s 2.2.1rc1 [ 12001 ]
        Oleksiy Zagorskyi made changes -
        Link This issue is duplicated by ZBXNEXT-2013 [ ZBXNEXT-2013 ]
        Hide
        Oleksiy Zagorskyi added a comment - - edited

        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 Samersovs Fixed in development branch svn://svn.zabbix.com/branches/dev/ZBX-6296

        Pavels Jelisejevs CLOSED.

        Show
        Oleksiy Zagorskyi added a comment - - edited 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 Samersovs Fixed in development branch svn://svn.zabbix.com/branches/dev/ZBX-6296 Pavels Jelisejevs CLOSED.
        Oleksiy Zagorskyi made changes -
        Resolution Fixed [ 1 ]
        Status Closed [ 6 ] Reopened [ 4 ]
        Assignee Eduards Samersovs [ eduards ]
        Oleksiy Zagorskyi made changes -
        Link This issue is duplicated by RBA-50 [ RBA-50 ]
        Eduards Samersovs (Inactive) made changes -
        Status Reopened [ 4 ] Resolved [ 5 ]
        Resolution Fixed [ 1 ]
        Pavels Jelisejevs (Inactive) made changes -
        Assignee Eduards Samersovs [ eduards ] Pavels Jelisejevs [ jelisejev ]
        Hide
        Pavels Jelisejevs (Inactive) added a comment -

        TESTED.

        Show
        Pavels Jelisejevs (Inactive) added a comment - TESTED.
        Pavels Jelisejevs (Inactive) made changes -
        Status Resolved [ 5 ] Tested [ 10002 ]
        Assignee Pavels Jelisejevs [ jelisejev ] Eduards Samersovs [ eduards ]
        Hide
        Pavels Jelisejevs (Inactive) added a comment - - edited

        Fixed in 2.2.2rc2 r42243 and 2.3 r42244.

        CLOSED.

        Show
        Pavels Jelisejevs (Inactive) added a comment - - edited Fixed in 2.2.2rc2 r42243 and 2.3 r42244. CLOSED.
        Pavels Jelisejevs (Inactive) made changes -
        Status Tested [ 10002 ] Closed [ 6 ]
        Hide
        Janis Jansons added a comment -

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

        Show
        Janis Jansons added a comment - The Latest Data is very slow using Zabbix 2.2.2. Was great on 2.0.x but now it's almost unusable.
        Hide
        David added a comment -

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

        Show
        David added a comment - Still seeing performance issues on the Latest Data page in 2.2.3.
        Hide
        Bruce Reed added a comment -

        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.

        Show
        Bruce Reed added a comment - 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.
        Hide
        richlv added a comment -

        ZBX-7373 aims to improve it further

        Show
        richlv added a comment - ZBX-7373 aims to improve it further
        Hide
        Raimo added a comment -

        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.

        Show
        Raimo added a comment - 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.
        Alexei Vladishev made changes -
        Workflow Zabbix workflow [ 28061 ] Zabbix workflow - new [ 46970 ]
        Alexander Vladishev made changes -
        Zabbix ID RTD
        Alexander Vladishev made changes -
        Workflow Zabbix workflow - new [ 46970 ] Copy of Zabbix workflow - new [ 67142 ]
        Alexander Vladishev made changes -
        Workflow Copy of Zabbix workflow - new [ 67142 ] Zabbix workflow - new [ 82362 ]
        Gatis Rumbens made changes -
        Issue Type Change Request [ 4 ] Incident report [ 10110 ]
        Assignee Eduards Samersovs [ eduards ]
        Transition Time In Source Status Execution Times Last Executer Last Execution Date
        Open Open Resolved Resolved
        285d 21h 21m 1 Eduards Samersovs (Inactive) 2013 Dec 03 14:40
        Closed Closed Reopened Reopened
        44d 21h 11m 1 Oleksiy Zagorskyi 2014 Jan 30 11:25
        Reopened Reopened Resolved Resolved
        23h 51m 1 Eduards Samersovs (Inactive) 2014 Jan 31 11:17
        Resolved Resolved Merging Merging
        17d 3h 51m 2 Pavels Jelisejevs (Inactive) 2014 Feb 04 16:02
        Merging Merging Closed Closed
        1h 48m 2 Pavels Jelisejevs (Inactive) 2014 Feb 04 17:24

          People

          • Assignee:
            Unassigned
            Reporter:
            Toms
          • Votes:
            2 Vote for this issue
            Watchers:
            9 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: