|
Please try this query:
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 (11,
13,
990)
WHERE p.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hgg.hostid and (permission in (0,1) or permission is NULL))
AND p.objectid=f.triggerid
AND f.itemid=i.itemid
AND i.hostid=hg.hostid
AND hg.groupid=1491
AND NOT EXISTS
(SELECT NULL
FROM event_suppress es
WHERE es.eventid=p.eventid)
AND (p.r_eventid IS NULL
OR p.r_clock>1594396440)
ORDER BY p.eventid DESC
LIMIT 1001;
|
|
I think this is likely related to #ZBX-17866 and the slow query I've seen and the widget performance issues that the OP on that issue reported.
|
|
If it is possible then please check if following index helps:
CREATE INDEX rights_3 ON rights (groupid,id);
Or even this one below to create covering index
CREATE INDEX rights_3 ON rights (groupid,id,permission);
it is strange that that PostgreSQL selects "Index Cond: (id = hgg.groupid)" as index condition, it should have been more efficient to use "(groupid = ANY ('11,13,990'::bigint[]))"
-> Index Scan using rights_2 on rights r (cost=0.29..0.60 rows=1 width=12) (actual time=0.003..0.004 rows=1 loops=19846514)
Index Cond: (id = hgg.groupid)
Filter: (groupid = ANY ('{11,13,990}'::bigint[]))
Rows Removed by Filter: 12
can you please share
select count(*) from rights where groupid IN (11,13,990);
|
|
Please also try, basically want to check how MySQL query analyzers reacts to such changes and if it will make correct decision now
SELECT DISTINCT e.eventid,
e.objectid,
e.clock,
e.ns,
e.name,
e.severity
FROM events e
WHERE EXISTS (SELECT NULL
FROM
functions f,
items i,
hosts_groups hg
WHERE e.source = '0'
AND e.object = '0'
AND e.objectid = f.triggerid
AND f.itemid = i.itemid
AND i.hostid = hg.hostid
AND hg.groupid IN ( 4934, 4937 )
AND e.severity IN ( 2, 3, 4, 5 )
AND e.value = 1)
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 ( 16, 22, 52 )
WHERE e.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 e.eventid DESC
LIMIT 1001;
select count(*) from events;
|
|
Suggested to reorganize query in the following way so that first Zabbix frontend selects all necessary events using hosts_groups, severity, value, object and source and only then try checking rights for those instead of running through all events and checking everything:
SELECT DISTINCT e.eventid,
e.objectid,
e.clock,
e.ns,
e.name,
e.severity
FROM events e
WHERE EXISTS (SELECT NULL
FROM
functions f,
items i,
hosts_groups hg
WHERE e.source = '0'
AND e.object = '0'
AND e.objectid = f.triggerid
AND f.itemid = i.itemid
AND i.hostid = hg.hostid
AND hg.groupid IN ( 4934, 4937 )
AND e.severity IN ( 2, 3, 4, 5 )
AND e.value = 1)
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 ( 16, 22, 52 )
WHERE e.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 e.eventid DESC
LIMIT 1001;
It increases performance from 2 minutes and 30 seconds to 3 seconds.
|
|
I have tested the suggested SQL queries on multiple servers. The database is identical.
# select count(*) from events;
14820006
# select count(*) from events where source=0;
96435
# select count(*) from functions;
26402
# select count(*) from items;
33581
# select count(*) from hosts_groups;
229
# select count(*) from rights;
81
| |
MySQL 5.5.62 |
MySQL 5.7.29 |
MySQL 8.0.19 |
MariaDB 5.5.64 |
MariaDB 10.3.15 |
PostgreSQL 12.4 |
| SQL A (original) |
1.952s |
1.663s |
3.366s |
4.763s |
0.091s |
1.922s |
| SQL B |
0.639s |
0.806s |
3.486s |
4.308s |
8.984s |
1.827s |
| SQL C |
27.354s |
11.368s |
49.302s |
5.044s |
0.063s |
1.809s |
SQL A (original)
SELECT DISTINCT e.eventid,e.objectid,e.clock,e.ns,e.name,e.severity
FROM events e,functions f,items i,hosts_groups hg
WHERE e.source='0'
AND e.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=18
WHERE e.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 e.objectid=f.triggerid
AND f.itemid=i.itemid
AND i.hostid=hg.hostid
AND hg.groupid=10
AND e.severity IN (1,2,3,4,5)
AND NOT EXISTS (
SELECT NULL
FROM event_suppress es
WHERE es.eventid=e.eventid
)
AND e.value=1
ORDER BY e.eventid DESC
LIMIT 10001
SQL B
SELECT e.eventid,e.objectid,e.clock,e.ns,e.name,e.severity
FROM events e
WHERE e.source='0'
AND e.object='0'
AND e.severity IN (1,2,3,4,5)
AND e.value=1
AND EXISTS (
SELECT NULL
FROM functions f,items i,hosts_groups hg
WHERE e.objectid=f.triggerid
AND f.itemid=i.itemid
AND i.hostid=hg.hostid
AND hg.groupid=10
)
AND NOT EXISTS (
SELECT NULL
FROM event_suppress es
WHERE es.eventid=e.eventid
)
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=18
WHERE e.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 e.eventid DESC
LIMIT 10001
SQL C
SELECT e.eventid,e.objectid,e.clock,e.ns,e.name,e.severity
FROM events e
WHERE EXISTS (
SELECT NULL
FROM functions f,items i,hosts_groups hg
WHERE e.source = '0'
AND e.object = '0'
AND e.severity IN (1,2,3,4,5)
AND e.value = 1
AND e.objectid = f.triggerid
AND f.itemid = i.itemid
AND i.hostid = hg.hostid
AND hg.groupid IN (10)
)
AND NOT EXISTS (
SELECT NULL
FROM event_suppress es
WHERE es.eventid=e.eventid
)
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 (18)
WHERE e.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 e.eventid DESC
LIMIT 1001
|
|
This patch should improve performance of SQL statements in event.get and problem.get API methods with MySQL 5.x. Please check.
zbx-18080-5.0.4.patch
zbx-18080-4.0.26.patch
|
|
This was resolved with the above patch a year ago and I've been through multiple Zabbix updates since 5.0.4 and not had the issue reoccur. I'm not sure why this Issue is still open and continually pushed on to new Sprints, but it should almost certainly be marked as resolved and closed.
|
|
It seems that the problem cannot be reproduced with the latest releases of Zabbix, I am closing it.
|
|
The problem recurred on version 5.4.12
|
Generated at Sun May 18 07:08:44 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.
explain.sql
query_cost.png
zbx-18080-4.0.26.patch
Team D