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

Database query latest history data very slow

    Details

      Description

      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

       

      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,

      Aarón

       

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

        Attachments

          Activity

            People

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

              Dates

              • Created:
                Updated: