Tuning Mysql/MariaDB

[bennyboy]

Hi,

I want to tune MariaDB to get better result with Nagios DB. I use MySQLTuner 1.7.4 - Major Hayden <[email protected]> http://mysqltuner.com/.

My current my.cnf

Code: Select all

[mysqld]
query_cache_size=16M
query_cache_limit=4M
tmp_table_size=64M
max_heap_table_size=64M
key_buffer_size=32M
table_open_cache=32

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

I plan to use that custom configuration. I want to know if Nagios have advice. What I can use and what you discourage to use.

New my.cnf

Code: Select all

[mysqld]
query_cache_type=1
query_cache_min_res_unit = 2k
query_cache_limit=4M
query_cache_size=80M
tmp_table_size=64M
max_heap_table_size=64M
key_buffer_size=32M
table_open_cache=32
innodb_file_per_table=1
innodb_log_file_size=16M
thread_cache_size=16
# Skip reverse DNS lookup of clients
skip-name-resolve
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
long_query_time = 1

#The default value is 28800 seconds
wait_timeout=60

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

Increase mariadb max_connections

Code: Select all

# mysql -u root -p
mysql> set global max_connections := 500;

Ajusting some sysctl config

Code: Select all

vm.swappiness=0
fs.aio-max-nr=1048576
 8-) 

[bolson]

My advice would be to benchmark, backup your my.cnf, make the changes, and benchmark again. You may want to make the changes incrementally rather than all at once. Also, I would go with swappiness=1 rather than swappiness=0. Newer kernels completely disable swap on swappiness=0 leaving you with no protection from a memory leak crashing your system. swappiness=1 will accomplish the same thing but will swap when necessary to avoid an out of memory condition.

[bennyboy]

I read a lot about mariadb this afternoon. My conclusion is to swtich from 5.6 to 10.1. Nagios will support that ? Can you ask your dev team when he plan to switch to another version of MariaDB ?

thank a lot!

[kyang]

If you wanted to upgrade MariaDB manually, it may not be supported.

A Nagios XI license is approved for up to three installations: one primary monitoring/production, one backup/failover, and one test environment.

You could implement all of the changes on your test server first, then make sure everything works well BEFORE trying it in production (with all safety measures in place - proper backups, snapshots, etc.).

As for the MariaDB version, we use the version that is in the OS for almost all of our packages.

[bennyboy]

Nagios XI plan to test and migrate to MariaDB 10.X ?

[kyang]

As for the MariaDB version, we use the version that is in the OS for almost all of our packages.

@bennyboy, as of now we don't have any plans set for testing and migrating to MariaDB 10.X.

Did you have any more questions?