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.txt
        194 kB
        Dimitri Bellini
      2. zabbix_latest_data_query_zabbix_22rc1.txt
        3.64 MB
        Dimitri Bellini

        Activity

        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();
        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
        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
        Hide
        Eduards Samersovs added a comment -

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

        Show
        Eduards Samersovs added a comment - Fixed in development branch svn://svn.zabbix.com/branches/dev/ZBX-6296
        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
        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!
        Hide
        Eduards Samersovs added a comment -

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

        Show
        Eduards Samersovs added a comment - Fixed in versions 2.3.0 (trunk) r.41027, 2.2.2rc1 r.41028
        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.
        Hide
        Pavels Jelisejevs added a comment -

        TESTED.

        Show
        Pavels Jelisejevs added a comment - TESTED.
        Hide
        Pavels Jelisejevs added a comment - - edited

        Fixed in 2.2.2rc2 r42243 and 2.3 r42244.

        CLOSED.

        Show
        Pavels Jelisejevs added a comment - - edited Fixed in 2.2.2rc2 r42243 and 2.3 r42244. CLOSED.
        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.

          People

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

            Dates

            • Created:
              Updated:
              Resolved: