Uploaded image for project: 'ZABBIX FEATURE REQUESTS'
  2. ZBXNEXT-5263

Database query latest history data very slow



      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')); 
       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-> 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');
      (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


      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?


      Best regards,



      PS: IOPs spike when I do those queries, so I'm even maxing out the IOPs of the hard drives.




            • Assignee:
              zabbix.dev Zabbix Development Team
              fefa2k Aaron
            • Votes:
              7 Vote for this issue
              7 Start watching this issue


              • Created: