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
Time: 6810,381 ms (00:06,810)
Here is the table size:
zabbix=> SELECT pg_size_pretty (pg_relation_size('history_uint'));
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(> 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-> 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-> 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');
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
It's stated here -> https://support.zabbix.com/browse/ZBX-13193?focusedCommentId=304714&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-304714
and here -> https://support.zabbix.com/browse/ZBX-13193?focusedCommentId=332045&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-332045
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?
PS: IOPs spike when I do those queries, so I'm even maxing out the IOPs of the hard drives.