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

Slow latest data section due to an inefficient query

    XMLWordPrintable

    Details

    • Type: Incident report
    • Status: Closed
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 2.2.2
    • Fix Version/s: None
    • Component/s: Frontend (F)
    • Environment:
      Zabbix 2.2.2, MySQL 5.6 (history and trend tables partitioned by week), one year history, DB size ~70GB (~800M rows)

      Description

      Latest data page in Zabbix frontend uses inefficient query to get the latest value for each item. It shows that almost 100K rows are examined to get the latest 2 values for this item:

      mysql>explain extended SELECT * FROM history h WHERE h.itemid='475' ORDER BY h.clock DESC LIMIT 2 OFFSET 0;
      +----+-------------+-------+------+---------------+-----------+---------+-------+-------+----------+-------------+
      | id | select_type | table | type | possible_keys | key       | key_len | ref   | rows  | filtered | Extra       |
      +----+-------------+-------+------+---------------+-----------+---------+-------+-------+----------+-------------+
      |  1 | SIMPLE      | h     | ref  | history_1     | history_1 | 8       | const | 99687 |   100.00 | Using where |
      +----+-------------+-------+------+---------------+-----------+---------+-------+-------+----------+-------------+
      1 row in set, 1 warning (0.04 sec)
      

      This query is executed for each item on that page and it is resulting in slow page loading when the host has hundreds of items.

      The frontend debug mode shows:

      ******************** Script profiler ********************
      Total time: 113.196293
      Total SQL time: 112.707198
      SQL count: 288 (selects: 223 | executes: 65)
      Peak memory usage: 21M
      Memory limit: 512M
      

      This issue is amplified while using partitioned MySQL database with long history devided over many partitions and database engine has to go through all/most of them to gather entire history for that item.

      I suggest using clock as an additional filter for that query to limit the amount of rows to be examined.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              arli Arli
              Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: