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

Heavy query in latest data

    XMLWordPrintable

Details

    • Problem report
    • Status: Closed
    • Major
    • Resolution: Duplicate
    • 5.0.2
    • None
    • Frontend (F)
    • None

    Description

      Steps to reproduce:

      1. Upgrade Zabbix to 5.0.2 version with PostgreSQL 11 and TimescaleDB extension with big history* tables
      2. Check latest data

      Result:

      1. Getting heavy query and slow web response

      Rootcause is SQL query

      SELECT itemid FROM history WHERE itemid IN (itemid1,itemid2,itemid3,...) GROUP BY itemid HAVING MAX(clock)>$period

      Query generated by getItemsHavingValuesFromSql function from CHistoryManager.php

      Suggested alternative:

      with cleaned as(select itemid from history where clock >$period) select distinct itemid from cleaned where itemid in((itemid1,itemid2,itemid3,...);

      Rollback to 5.0.1 Frontend version have fixed the issue

       

      Execution plan for

      current SQL

      and

      Suggested SQL

       

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              igorbach Igor Gorbach
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: