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

            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: