Zabbix Server Memory Problem

[onder]

Hello everyone,
I have a Zabbix server with the following specifications and I am experiencing memory problems. How can I solve this problem? Can I get your advice? Thanks

**********
System information
**********
16 CORE
64 GB Memory
Ubuntu 22.04 LTS
Apache:2.4.52 (Ubuntu)
MySQL: 8.0.36-0ubuntu0.22.04.1
Zabbix: 6.4.14

Parameter Value Details
Zabbix server is running Yes localhost:10051
Number of hosts (enabled/disabled) 397 396 / 1
Number of templates 284
Number of items (enabled/disabled/not supported) 74102 71055 / 2788 / 259
Number of triggers (enabled/disabled [problem/ok]) 48328 46995 / 1333 [196 / 46799]
Number of users (online) 87 6
Required server performance, new values per second 852.34
High availability cluster Disabled

**********
/etc/mysql/mysql.conf.d/mysqld.cnf
**********
[mysqld]
user = mysql
innodb_buffer_pool_size = 51G
innodb_buffer_pool_instances = 48
innodb_log_file_size = 12G
join_buffer_size = 4M
skip-name-resolve=ON
bind-address = 127.0.0.1
mysqlx-bind-address = 127.0.0.1
key_buffer_size = 16M
myisam-recover-options = BACKUP
max_connections = 1000
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes
log-slow-admin-statements
log-slow-replica-statements
log_bin = /var/log/mysql/mysql-bin.log
binlog_expire_logs_seconds = 604800
max_binlog_size = 100M


**********
/etc/apache2/apache2.conf
**********
DefaultRuntimeDir ${APACHE_RUN_DIR}
PidFile ${APACHE_PID_FILE}
Timeout 100
KeepAlive On
MaxKeepAliveRequests 200
KeepAliveTimeout 3
User ${APACHE_RUN_USER}
Group ${APACHE_RUN_GROUP}
HostnameLookups Off
ErrorLog ${APACHE_LOG_DIR}/error.log
LogLevel warn
IncludeOptional mods-enabled/*.load
IncludeOptional mods-enabled/*.conf
Include ports.conf
<Directory />
Options FollowSymLinks
AllowOverride None
Require all denied
</Directory>
<Directory /usr/share>
AllowOverride None
Require all granted
</Directory>
<Directory /var/www/>
Options Indexes FollowSymLinks
AllowOverride None
Require all granted
</Directory>
AccessFileName .htaccess
<FilesMatch "^\.ht">
Require all denied
</FilesMatch>
LogFormat "%v:%p %h %l %u %t "%r" %>s %O "%{Referer}i" "%{User-Agent}i"" vhost_combined
LogFormat "%h %l %u %t "%r" %>s %O "%{Referer}i" "%{User-Agent}i"" combined
LogFormat "%h %l %u %t "%r" %>s %O" common
LogFormat "%{Referer}i -> %U" referer
LogFormat "%{User-agent}i" agent
IncludeOptional conf-enabled/*.conf
IncludeOptional sites-enabled/*.conf
ServerName localhost

**********
/etc/zabbix/zabbix_server.conf
**********
LogFile=/var/log/zabbix/zabbix_server.log
LogFileSize=100
PidFile=/run/zabbix/zabbix_server.pid
SocketDir=/run/zabbix
DBName=db
DBUser=user
DBPassword=password
StartPollers=100
StartPollersUnreachable=100
StartTrappers=100
StartPingers=100
SNMPTrapperFile=/var/log/snmptrap/snmptrap.log
CacheSize=5G
HistoryCacheSize=1G
HistoryIndexCacheSize=1G
TrendCacheSize=1G
TrendFunctionCacheSize=128M
ValueCacheSize=5G
Timeout=10
FpingLocation=/usr/bin/fping
Fping6Location=
LogSlowQueries=0
StartLLDProcessors=20
StatsAllowedIP=127.0.0.1


**********
vmstat
**********
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
4 1 4130612 471388 493504 3937836 0 1 3 195 1 2 3 2 89 6 0

**********
topas
**********
top - 14:07:43 up 12 days, 16:01, 2 users, load average: 8.48, 7.58, 6.71
Tasks: 749 total, 2 running, 747 sleeping, 0 stopped, 0 zombie
%Cpu(s): 4.6 us, 4.5 sy, 0.0 ni, 72.8 id, 17.2 wa, 0.0 hi, 1.0 si, 0.0 st
MiB Mem : 64394.4 total, 613.7 free, 59660.6 used, 4120.0 buff/cache
MiB Swap: 4096.0 total, 72.0 free, 4024.0 used. 3551.5 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1052 mysql 20 0 59.7g 54.7g 16732 S 166.9 86.9 6449:11 mysqld
1891 zabbix 20 0 13.2g 15700 9252 R 8.3 0.0 567:35.15


mysql> SHOW VARIABLES LIKE 'max_connections';
max_connections 1000

mysql> SHOW STATUS WHERE `variable_name` = 'Threads_connected';
Threads_connected 144

mysql> SHOW VARIABLES LIKE 'innodb_log_file_size';
innodb_log_file_size 12884901888

mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
innodb_buffer_pool_size 57982058496

mysql> SHOW VARIABLES LIKE 'table_open_cache';
table_open_cache 4000

mysql> SHOW VARIABLES LIKE 'query_cache_size';
Empty set (0.01 sec)

[solution]

With this configuration you will have problems.
Hardware memory: 64GB
your configuration:
innodb_buffer_pool_size = 51G + 1000 connections + other caches + zabbix server + other applications + O.S = +100GB (maybe) required.

Use mysqltuner to find out the maximum that mysql will consume with the current configuration, and follow the tips, make adjustments.

https://github.com/major/MySQLTuner-perl

Wellington

[onder]

solution thank you for your interest. Ok, I will run mysqltuner and reconfigure SQL. I will monitor it and share the result again.