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

Slow queries when checking permissions

    XMLWordPrintable

Details

    • Problem report
    • Status: Reopened
    • Major
    • Resolution: Unresolved
    • 2.2.2rc1
    • None
    • API (A)
    • OS: FreeBSD 9.2
      Storage: 1TB RAID10, RAM: 20GB, CPU: 8 cores. VMware virtual appliance
      Hosts: 2790, Items: 162450, Frontend users: 126
    • Sprint 27, Sprint 28, Sprint 29, Sprint 30, Sprint 31, Sprint 32, Sprint 33, Sprint 34, Sprint 35, Sprint 36, Sprint 37, Sprint 38, Sprint 39, Sprint 40, Sprint 41, Sprint 42, Sprint 43, Sprint 44, Sprint 45

    Description

      There was problem similar to ZBX-4789. 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.

      Hosts: 2790,  Items: 162450, Frontend users: 126
      Table sizes:
      host_groups: 6538
      rights: 209
      usrgrp: 17
      graphs: 24030
      hosts: 3784
      

      Attachments

        Issue Links

          Activity

            People

              zabbix.dev Zabbix Development Team
              zhizhin_av Aleksandr Zhizhin
              Votes:
              15 Vote for this issue
              Watchers:
              24 Start watching this issue

              Dates

                Created:
                Updated: