-
Change Request
-
Resolution: Unresolved
-
Major
-
None
-
None
I'm querying the database directly to gather some information on the latest history data directly from a Grafana dashboard. It works dandy but the queries take a very long time to finish.
A simple query to gather the latest history item takes about 6 seconds on a 2740MB table (history_uint).
Here is the query (very simple one, the ones I'm doing require joins, that's when it get's messy):
zabbix=> select * from history_uint order by history_uint.clock DESC LIMIT 1; itemid | clock | value | ns --------+------------+-------+---------- 188644 | 1553786344 | 1 | 94740072 (1 row) Time: 6810,381 ms (00:06,810)
Here is the table size:
zabbix=> SELECT pg_size_pretty (pg_relation_size('history_uint'));
pg_size_pretty
----------------
2740 MB
(1 row)
One of the queries I'm doing to get the latest data on several items:
zabbix=> SELECT DISTINCT COUNT(items.name) zabbix-> FROM history_uint zabbix-> JOIN (SELECT zabbix(> history_uint.itemid, zabbix(> max(history_uint.clock) as max_clock zabbix(> FROM history_uint WHERE history_uint.clock > extract(epoch from now()- INTERVAL '6 MINUTES') zabbix(> GROUP BY history_uint.itemid) subselect_history_max_clock zabbix-> ON (history_uint.itemid = subselect_history_max_clock.itemid) AND (history_uint.clock = subselect_history_max_clock.max_clock) zabbix-> zabbix-> JOIN items ON items.itemid = subselect_history_max_clock.itemid zabbix-> JOIN hosts ON items.hostid = hosts.hostid zabbix-> JOIN hosts_groups ON hosts.hostid = hosts_groups.hostid zabbix-> JOIN hstgrp ON hosts_groups.groupid = hstgrp.groupid zabbix-> zabbix-> WHERE (hstgrp.name = 'Amadores-Clusters') zabbix-> AND (items.name LIKE '%AP Status%') zabbix-> AND (hosts.host LIKE '%VC%') zabbix-> AND history_uint.clock > extract(epoch from now()- INTERVAL '6 MINUTES'); count ------- 469 (1 row) Time: 33019,439 ms (00:33,019)
This is on a server idling at the moment. If multiple users start using Grafana to check the dashboards it can get very messy very fast.
What I found:
- There is no entry on the database that relates the history latest data besides the highest clock which isn't very efficient as it's not even pre-sorted
- Other users have found this same issue
- One of those users (dimir) came up with a solution and I think the fix is clean and simple
The fix:
It's stated here -> https://support.zabbix.com/browse/ZBX-13193?focusedCommentId=304714&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-304714
It would create 5 tables with the latest data gathered on each item/history* table, those tables wouldn't grow over time as it's as big as items has the history tables, so it's a very tidy and fast solution.
Could be this implemented on Zabbix?
Best regards,
Aarón
PS: IOPs spike when I do those queries, so I'm even maxing out the IOPs of the hard drives.
