-
Incident report
-
Resolution: Unresolved
-
Trivial
-
None
-
3.0.0alpha5
-
MySQL 5.7.10
PHP 7.01
NGINX 1.99
There is an performance issue when using MySQL if you go “Monitoring -> Latest Data” and bring up a host with items that do not have data points because they are unsupported or if data has not been populated for a while, the “Latest Data” page could take up to 60+ seconds to come up. This problem has been an issue since Zabbix 2.4 when last values where moved from the items table to the history tables. The problem creeps up when your history tables get large in MySQL and you go to latest data with unsupported items. The queries have the ORDER BY … DESC clause which causes a condition called “late row lookup” ( http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/ ). Basically the entire history table gets scanned. This was causing extremely long delays when view “Latest Data” in Zabbix which were getting worse as the history tables grew.
I fixed this issue by rewriting one the queries in the API for the history calls and ran some test. Time went from 70+ seconds down to 0.1 seconds when viewing “Latest Data” for one host that had unsupported items.
Before the modifications latest data stats to bring up history items for one host:
Total time: 73.846612
Total SQL time: 73.767173
SQL count: 173 (selects: 159 | executes: 14)
Peak memory usage: 8M
Memory limit: 5G
After the modifications latest data stats to bring up history items for the same host:
Total time: 0.156582
Total SQL time: 0.076732
SQL count: 173 (selects: 159 | executes: 14)
Peak memory usage: 6M
Memory limit: 5G
Changed include/classes/api/manager/CHistoryManager.php.
Original Code start line 40:
'SELECT *'. ' FROM '.self::getTableName($item['value_type']).' h'. ' WHERE h.itemid='.zbx_dbstr($item['itemid']). ($period ? ' AND h.clock>'.(time() - $period) : ''). ' ORDER BY h.clock DESC',
Modified to the following:
'SELECT *'. ' FROM ('. ' SELECT itemid, clock'. ' FROM '.self::getTableName($item['value_type']). ' WHERE itemid='.zbx_dbstr($item['itemid']). ($period ? ' AND clock>'.(time() - $period) : ''). ' ORDER BY clock DESC LIMIT '.$limit.' OFFSET 0'. ' ) o'. ' JOIN '.self::getTableName($item['value_type']).' h'. ' ON h.itemid = o.itemid '. ($period ? ' AND h.clock = o.clock' : ''). ' ORDER by h.clock DESC',
diff -ruN zabbix-original/frontends/php/include/classes/api/managers/CHistoryManager.php zabbix/frontends/php/include/classes/api/managers/CHistoryManager.php
--- zabbix-original/frontends/php/include/classes/api/managers/CHistoryManager.php 2015-11-22 22:48:54.311528062 -0500
+++ zabbix/frontends/php/include/classes/api/managers/CHistoryManager.php 2015-12-27 04:19:49.659064808 -0500
@@ -38,10 +38,17 @@
foreach ($items as $item) {
$values = DBfetchArray(DBselect(
'SELECT *'.
- ' FROM '.self::getTableName($item['value_type']).' h'.
- ' WHERE h.itemid='.zbx_dbstr($item['itemid']).
- ($period ? ' AND h.clock>'.(time() - $period) : '').
- ' ORDER BY h.clock DESC',
+ ' FROM ('.
+ ' SELECT itemid, clock'.
+ ' FROM '.self::getTableName($item['value_type']).
+ ' WHERE itemid='.zbx_dbstr($item['itemid']).
+ ($period ? ' AND clock>'.(time() - $period) : '').
+ ' ORDER BY clock DESC LIMIT '.$limit.' OFFSET 0'.
+ ' ) o'.
+ ' JOIN '.self::getTableName($item['value_type']).' h'.
+ ' ON h.itemid = o.itemid '.
+ ($period ? ' AND h.clock = o.clock' : '').
+ ' ORDER by h.clock DESC',
$limit
));
Note:
Late row lookups = A late row lookup requires that the engine looks up a row in a table only if it had not been filtered by the index. MySQL is not capable of doing this but this can be emulated. It improves the LIMIT performance and filtering efficiency on the indexed fields.