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

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


    • Icon: Change Request Change Request
    • Resolution: Unresolved
    • Icon: Major Major
    • None
    • 5.0.14, 6.2.0
    • CentOS: v7.9
      Zabbix: v5.0.14
      DB: PGSQL (postgresql12-server-12.7-1PGDG.rhel7.x86_64) + TSDB (timescaledb-2-postgresql-12-2.3.1-0.el7.x86_64)
    • Sprint 80 (Sep 2021), Sprint 81 (Oct 2021), Sprint 82 (Nov 2021), Sprint 83 (Dec 2021), Sprint 84 (Jan 2022), Sprint 85 (Feb 2022), Sprint 86 (Mar 2022), Sprint 87 (Apr 2022), Sprint 88 (May 2022), Sprint 89 (Jun 2022), Sprint 90 (Jul 2022), Sprint 91 (Aug 2022)

      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

            zabbix.dev Zabbix Development Team
            elina.kuzyutkina Elina Kuzyutkina (Inactive)
            9 Vote for this issue
            8 Start watching this issue