Mysql – Zabbix Performance (history syncer 100%)

mariadbMySQLPROXYzabbix

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

Logs, confs and graphs

Best Answer

Solution:

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

[client-server]

[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_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_force_recovery = 0

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

[mysql_safe] log-error=/opt/timwe/log/mysqld_safe_error.log

pid-file=/var/run/mysqld/mysqld.pid

Related Topic