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

High-load Frontend Query

    XMLWordPrintable

Details

    • Problem report
    • Status: Open
    • Trivial
    • Resolution: Unresolved
    • 5.0.11
    • None
    • Frontend (F)
    • None

    Description

      On huge non-partitioned history* tables query like

      SELECT itemid,COUNT(*) AS count,AVG(value) AS avg,MIN(value) AS min,MAX(value) AS max,round(1552* MOD(CAST(clock AS UNSIGNED)+3314,8986)/(8986),0) AS i,MAX(clock) AS clock FROM history_uint WHERE itemid='2122261' AND clock>='1620028710' AND clock<='1620037696' GROUP BY itemid,round(1552* MOD(CAST(clock AS UNSIGNED)+3314,8986)/(8986),0);
      

      from function
      https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/ui/include/classes/api/managers/CHistoryManager.php#920

      running for a  more than 30 seconds and create a huge load on database

      I guess, the root reason is max(), min() and avg() functions usage

      I guess too, that partitioning could help here, because table will scanning for one or several partitions, not a full table, but

      Is it possible to optimize this frontend SQL query some way?

       

       

      Attachments

        Activity

          People

            zabbix.dev Zabbix Development Team
            igorbach Igor Gorbach
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated: