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

Enhance permission checking/handling

    XMLWordPrintable

    Details

    • Type: New Feature Request
    • Status: Open
    • Priority: Blocker
    • Resolution: Unresolved
    • Affects Version/s: 3.0.30, 4.0.19, 4.0.21, 4.4.7, 5.0.0alpha4, 5.0.1
    • Fix Version/s: None
    • Component/s: Frontend (F)

      Description

      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

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              zabbix.dev Zabbix Development Team
              Reporter:
              edgar.akhmetshin Edgar Akhmetshin
              Votes:
              38 Vote for this issue
              Watchers:
              19 Start watching this issue

                Dates

                Created:
                Updated: