-
Incident report
-
Resolution: Unresolved
-
Trivial
-
None
-
6.4.11
-
None
Several database queries are very slow for large Zabbix installations on the Data collection -> Hosts page:
SELECT COUNT(*) AS rowscount,i.hostid FROM items i WHERE i.type<> 9 AND i.flags IN (0,4) AND i.hostid IN (<hostids>) GROUP BY i.hostid
SELECT COUNT(*) AS rowscount,i.hostid FROM items i WHERE i.flags=1 AND i.hostid IN (<hostids>) GROUP BY i.hostid
SELECT COUNT(DISTINCT t.triggerid) AS rowscount,i.hostid FROM triggers t,functions f, items WHERE i.hostid IN (<hostids>) AND f.triggerid=t.triggerid AND f.itemid=i.itemid AND t.flags IN (0,4) GROUP BY i.hostid
SELECT COUNT(*) AS rowscount,i.hostid FROM items i WHERE i.type<>9 AND i.flags IN (0,4) AND i.hostid IN (<hostids>) AND i.type=7 GROUP BY i.hostid
Steps to reproduce:
- Create many hosts with lots of items and triggers. In my case I have dozens of hosts with 20000 or more items each. Some hosts have 30000 and even 50000 items
- Try to open up Data collection -> Hosts and it will take well over a minute to load the display with these hosts
Expected:
It would be better for this page to load much more quickly.
Would it be possible for one of the backend processes, i.e. Configuration Syncer to cache this information locally (it probably already is?) and occasionally sync the counts to a separate table? The queries above are very slow with the GROUP BY. The counts are useful, but with over 5 million items across all hosts (and some hosts have thousands) I'd prefer to not see the count and just have the links for Items, Triggers, Graphs, etc.