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

Slow queries when checking permissions

XMLWordPrintable

    • 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, Sprint 87 (Apr 2022), Sprint 88 (May 2022), Sprint 89 (Jun 2022), Sprint 90 (Jul 2022), Sprint 91 (Aug 2022)
    • 2

      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
      

        1. zbx-7706-report.pdf
          68 kB
        2. superadmin 5.0.8 problems page.txt
          275 kB
        3. superadmin.txt
          392 kB
        4. Selection_113.png
          Selection_113.png
          46 kB
        5. Selection_112.png
          Selection_112.png
          115 kB
        6. Selection_105.png
          Selection_105.png
          47 kB
        7. Selection_066.png
          Selection_066.png
          30 kB
        8. Selection_059.png
          Selection_059.png
          26 kB
        9. Selection_058.png
          Selection_058.png
          50 kB
        10. Selection_054.png
          Selection_054.png
          31 kB
        11. Selection_051.png
          Selection_051.png
          35 kB
        12. Selection_049.png
          Selection_049.png
          47 kB
        13. regularuser.txt
          4.61 MB
        14. non-superadmin 5.0.8 problems page-1.txt
          196 kB
        15. moving-graph-permission-check-as-subquery-in-from-clause-v2.patch
          3 kB
        16. moving-graph-permission-check-as-subquery-in-from-clause-trunk-v3.patch
          3 kB
        17. moving-graph-permission-check-as-subquery-in-from-clause-3.0-v3.patch
          4 kB
        18. moving-graph-permission-check-as-subquery-in-from-clause.patch
          3 kB
        19. limit-subquery-2-and-3.patch
          1 kB
        20. join-subquery-2-and-3-into-one-subquery.patch
          2 kB
        21. inacc.png
          inacc.png
          3 kB
        22. image-2022-06-01-15-56-13-779.png
          image-2022-06-01-15-56-13-779.png
          3 kB
        23. image-2022-06-01-15-55-48-132.png
          image-2022-06-01-15-55-48-132.png
          25 kB
        24. image-2022-06-01-15-55-25-171.png
          image-2022-06-01-15-55-25-171.png
          25 kB
        25. image-2022-06-01-10-24-37-851.png
          image-2022-06-01-10-24-37-851.png
          23 kB
        26. image-2022-06-01-10-22-52-306.png
          image-2022-06-01-10-22-52-306.png
          23 kB
        27. image-2022-06-01-10-15-21-342.png
          image-2022-06-01-10-15-21-342.png
          6 kB
        28. image-2022-06-01-10-13-43-565.png
          image-2022-06-01-10-13-43-565.png
          29 kB
        29. image-2021-02-08-11-51-40-035.png
          image-2021-02-08-11-51-40-035.png
          490 kB
        30. group-selection-in-main-query.patch
          2 kB
        31. explain-1.txt
          80 kB
        32. explain.txt
          5 kB
        33. explain_query_results.txt
          249 kB
        34. dump.txt
          263 kB
        35. dropdown-first-entry.png
          dropdown-first-entry.png
          59 kB

            rlataria Roberts Lataria (Inactive)
            zhizhin_av Aleksandr Zhizhin
            Team A
            Votes:
            16 Vote for this issue
            Watchers:
            37 Start watching this issue

              Created:
              Updated:
              Resolved: