Problem dashboard.

[GeorgeKup]

OS RHEL 7.6 Zabbix 4.4.6. nginx and zabbix server on one server, database (mariadb 10.4) on another server.
RAM: on the server - 16 GB, on the database - 32 GB. The number of nodes is 600. The number of data elements is 10,000.
Mariadb configuration file:
# path
datadir = / var / lib / mysql
# tmpdir = / dev / shm
# pid-file = /var/run/mysqld/mysqld.pid
#socket = /var/run/mysqld/mysqld.sock
basedir = / usr
lc-messages-dir = / usr / share / mysql
# network
connect_timeout = 60
wait_timeout = 28800
max_connections = 3000
max_allowed_packet = 256M
max_connect_errors = 1000
#interactive_timeout <--> <------> = 180
#net_read_timeout <-----> <------> = 900
#net_write_timeout <----> <------> = 900
# limits
tmp_table_size = 1G
max_heap_table_size = 1G
table_open_cache = 1024
# logs
# general_log_file <----> <------> = /tmp/mysql.log
# general_log <-> <------> <------> = 1
slow_query_log <> <------> <------> = ON
log_error = /tmp/mysql-error.log
slow_query_log_file = /tmp/mysql-slow.log
slow_query_log = 1
long_query_time = 5
# innodb
################################################## ##### ###################################
# query_cache_type = 1
# query_cache_size = 2G
query_cache_type = 0
query_cache_size = 0
query_cache_limit = 1M
innodb_buffer_pool_instances = 21
join_buffer_size = 512K
################################################## ##### ###################################
thread_cache_size = 16
innodb_data_file_path = ibdata1: 128M; ibdata2: 128M: autoextend: max: 4096M
innodb_file_per_table = 1
innodb_status_file = 1
innodb_buffer_pool_size = 12G
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M
innodb_strict_mode <----> <------> = 0
# innodb_use_native_aio> <------> = 0
innodb_log_file_size <--> <------> = 1G
innodb_buffer_pool_instances <--> = 12
# performance_schema <--> <------> = ON

in the zabbix_server configuration file, only the values are changed: StartPollers = 50; StartPollersUnreachable = 10

There are a lot of entries in the slow query log:

# Time: 200304 9:12:08
# User @ Host: zabbix [zabbix] @ [IP]
# Thread_id: 834904 Schema: zabbix QC_hit: No
# Query_time: 5.098264 Lock_time: 0.000122 Rows_sent: 0 Rows_examined: 3668171
# Rows_affected: 0 Bytes_sent: 331
use zabbix;
SET timestamp = 1583302328;
SELECT p.eventid, p.objectid, p.clock, p.ns, p.name, p.severit y FROM problem p 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 = 15 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 NOT EXISTS (SELECT NULL FROM event_suppress es WHERE es.eventid = p.eventid) AND (p.r_eventid IS NULL OR p.r_clock> 1583302023) ORDER BY p.eventid DESC LIMIT 1001;

MySQL uses only 6 GB of memory.


Problem:

Slowly (a minute or more) dashbodes open. Moreover, what is interesting is if the user has rights to everything, i.e. admin
then his dashboard opens in about 3 seconds, if the user has rights restrictions, then his
The dashboard opens for a minute or more. Changing MySQL configuration parameters to increase parameters
nothing gave. Translation to a much more powerful database hardware yielded nothing.

[tim.mooney]

I don't know if any of these are related to you problem, but I did notice a few things.

First, is MariaDB 10.4 supported with Zabbix 4.4.x? I know MariaDB up through 10.2 is supported, but I don't know about 10.3 or 10.4.

Second, you have innodb_buffer_pool_instances listed twice, with two different values.

Third, if your separate database server is dedicated to just MariaDB and it has 32 GiB RAM, you likely should set innodb_buffer_pool_size higher than 12G. I would probably set it in the 20-24G range, and then adjust innodb_buffer_pool_instances if needed. I'm using an older version of MariaDB, though, so I don't know if any of the tuning suggestions for innodb_buffer_pool_size & innodb_buffer_pool_instances has changed at very recent versions.

Fourth, you didn't mention SSDs for your MariaDB server, but your innodb_io_capacity is set quite high. Does your volume containing the database really have a high I/O capacity ("iops")?

[mmussons]

Hi GeorgeKup,
did you find any solution about? I have exactly the same problem but searching in events with this question:

SELECT e.eventid,e.objectid,e.clock,e.ns,e.name,e.severit y FROM events e 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,18,41,42,43,45,49,51,62,64,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 NOT EXISTS (SELECT NULL FROM event_suppress es WHERE es.eventid=e.eventid) AND e.clock>='1634580013' AND e.clock<='1637175613' AND e.eventid<='45539654' AND e.value=1 ORDER BY e.eventid DESC LIMIT 1001

For us this query takes more than 10 minutes so it's impossible to work because we have several users and queries begin to queue until database or zabbix die.
Any idea?

Thank you.