-
Incident report
-
Resolution: Duplicate
-
Major
-
None
-
2.2.2
-
Zabbix 2.2.2, MySQL 5.6 (history and trend tables partitioned by week), one year history, DB size ~70GB (~800M rows)
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.
- duplicates
-
-
- Closed
-