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

Use BRIN index and partial indexing on history tables

    XMLWordPrintable

Details

    • New Feature Request
    • Status: Open
    • Trivial
    • Resolution: Unresolved
    • 2.2.15, 3.0.5, 3.2.1
    • None
    • Server (S)
    • PostgreSQL 9.5+

    Description

      With PostgreSQL 9.5 a new kind of index was introduced, block range index.

      BRIN indexes are a fraction (2-5% ) of normal BTree indexes, and only store min/max range data for a set of data/blocks.

      On a naturally time-ordered table ( history, history_uint, etc) these indexes are very efficient, and can serve almost all queries.

      Except!

      Unbounded Lastvalue (limit=0) or sorted such (Select itemid, clock from history where itemid=1, order by clock desc, limit 1 offset 0) , will require parsing the entire table to get the data.

      WIth the default ZBX_HISTORY_PERIOD=86400, an partial index on history* tables that covers "clock > now()-86400". can be created, which will efficiently cover lookups that request data with a limit.

      More writeup on this approach is done here:
      https://www.modio.se/optimizing-disk-usage-of-zabbix-and-postgresql.html

      Attachments

        Activity

          People

            Unassigned Unassigned
            spider D.Spindel Ljungmark
            Votes:
            3 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated: