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

Slow queries when checking permissions

    XMLWordPrintable

    Details

    • Type: Incident report
    • Status: Closed
    • Priority: Critical
    • Resolution: Duplicate
    • Affects Version/s: 2.2.6, 2.4.1
    • Fix Version/s: None
    • Component/s: Frontend (F)
    • Labels:
    • Environment:
      OS: FreeBSD 9.2
      Storage: 1TB RAID10, RAM: 20GB, CPU: 8 cores. VMware virtual appliance
      Hosts: 4787, Items: 257416, Frontend users: 241

      Description

      When all users is super-admins frontend works quickly. When one of them login as non Super-Admin, delay up to 1 minute occurs on almost all pages.
      copy past from debug(open charts.php):
      SQL (63.758301): SELECT DISTINCT g.graphid,g.name FROM graphs g,graphs_items gi,items i,hosts h 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 ('7','11') WHERE g.graphid=gi.graphid AND gi.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 NOT EXISTS (SELECT NULL FROM items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid IN ('7','11') WHERE g.ymin_type=2 AND g.ymin_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 NOT EXISTS (SELECT NULL FROM items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid IN ('7','11') WHERE 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 i.itemid=gi.itemid AND g.graphid=gi.graphid AND h.hostid=i.hostid AND h.status<>3 AND g.flags IN ('0','4')
      charts.php:82 ? CPageFilter->_construct() ? CPageFilter->_initGraphs() ? CAPIObject->get() ? CAPIObject->_call() ? czbxrpc::call() ? czbxrpc::callAPI() ? call_user_func() ? CGraph->get() ? DBselect() in /usr/local/www/zabbix2/api/classes/CGraph.php:301

      The situation worsens when some users set to non Super-Admin and get access frontend - database dies: begin multiple locks, data loss from pollers, failures on graphs etc. Current workaround is set to all users Super-Admin attribute.

      <quote>
      David Szanto added a comment - 2014 Aug 01 13:58
      One more thing I forgot to mention:
      The following query got the exact same result in less than 0.3 seconds:
      SELECT g.*
      FROM groups g
      WHERE g.groupid IN (
      SELECT hg.groupid FROM hosts_groups hg WHERE hg.hostid IN (
      SELECT h.hostid FROM hosts h WHERE h.status IN (1,0)
      )
      )
      AND g.groupid NOT IN (
      SELECT r.id FROM rights r WHERE permission < 3 AND groupid IN (
      SELECT ug.id FROM users_groups ug WHERE userid=100100000000013
      )
      )
      AND g.groupid IN (
      SELECT rr.id FROM rights r WHERE permission >= 3
      );
      Could this query modification be implemented in the Zabbix frontend code (I'm currently using version 2.0.3)?
      I could check and restructure more queries for optimization. If I can be of help, please let me know.
      Cheers!
      </quote>

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              zhizhin_av Aleksandr Zhizhin
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: