Zabbix Performance (history syncer 100%)

[Timwetech]

Since November 2018 we have performance problems with Zabbix 3.4.10.
Since this time, we made tuning for tcp connections, tuning in the OS (CentOS 6.9), tuning in the MariaDB conf, tuning in the Zabbix Server conf and tuning in the Zabbix Proxies conf.

The following problem remains:

2 times a day (5:22 p.m. and 8:22 p.m., we have different hours in the past) History Syncer reaches 100% and proxies become "NoDataProcessing".
While history syncer is at 100% several "NoData" alarms start to appear.
To recover, we stop the 2 major proxies, restart the server and raise the 2 proxies with a time interval.


Number of hosts 6679
Number of items 732813
Number of triggers 358638
Required server performance, new values per second 2695.54



Hardwares (VM):

- Server:
CPU: 4 CPU
Memory: 24GB

- Database (Ver 15.1 Distrib 10.3.4-MariaDB, for Linux (x86_64) using readline 5.1):
CPU: 8 CPUs
Memory: 64GB
Disk: SSD


Infrastructure:

1 Zabbix Server version 3.4.10 (lx-all-mon-zserver-01)
1 Database Ver 15.1 Distrib 10.3.4-MariaDB (lx-all-mon-zdb-03)
6 proxies for machines
1 proxy for Databases monitoring
1 proxy for webchecks

Attachments:

Server, proxies and database configurations.
Server and database graphics

[Timwetech]

Solution:

- My.cnf has been changed to the settings below:

# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
# include all files from the config directory

[mysqld]
datadir = /opt/timwe/data/mysql
innodb_log_group_home_dir = /opt/timwe/data/mysql/innodblog
log-bin = /opt/timwe/data/mysql/binlog

log_error = /opt/timwe/log/mysql_error.log
slow_query_log_file = /opt/timwe/log/slow_query.log

tmpdir=/opt/timwe/data/mysql/tmpfs

innodb_autoextend_increment = 256
innodb_buffer_pool_instances = 12
innodb_buffer_pool_size = 48G
innodb_concurrency_tickets = 5000
#innodb_data_file_path = ibdata1:1G:autoextend
#innodb_file_format = Barracuda # default
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_log_file_size = 512M
innodb_log_files_in_group = 8
innodb_old_blocks_time = 1000
innodb_open_files = 2048
innodb_stats_on_metadata = OFF
#innodb_autoinc_lock_mode = 2
innodb_force_recovery = 0
#innodb_io_capacity = 3000

#large-pages
skip-name-resolve
bind-address = 0.0.0.0
default_storage_engine = innodb
event_scheduler = ON
binlog-row-event-max-size = 8192
binlog-format = MIXED
character_set_server = utf8
collation_server = utf8_bin
expire_logs_days = 1
join_buffer_size = 262144
max_allowed_packet = 32M
max_connect_errors = 10000
max_connections = 5000
max_heap_table_size = 134217728
port = 3306
query_cache_type = 1
query_cache_size = 268435456
slow-query-log = ON
table_open_cache = 2048
thread_cache_size = 512
tmp_table_size = 134217728
user = mysql
wait_timeout = 86400
#key_buffer_size = 10G --unset
#log-bin = mondb1-bin
#sync_binlog = 0

#optimizer_switch='index_condition_pushdown = off';

[mysql_safe]
log-error=/opt/timwe/log/mysqld_safe_error.log
pid-file=/var/run/mysqld/mysqld.pid