Details
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
- causes
-
ZBX-21222 Zabbix 5.0.23 - MySQL 8.0.15
-
- Confirmed
-
- is duplicated by
-
ZBX-8981 Slow queries when checking permissions
-
- Closed
-
-
-
- Closed
-
-
-
- QA Failed
-
- part of
-
-
- Closed
-