Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save dronezzzko/4a38a1652187dbd0a89c17ed5b249bb2 to your computer and use it in GitHub Desktop.
Save dronezzzko/4a38a1652187dbd0a89c17ed5b249bb2 to your computer and use it in GitHub Desktop.
MySQL/MariaDB Tuning And Optimization For Best Performance

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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment