[ZBXNEXT-5878] Enhance permission checking/handling Created: 2020 Apr 08  Updated: 2025 Jan 13  Resolved: 2024 Feb 02

Status: Closed
Project: ZABBIX FEATURE REQUESTS
Component/s: Server (S)
Affects Version/s: 3.0.30, 4.0.19, 4.0.21, 4.4.7, 5.0.0alpha4, 5.0.1
Fix Version/s: 7.0.0beta1, 7.0 (plan)

Type: New Feature Request Priority: Major
Reporter: Edgar Akhmetshin Assignee: Aleksejs Sestakovs
Resolution: Fixed Votes: 56
Labels: Frontend, Graphs, SQL, dashboard, slow, widgets
Σ Remaining Estimate: Not Specified Remaining Estimate: Not Specified
Σ Time Spent: Not Specified Time Spent: Not Specified
Σ Original Estimate: Not Specified Original Estimate: Not Specified

Attachments: Text File 20230313zabbixslowquery.log     File ZBXNEXT-5878-5.0-trigger-test.diff     File ZBXNEXT-5878-6.0-trigger-test.diff     File ZBXNEXT-5878-6.0-trigger.diff     File host_for_LLD_check.yaml     GIF File ug_hg_tg_migration_issue.gif    
Issue Links:
Causes
causes ZBX-21222 Problem screen is very slow, HTTP 502... Closed
causes ZBX-24772 Inconsistence in data returned with A... Closed
causes ZBX-24792 Empty Notifications page for a user w... Closed
causes ZBX-25670 update host groups membership is not ... Closed
causes ZBX-25868 Few built-in templates aren't accessi... Closed
Duplicate
is duplicated by ZBX-21222 Problem screen is very slow, HTTP 502... Closed
is duplicated by ZBX-8981 Slow queries when checking permissions Closed
is duplicated by ZBX-17302 ORA-04036: PGA memory used by the ins... Closed
is duplicated by ZBX-21504 Slow permission check for dashboards ... Closed
is duplicated by ZBX-7706 Slow queries when checking permissions Closed
is duplicated by ZBX-21541 Monitoring/Hosts very slow loading fo... Closed
is duplicated by ZBX-23957 Problems Tab & Dashboard with Problem... Closed
Sub-task
depends on ZBX-24016 Upgrade to 7.0.0beta1 failed in step ... Closed
part of ZBX-16822 Improve queries on problem page load Closed
Sub-Tasks:
Key
 Summary
 Type
 Status
 Assignee
ZBXNEXT-8532 Frontend changes to enhance permissio... Specification change (Sub-task) Closed Vladimirs Maksimovs  
Epic Link: Zabbix 7.0
Team: Team B
Sprint: 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
Story Points: 10

 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

 Comments   
Comment by Alexei Vladishev [ 2020 Apr 08 ]

Do I understand correctly that it is regression of MariaDB performance? It is used to be efficient but was broken in a recent release, correct?

Comment by Edgar Akhmetshin [ 2020 Apr 08 ]

In this case, this is just an example where a query that is not effective from the point of view of the execution plan has led to a slowdown. Similar problems with PostgreSQL, Oracle. The effect is smoothed by the use of quick ssd or where the total database size fits in the database cache, or where a simplified delegation scheme of access rights with 1 group and several users is used. Disabling the options of modern databases over time will lead to more problems. The speed of verification of access rights is tied to the power of the server hardware and no the SQL guidelines.

Described example is from database server with 6 core intel Xeon E5-series 3.6Ghz CPU with 10Gb of RAM and SAN storage attached VM with ~200 NVPS, ~50k hosts, 13k triggers and partitioned 130G history/trends.

Found some interesting analytics. According to the Page loading speed and user frustration SQL permission check should be done fast for a better UX.

0.1 seconds. This limit gives users the feeling of instantaneous response. This level of responsiveness is essential to support the feeling of direct manipulation. It’s also an ideal response time for the website.

1 second. One second keeps the user’s flow almost seamless. While users notice a slight delay, they still feel in control of the experience.

10 seconds is the limit for the user’s attention. For delays of more than 10 seconds, users will want to perform other tasks while waiting for the computer to finish.

Regards,
Edgar

Comment by Edgar Akhmetshin [ 2020 Apr 09 ]

Disabling optimiser switch doesn't help. But query execution plan changed to the old behaviour. 

 EXPLAIN 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 (29239,29388,29563,29565,30058,30074,30076,30164,30174,30176,30180,30181,30297,30299,31127,31129,31213,31229,31232,31234,31235,31237,31240,31241,31254,31269,31270,31288,31290,31294,31295,31306,31312,31694,31696,43282,44288,48038,50482,50483,50632,50633,67835,67947,67963,67965,69175,72757,77053,77055,80282,84314,84316,89583,89599,89615,89740,89756,89788,89804,89820,90152,90154,91250,91889) 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);
+------+--------------------+-------+------------+-------------------------+-------------------+---------+--------------------+--------+---------------------------------+
| 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 where                     |
|    3 | DEPENDENT SUBQUERY | f     | ref        | functions_1,functions_2 | functions_1       | 8       | zabbix.t.triggerid | 1      |                                 |
|    3 | DEPENDENT SUBQUERY | i     | eq_ref     | PRIMARY,items_1,items_3 | PRIMARY           | 8       | zabbix.f.itemid    | 1      |                                 |
|    3 | DEPENDENT SUBQUERY | h     | eq_ref     | PRIMARY,hosts_2         | PRIMARY           | 8       | zabbix.i.hostid    | 1      | Using where                     |
|    2 | DEPENDENT SUBQUERY | i     | index      | PRIMARY,items_1         | items_1           | 775     | NULL               | 136741 | Using index                     |
|    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 | 4 (7%) | Using where; Using rowid filter |
|    2 | DEPENDENT SUBQUERY | f     | ref        | functions_1,functions_2 | functions_1       | 8       | zabbix.t.triggerid | 1      | Using where                     |
+------+--------------------+-------+------------+-------------------------+-------------------+---------+--------------------+--------+---------------------------------+

With options on:

+------+--------------------+-------+------------+-----------------------------------------------------------+-------------------+---------+--------------------+---------+-----------+----------+------------+------------------------------------+
| id   | select_type        | table | type       | possible_keys                                             | key               | key_len | ref                | rows    | r_rows    | filtered | r_filtered | Extra                              |
+------+--------------------+-------+------------+-----------------------------------------------------------+-------------------+---------+--------------------+---------+-----------+----------+------------+------------------------------------+
|    1 | PRIMARY            | t     | range      | PRIMARY,triggers_1                                        | triggers_1        | 12      | NULL               | 65      | 61.00     |   100.00 |      93.44 | Using index condition; Using where |
|    3 | MATERIALIZED       | f     | ALL        | functions_1,functions_2                                   | NULL              | NULL    | NULL               | 69146   | 69457.00  |   100.00 |     100.00 |                                    |
|    3 | MATERIALIZED       | i     | eq_ref     | PRIMARY,items_1,items_3,items_4,items_5,items_6,items_7   | PRIMARY           | 8       | zabbix.f.itemid    | 1       | 1.00      |   100.00 |     100.00 |                                    |
|    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       | 1.00      |   100.00 |      54.79 | Using where                        |
|    2 | DEPENDENT SUBQUERY | i     | index      | PRIMARY,items_1                                           | items_1           | 775     | NULL               | 87119   | 93992.00  |   100.00 |     100.00 | Using index                        |
|    2 | DEPENDENT SUBQUERY | hgg   | ref        | hosts_groups_1                                            | hosts_groups_1    | 8       | zabbix.i.hostid    | 1       | 1.55      |   100.00 |     100.00 | Using index                        |
|    2 | DEPENDENT SUBQUERY | r     | ref|filter | rights_1,rights_2                                         | rights_2|rights_1 | 8|8     | zabbix.hgg.groupid | 5 (10%) | 0.98 (7%) |     9.68 |     100.00 | Using where; Using rowid filter    |
|    2 | DEPENDENT SUBQUERY | f     | ref        | functions_1,functions_2                                   | functions_1       | 8       | zabbix.t.triggerid | 1       | 1.00      |   100.00 |       0.00 | Using where                        |
+------+--------------------+-------+------------+-----------------------------------------------------------+-------------------+---------+--------------------+---------+-----------+----------+------------+------------------------------------+

So MariaDB is not the issue.

Comment by dimir [ 2020 Apr 09 ]

The subqueries must be executed separately. I understand it's a big job but someone from Frontend devs that know this area well could comment on that. I'll try to get someone.

Comment by dimir [ 2020 Apr 10 ]

One more idea would be to introduce additional table for trigger_host relation, it will allow to exclude huge tables like 'functions', 'items' when performing trigger permissions level checks

Maybe even trigger_hostgroup? Would it help to first create such a relationship on the fly and then deal with permissions? That relationship could also be cached, as someone from you guys were proposing some time ago.

Comment by dimir [ 2020 Apr 10 ]

One other improvement proposal I heard is adding that t.triggerid in (...) to the sub-queries. edgar.akhmetshin, could you try that and see if that gives any improvement?

Comment by Ahmed Morsy [ 2020 May 23 ]

Hello Guys,

Could anyone please confirm that moving to MYSQL instead of MariaDB does fix the problem? The current work around for me is to set users as super-admin which is really not good.

is there any other work arounds like downgrade/upgrade zabbix/mariadb versions?

My current configuration:

Zabbix - 4.4.7

MariaDB -  10.4

 

Hardware configuration:

I got 3 servers with the below configuration running Galera Cluster:

CPU:     2 x 12-Cores    Intel® Xeon® Gold 5118 Processor

