-
Incident report
-
Resolution: Fixed
-
Major
-
2.1.4
Screens are very slow for read-only users when using graphprotypes in screen elements. The problem started to occur after ZBX-6678 was integrated into Trunk.
Debug shows long SQL query when viewing screens with graphprototypes as an element.
SQL (89.410123): SELECT g.graphid FROM graphs g WHERE NOT EXISTS (SELECT NULL FROM graphs_items gi,items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid IN ('13','14','17') WHERE (g.graphid=gi.graphid AND gi.itemid=i.itemid OR g.ymin_type=2 AND g.ymin_itemid=i.itemid OR g.ymax_type=2 AND g.ymax_itemid=i.itemid) AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MAX(permission)<2 OR MIN(permission) IS NULL OR MIN(permission)=0) AND g.graphid IN ('524','525','661') AND g.flags IN ('0','4')
screens.php:146 ? CAPIObject->get() ? CAPIObject->__call() ? czbxrpc::call() ? czbxrpc::callAPI() ? call_user_func() ? CScreen->get() ? CGraph->get() ? DBselect() in /var/www/html/zabbix.cbeyond.net/api/classes/CGraph.php:270
Looks like the addition of the following code from ZBX-6678 is causing the slow query results:
In CGraph.php:
' WHERE (g.graphid=gi.graphid'.
' AND gi.itemid=i.itemid'.
' OR g.ymin_type='.GRAPH_YAXIS_TYPE_ITEM_VALUE.
' AND g.ymin_itemid=i.itemid'.
' OR g.ymax_type='.GRAPH_YAXIS_TYPE_ITEM_VALUE.
' AND g.ymax_itemid=i.itemid'.
')'.
mysql> explain SELECT g.graphid FROM graphs g WHERE NOT EXISTS (SELECT NULL FROM graphs_items gi,items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid IN ('13','14','17') WHERE (g.graphid=gi.graphid AND gi.itemid=i.itemid OR g.ymin_type=2 AND g.ymin_itemid=i.itemid OR g.ymax_type=2 AND g.ymax_itemid=i.itemid) AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MAX(permission)<2 OR MIN(permission) IS NULL OR MIN(permission)=0) AND g.graphid IN ('524','525','661') AND g.flags IN ('0','4');
-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | PRIMARY | g | range | PRIMARY | PRIMARY | 8 | NULL | 3 | Using where |
| 2 | DEPENDENT SUBQUERY | i | index | PRIMARY,items_1 | items_1 | 1030 | NULL | 31909 | Using index; Using temporary; Using filesort |
| 2 | DEPENDENT SUBQUERY | hgg | ref | hosts_groups_1 | hosts_groups_1 | 8 | zabbix.i.hostid | 1 | Using index |
| 2 | DEPENDENT SUBQUERY | r | ref | rights_2,rights_1 | rights_2 | 8 | zabbix.hgg.groupid | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | gi | ALL | graphs_items_1,graphs_items_2 | NULL | NULL | NULL | 1533 | Using where; Using join buffer (Block Nested Loop) |
-----------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set