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

Zabbix 2.1.4 - Screens very slow for read-only users when using graphprotypes in screen elements

    XMLWordPrintable

    Details

    • Type: Incident report
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.1.4
    • Fix Version/s: 2.1.8
    • Component/s: Frontend (F)
    • Labels:

      Description

      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

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            palmertree Kenneth Palmertree
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: