Hi all,
I have critical performance problem with our big Zabbix 5 environment.
We have more or less 25000 hosts and 1500000 enabled items and 2000000 enabled triggers; most of them are snmp traps so we don't have polling problems)
But we have a big data history and a lot of hostgroups, usergroups, users and rights for these users so the problem is that queris for Normal users get more than 15 minutes running and for SuperAdmin users not more than 2 minutes.
Queries for different users are:
USER NORMAL
SELECT DISTINCT e.eventid,e.objectid,e.clock,e.ns,e.name,e.severit y 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 IN (13,80) 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=24755 AND NOT EXISTS (SELECT NULL FROM event_suppress es WHERE es.eventid=e.eventid) AND e.clock>='1642546800' AND e.clock<='1643151600' AND e.value=1 ORDER BY e.eventid DESC LIMIT 1001
SUPER ADMIN
SELECT DISTINCT e.eventid,e.objectid,e.clock,e.ns,e.name,e.severit y FROM events e,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=24755 AND NOT EXISTS (SELECT NULL FROM event_suppress es WHERE es.eventid=e.eventid) AND e.clock>='1642546800' AND e.clock<='1643151600' AND e.value=1 ORDER BY e.eventid DESC LIMIT 1001
Also, users push APPLY button a lot of times without wait the query finish so sometimes we found more than 30 queris running until Zabbix down.
The workaround is give SUPERADMIN rights to users but is very dangerouse....
So, any idea on how to solve it? Partition? Index.... Our DB is a MySQL 8 Cluster InnoDB of three nodes.
Thank you.
I have critical performance problem with our big Zabbix 5 environment.
We have more or less 25000 hosts and 1500000 enabled items and 2000000 enabled triggers; most of them are snmp traps so we don't have polling problems)
But we have a big data history and a lot of hostgroups, usergroups, users and rights for these users so the problem is that queris for Normal users get more than 15 minutes running and for SuperAdmin users not more than 2 minutes.
Queries for different users are:
USER NORMAL
SELECT DISTINCT e.eventid,e.objectid,e.clock,e.ns,e.name,e.severit y 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 IN (13,80) 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=24755 AND NOT EXISTS (SELECT NULL FROM event_suppress es WHERE es.eventid=e.eventid) AND e.clock>='1642546800' AND e.clock<='1643151600' AND e.value=1 ORDER BY e.eventid DESC LIMIT 1001
SUPER ADMIN
SELECT DISTINCT e.eventid,e.objectid,e.clock,e.ns,e.name,e.severit y FROM events e,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=24755 AND NOT EXISTS (SELECT NULL FROM event_suppress es WHERE es.eventid=e.eventid) AND e.clock>='1642546800' AND e.clock<='1643151600' AND e.value=1 ORDER BY e.eventid DESC LIMIT 1001
Also, users push APPLY button a lot of times without wait the query finish so sometimes we found more than 30 queris running until Zabbix down.
The workaround is give SUPERADMIN rights to users but is very dangerouse....
So, any idea on how to solve it? Partition? Index.... Our DB is a MySQL 8 Cluster InnoDB of three nodes.
Thank you.