Uploaded image for project: 'ZABBIX FEATURE REQUESTS'
  1. ZABBIX FEATURE REQUESTS
  2. ZBXNEXT-5878

Enhance permission checking/handling

XMLWordPrintable

    • Sprint 101 (Jun 2023), Sprint 102 (Jul 2023), Sprint 103 (Aug 2023), Sprint 104 (Sep 2023), Sprint 105 (Oct 2023), Sprint 106 (Nov 2023), Sprint 107 (Dec 2023), S2401
    • 10

      Current issues: ZBX-7706, ZBX-17302. Unfortunately with no proper solution, all such issues are workarounds.

      The problem: inefficient SQL queries due to the intensive subqueries for permission checks, for example widget Problem by severity from 4.4 and MariaDB 10.4:

      SELECT t.triggerid, t.priority, t.expression, t.comments, t.url 
      FROM triggers t 
      WHERE 
      	NOT EXISTS (
      		SELECT NULL 
      		FROM functions f,items i,hosts_groups hgg 
      		LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid=11 
      		WHERE t.triggerid=f.triggerid AND f.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 t.triggerid IN (...)
      	AND NOT EXISTS (
      		SELECT NULL 
      		FROM functions f,items i,hosts h 
      		WHERE 
      			t.triggerid=f.triggerid 
      			AND f.itemid=i.itemid 
      			AND i.hostid=h.hostid 
      			AND (i.status<>0 OR h.status<>0)
      	) 
      	AND t.status=0 
      	AND t.flags IN (0,4);
      

      where "IN (...)" - list of ID.

      MariaDB 10.4 engine optimizer will use materialised views with additional options for IN syntax and this will void to the following slow queries even on small installations without super-super fast SSD based on 3DXpoint memory/SLC memory:

      Total time: 98.686037
      Total SQL time: 98.41665
      
      +------+--------------------+-------+------------+-----------------------------------------------------------+-------------------+---------+--------------------+---------+------------------------------------+
      | id   | select_type        | table | type       | possible_keys                                             | key               | key_len | ref                | rows    | Extra                              |
      +------+--------------------+-------+------------+-----------------------------------------------------------+-------------------+---------+--------------------+---------+------------------------------------+
      |    1 | PRIMARY            | t     | range      | PRIMARY,triggers_1                                        | triggers_1        | 12      | NULL               | 65      | Using index condition; Using where |
      |    3 | MATERIALIZED       | f     | ALL        | functions_1,functions_2                                   | NULL              | NULL    | NULL               | 69146   |                                    |
      |    3 | MATERIALIZED       | i     | eq_ref     | PRIMARY,items_1,items_3,items_4,items_5,items_6,items_7   | PRIMARY           | 8       | zabbix.f.itemid    | 1       |                                    |
      |    3 | MATERIALIZED       | h     | eq_ref     | PRIMARY,hosts_1,hosts_2,hosts_3,hosts_4,hosts_5,c_hosts_3 | PRIMARY           | 8       | zabbix.i.hostid    | 1       | Using where                        |
      |    2 | DEPENDENT SUBQUERY | f     | ref        | functions_1,functions_2                                   | functions_1       | 8       | zabbix.t.triggerid | 1       | Using temporary                    |
      |    2 | DEPENDENT SUBQUERY | i     | eq_ref     | PRIMARY,items_1                                           | PRIMARY           | 8       | zabbix.f.itemid    | 1       |                                    |
      |    2 | DEPENDENT SUBQUERY | hgg   | ref        | hosts_groups_1                                            | hosts_groups_1    | 8       | zabbix.i.hostid    | 1       | Using index                        |
      |    2 | DEPENDENT SUBQUERY | r     | ref|filter | rights_1,rights_2                                         | rights_2|rights_1 | 8|8     | zabbix.hgg.groupid | 5 (10%) | Using where; Using rowid filter    |
      +------+--------------------+-------+------------+-----------------------------------------------------------+-------------------+---------+--------------------+---------+------------------------------------+
      8 rows in set (0.002 sec)
      

      https://mariadb.com/kb/en/exists-to-in-optimization/
      https://jira.mariadb.org/browse/MDEV-12387

      On MySQL this query will use function_ keys and slowdown will not be so visible.

      Permission checks should be fast.

      Regards,
      Edgar

            asestakovs Aleksejs Sestakovs
            edgar.akhmetshin Edgar Akhmetshin
            Team B
            Votes:
            56 Vote for this issue
            Watchers:
            50 Start watching this issue

              Created:
              Updated:
              Resolved: