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

Long runnig queries from frontend on history tables + High CPU usage on DB host same time

    XMLWordPrintable

Details

    • Team C
    • Sprint 80 (Sep 2021), Sprint 81 (Oct 2021), Sprint 82 (Nov 2021)
    • 2

    Description

      Frontend can generate queries like
      SELECT itemid FROM history* WHERE (itemid IN (........)) AND clock>unixtime GROUP BY itemid;
      We have index for historical tables on (itemid,clock). So normally we have:

      Bitmap Index Scan on _hyper_*_chunk_history_str_1  (cost=0.00..2307.05 rows=95986 width=0) (actual time=14.939..14.940 rows=92213 loops=1)
      Index Cond: ((itemid = ANY ('{....,...,....}'::bigint[])) AND (clock > XXXX))
      

      i.e. postgres splits a set of itemids and performs parallel scanning by index (itemid,clock)

      Starting from a certain amount of itemid in condition postgres stops using this index and uses Index Cond: (clock > XXXXX) and [already in parallel] Filter: ((itemid = ANY (....)

      It works much longer. ~2sec with index (itemid,clock) against ~170sec with index (clock)

      As solution options:
      1.tuning postgres for forced use of the index (SET enable_seqscan = OFF)
      2. to split requests into several

      Attachments

        Activity

          People

            sasha Alexander Vladishev
            elina.kuzyutkina Elina Kuzyutkina
            Votes:
            4 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated: