Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-24435

Slow query in latest data when more than 950 items selected

XMLWordPrintable

    • Icon: Incident report Incident report
    • Resolution: Unresolved
    • Icon: Minor Minor
    • None
    • 6.0.29
    • Frontend (F)
    • None
    • PostgreSQL 15.6
      timescaledb 2.13.1
    • Support backlog

      When loading latest data using sub-filter "With data" it works well if amount of items is bellow 950.
      We tested with ~900 items and the page loads in 1-2 seconds.
      With ~1200 items it initially timed out, but after php/web configuration is updated, it could load in 10-15 minutes.
      Main problem is query:

      LOG:  duration: 928896.744 ms  statement: SELECT DISTINCT itemid FROM history_uint WHERE (itemid IN (3898948,4980401,4980402 .......) OR itemid IN (5428917,5428919, ......)) AND clock>1714319213
      

      Query shortened here.
      We updated the query to exclude the "OR":

      SELECT DISTINCT itemid FROM history_uint WHERE itemid IN (3898948,4980401,4980402,4980403...) AND clock>1714319213
      

      And this executes almost instantly.

      This looks like a problem with the specific DB, but when we change the file:
      include/db.inc.php

      $MAX_NUM_IN = 2000;
      

      (increase form 950)
      Now the frontend also loads data very fast, 1-2 seconds for ~1700 items.

            zabbix.dev Zabbix Development Team
            zux Edgars Melveris
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated: