Three RHEL 8.5 nodes with 2 CPUs, 4 GB RAM. Web front end with MariaDB on one node, Zabbix Server on 2 other nodes in HA configuration

Hello Travis,

Please provide additional information:

1. MariaDB exact version used and table sizes (replace <zabbix db name> with actual name):

   select version();
   
   SELECT
     TABLE_NAME AS `Table`,
     ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
   FROM
     information_schema.TABLES
   WHERE
     TABLE_SCHEMA = "<zabbix db name>"
   ORDER BY
     (DATA_LENGTH + INDEX_LENGTH)
   DESC;
   

2. output from the following query:

   ANALYZE select distinct d.triggerid_down,d.triggerid_up from trigger_depends d,triggers t,hosts h,items i,functions f where t.triggerid=d.triggerid_down and t.flags<>2 and h.hostid=i.hostid and i.itemid=f.itemid and f.triggerid=d.triggerid_down and h.status in (0,1);
   

Also MariaDB configuration file would be helpful.

Regards,
Edgar

Hi Edgar,

Here is the output from the commands you provided:

select version();
10.6.7-MariaDB

see  table_size.csv for table size information

see trigger_query.csvfor the second query command you requested

see server.cnffor MariaDB configuration

Additionally, our zabbix cluster is brand new and we haven't added many devices yet.

Please let me know if you need anything else!

Do you have any additional database configuration files (in include directories for example)?

Just my two cents here, we're seeing the same behavior with MariaDB 10.6, Rocky Linux 8, and Zabbix 6.0.

Using  a very simple test environment, a small vagrant machine with MariaDB 10.6 from the official MariaDB Repo (no tunning at all in my.cnf) and a clean Zabbix 6 install (no other hosts added only the default Zabbix server host), and we get a lot of slow queries with the same select:

977:20220307:113729.416 slow query: 51.571350 sec, "select distinct d.triggerid_down,d.triggerid_up from trigger_depends d,triggers t,hosts h,items i,functions f where t.triggerid=d.triggerid_down and t.flags<>2 and h.hostid=i.hostid and i.itemid=f.itemid and f.triggerid=d.triggerid_down and h.status in (0,1)"
977:20220307:113922.890 slow query: 52.474598 sec, "select distinct d.triggerid_down,d.triggerid_up from trigger_depends d,triggers t,hosts h,items i,functions f where t.triggerid=d.triggerid_down and t.flags<>2 and h.hostid=i.hostid and i.itemid=f.itemid and f.triggerid=d.triggerid_down and h.status in (0,1)"
977:20220307:114118.002 slow query: 53.586517 sec, "select distinct d.triggerid_down,d.triggerid_up from trigger_depends d,triggers t,hosts h,items i,functions f where t.triggerid=d.triggerid_down and t.flags<>2 and h.hostid=i.hostid and i.itemid=f.itemid and f.triggerid=d.triggerid_down and h.status in (0,1)"
977:20220307:114313.576 slow query: 54.160693 sec, "select distinct d.triggerid_down,d.triggerid_up from trigger_depends d,triggers t,hosts h,items i,functions f where t.triggerid=d.triggerid_down and t.flags<>2 and h.hostid=i.hostid and i.itemid=f.itemid and f.triggerid=d.triggerid_down and h.status in (0,1)"
977:20220307:114506.700 slow query: 51.791388 sec, "select distinct d.triggerid_down,d.triggerid_up from trigger_depends d,triggers t,hosts h,items i,functions f where t.triggerid=d.triggerid_down and t.flags<>2 and h.hostid=i.hostid and i.itemid=f.itemid and f.triggerid=d.triggerid_down and h.status in (0,1)"

Rolling back to MariaDB 10.5 seems to solve the problem.

Currently looks like query analyser in MariaDB is misbehaving and not planning query correctly because there shouldn't be many trigger_depends but it looks like it just attempts to read all triggers, functions, hosts and items regardless of this fact, best solution would be to read all trigger dependencies without also finding related triggers, hosts, items and functions, alternative could be to add inner join to give more hints for MariaDB query analyser that it should only find related information for trigger_depends table.

Related issue:
ZBX-20936

Thank you for your report, closing as a duplicate of ZBX-20936