MariaDB tuning for Zabbix

I have a Zabbix server with 40GB of memory that requires around 700 new values per second. The server runs MariaDB 11.4 in a standard LAMP-stack and I use MySQLTuner-perl to tune the database. Here is how.

Enable the following configuration in /etc/my.cnf.d/mariadb_server.cnf

[mariadb]
performance_schema
log_queries_not_using_indexes
log_slow_query
log_slow_query_time = 4

Restart MariaDB:

$ systemctl restart mariadb

Let the server run for some time and then execute the script:

$ perl mysqltuner.pl

The script will give suggestions on how to increase performance and stability. Make small adjustments, restart MariaDB and repeat. This is what the final configuration ended up like in my case:

$ cat /etc/my.cnf.d/mariadb_server.cnf
[mariadb]
bind_address                  = 127.0.0.1
port                          = 3306
socket                        = /var/lib/mysql/mysql.sock
pid_file                      = /run/mariadb/mariadb.pid
datadir                       = /var/lib/mysql
tmpdir                        = /var/tmp
log_error                     = /var/log/mariadb/mariadb.log
skip_external_locking
skip_name_resolve
innodb_buffer_pool_size        = 16G
innodb_log_file_size           = 4G
innodb_flush_log_at_trx_commit = 0
join_buffer_size               = 4M
table_definition_cache         = 500

It’s also a good idea to take a look at /var/log/mariadb/mariadb.log to ensure there aren’t any warnings etc.


Posted

in

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *