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

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

      
    Raw
  

              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