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

Slow queries when checking permissions

    XMLWordPrintable

Details

    • Team A
    • 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

    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

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

        Issue Links

          Activity

            People

              rlataria Roberts Lataria
              zhizhin_av Aleksandr Zhizhin
              Votes:
              16 Vote for this issue
              Watchers:
              36 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: