MySQL/MariaDB Tuning And Optimization For Best Performance

mysql-mariadb-tuning-and-optimization-for-best-performance.md

MySQL/MariaDB Tuning And Optimization For Best Performance

OS Level Optimization

Swappiness

sysctl -w vm.swappiness=1
echo "vm.swappiness = 1" | sudo tee -a /etc/sysctl.conf

noatime

Add noatime to the options of the / file system in /etc/fstab, e.g. like this:

/etc/fstab

/dev/sda1 / ext4 noatime,errors=remount-ro 0 1

sudo mount -o remount /

I/O Scheduler

sudo echo noop > /sys/block/sdb/queue/scheduler

CPU Governor

sudo echo "performance" | sudo tee /sys/devices/system/cpu/cpu*/cpufreq/scaling_governor

MySQL/MariaDB Tuning And Optimization

It's assumed that the latest version of MySQL/MariaDB is installed and the default engine is InnoDB.

Optimized Options Based on Your Total Memory

/my.cnf or /etc/mysql/my.cnf

Less Than 1GB

[mysqld]

key_buffer_size                 = 64K # As we don't make use of MyISAM tables at all.
innodb_buffer_pool_size         = 128M
innodb_flush_method             = O_DIRECT_NO_FSYNC # Use O_DIRECT for XFS filesystems
innodb_flush_log_at_trx_commit  = 0

slow_query_log                  = /var/log/mysql/mysql-slow.log
long_query_time                 = 1

1GB

[mysqld]

key_buffer_size                 = 64K # As we don't make use of MyISAM tables at all.
innodb_buffer_pool_size         = 512M
innodb_flush_method             = O_DIRECT_NO_FSYNC # Use O_DIRECT for XFS filesystems
innodb_flush_log_at_trx_commit  = 0

slow_query_log                  = /var/log/mysql/mysql-slow.log
long_query_time                 = 1

2GB

[mysqld]

key_buffer_size                 = 64K # As we don't make use of MyISAM tables at all.
innodb_buffer_pool_size         = 1024M
innodb_flush_method             = O_DIRECT_NO_FSYNC # Use O_DIRECT for XFS filesystems
innodb_flush_log_at_trx_commit  = 0

slow_query_log                  = /var/log/mysql/mysql-slow.log
long_query_time                 = 1

4GB

[mysqld]

key_buffer_size                 = 64K # As we don't make use of MyISAM tables at all.
innodb_buffer_pool_size         = 3072M
innodb_flush_method             = O_DIRECT_NO_FSYNC # Use O_DIRECT for XFS filesystems
innodb_flush_log_at_trx_commit  = 0

slow_query_log                  = /var/log/mysql/mysql-slow.log
long_query_time                 = 1