Zabbix 2.1.4 - Screens slow for readonly users when using graphprotypes in screens
Curious if anyone else is seeing this behavior in Zabbix 2.1.4 or is it something on my side. 
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/domain.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'.
')'.
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/domain.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'.
')'.
Code:
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) |
+----+--------------------+-------+-------+-------------------------------+----------------+---------+--------------------+-------+----------------------------------------------------+