RAM:    96GB

Disk:     SSD

 

Zabbix Data:

Hosts 9k

Items  250k

 

Thanks,

Ahmed

 

Comment by Ahmed Morsy [ 2020 Jul 02 ]

Just to let you know, moving from Mariadb to MySQL made a big performance difference for my setup.

 

Comment by Alexei Vladishev [ 2020 Jul 02 ]

amorsy, thanks for the follow up, interesting!

Comment by Vladislavs Sokurenko [ 2020 Jul 02 ]

This is a long shot but could such index help to avoid full table scan ?

KEY `functions_1` (`triggerid`),
to:
`functions_1` (`triggerid`,`itemid`),

if someone has test database to try:

create index functions_3 on functions (`triggerid`,`itemid`);
drop index functions_1 on functions;

and show explain after.

Comment by dimir [ 2020 Jul 03 ]

amorsy positive?

Comment by Ahmed Morsy [ 2020 Jul 03 ]

@dimir  Yes, that solved the problem for me.

Comment by Backoffice Team [ 2022 Jun 23 ]

Folks, we know this problem from long time now, but we are glad to see a ticket to attack it.

I'd like to contribute with the query tuning we've done to archive better performance. Please, consider implementing it, it looks very promissing.

 

Tests were done using percona 8.0.26.

~3M items, ~1M triggers, ~15k nvps

 

Original
SELECT DISTINCT p.eventid,p.objectid,p.clock,p.ns,p.name,p.severity FROM problem p,functions f,items i,hosts_groups hg WHERE p.source='0' AND p.object='0' AND NOT EXISTS (SELECT NULL FROM functions f,items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid IN (21,45,178) WHERE p.objectid=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 p.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hg.hostid AND hg.groupid=1570 AND NOT EXISTS (SELECT NULL FROM event_suppress es WHERE es.eventid=p.eventid) AND (p.r_eventid IS NULL OR p.r_clock>1655911651) AND p.clock>='1654702112' ORDER BY p.eventid DESC LIMIT 100;
...
37 rows in set (16.40 sec)

Changed
SELECT * FROM ( SELECT DISTINCT p.eventid,p.objectid,p.clock,p.ns,p.name,p.severity  FROM problem p,functions f,items i,hosts_groups hg  WHERE p.source='0'  AND p.object='0'  AND p.objectid=f.triggerid  AND f.itemid=i.itemid  AND i.hostid=hg.hostid  AND hg.groupid='1570'  AND NOT EXISTS (SELECT NULL FROM event_suppress es WHERE es.eventid=p.eventid) AND (p.r_eventid IS NULL OR p.r_clock>1655911651) AND p.clock>='1654702112' ) a 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 IN (21,45,178) WHERE a.objectid=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)  ORDER BY a.eventid DESC LIMIT 101;
...
37 rows in set (0.67 sec)

 

Comment by Matt Alvis [ 2022 Oct 26 ]

I am on Zabbix 6.0.9 and experiencing this issue. Please include the latest versions as Affected.

Comment by Ian [ 2022 Dec 21 ]

Hi, I am also working with Zabbix version 6.0.5 and I get the same error.

Is this error being worked on or is there any solution to solve the problem? Setting all users as Super Admin does not work for us, as we need to filter the hosts they can see.

Comment by Vladislavs Sokurenko [ 2023 May 03 ]

Looks like related issue:
ZBX-18080

Comment by Vladislavs Sokurenko [ 2023 May 03 ]

There are suggested changes in patch for ZBX-18080 but also would be nice to try replacing

	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
	)

with

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 where permission=2 or permission=3)

And see if it helps.
If so then maybe it's possible to add another extra query

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 where permission is null or permission=0)
Comment by Vladislavs Sokurenko [ 2023 May 16 ]

Please try patch ZBXNEXT-5878-6.0-trigger.diff  or ZBXNEXT-5878-6.0-trigger-test.diffif possible to see if it improves trigger permission checks.

Comment by Thomas Mertz [ 2024 Jan 18 ]

Hi!

We recently upgraded our zabbix database from mariadb 10.3 to mariadb 10.6.16 and faced this issue discussed here.

We found out the difference of behavior beetween versions came mainly from a fix in the apt package : https://jira.mariadb.org/browse/MDEV-16251

As a workaround we re-enabled the  query_cache on the mariadb server, and performances are back to what they previously were.

I hope this information can help some people before a fix is available in zabbix 7

Comment by Dhinesh Kumar [ 2024 Jan 19 ]

@thomas, Thanks for your info. But unfortunately in MySQL version >8.0 query_cache is deprecated

Hope Support team can give some info to overcome this issue for version zabbix 5.0.38.

Comment by Vladimirs Maksimovs [ 2024 Jan 23 ]

Available in versions:

Comment by Martins Valkovskis [ 2024 Jan 26 ]

Updated documentation:

Generated at Sun Apr 20 21:37:18 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.