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.

[GeorgeKup]

View Requests

SELECT count (itemid) AS history_uint FROM history_uint WHERE itemid NOT IN (SELECT itemid FROM items WHERE status = '0');

with an almost empty base pass in 12 minutes