Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-21541

Monitoring/Hosts very slow loading for user role. Caused by query fetching graph count

    XMLWordPrintable

Details

    • Problem report
    • Status: Confirmed
    • Major
    • Resolution: Unresolved
    • 6.0.7
    • None
    • Frontend (F)
    • Ubuntu 20.04, Postgres 14.5, nginx 1.20.1
      Zabbix Sever VM: 4 cores, 16GB
      DB VM: 4 cores, 8GB
      Hosts: 2133
      Items: 138196
      NVPS: 975

    Description

      Steps to reproduce:

      1. Upgraded from 5.0.x to 6.0.7
      2. Create user with role user 
      3. Go to Monitoring -> Hosts

      Result

      The Hosts page takes a long time to load (~30s). The debug shows this is caused by one SQL query, that fetches the number of graphs for each of the hosts (the Graphs column)

      This is not an issue for super user accounts.

      Query:

      SQL (30.400495): SELECT COUNT(DISTINCT g.graphid) AS rowscount,i.hostid FROM graphs g,graphs_items gi,items i WHERE NOT EXISTS (SELECT NULL FROM graphs_items gi,items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid IN (11,14,18) WHERE g.graphid=gi.graphid AND gi.itemid=i.itemid AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MAX(permission)<'2' OR MIN(permission) IS NULL OR MIN(permission)=0) AND i.hostid IN (10603,10604,10607,10610,10838,10849,10850,11037,11049,11050,11067,11076,11491,11716,11717,11718,11719,11721,11723,11724,11725,11726,11741,11772,12150,14406,14407,14408,14409,14410,14411,14412,14413,14414,14415,14416,14417,14418,14421,14422,14423,14424,14425,14426,15837,15951,16037,16038,16156,16161) AND gi.graphid=g.graphid AND i.itemid=gi.itemid AND g.flags IN (0,4) GROUP BY i.hostid

       

      Expected:

      The load time for the query should be shorter

      Attachments

        Issue Links

          Activity

            People

              zabbix.dev Zabbix Development Team
              p.wrzecionowski Piotr Wrzecionowski
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated: