Performance Tuning (MySQL)

OS Changes

OS Swapiness

Adjusts OS's tendency to swap memory to disk. The OS will swap memory to disk when the % usage of RAM is reached, so for 60%, the OS will start swapping when there is 60% of available RAM remaining.

• To change permanently
  1. Edit /etc/sysctl.conf
  2. Update vm.swappiness = 0
  3. Reboot to apply
• To change on the fly
  • sysctl vm.swappiness=10
• To check current
  • cat /proc/sys/vm/swappiness

Disk IO Scheduler

Adjusts the disk IO queuing algorithm in use. Deadline is recommended for MySQL.

• To change permanently (requires reboot)
  1. Edit the /etc/grub.conf
  2. Append elevator=deadline to the kernel line (see example below)
• To change sda and sdb devices on the fly
  1. echo deadline > /sys/block/sda/queue/scheduler
  2. echo deadline > /sys/block/sdb/queue/scheduler
• To check current
  • cat /sys/block/sda/queue/scheduler
  • Current scheduler is surrounded by [ ... ]

title Red Hat Enterprise Linux Server (2.6.18-8.el5)
	root (hd0,0) 
	kernel /vmlinuz-2.6.18-8.el5 ro root=/dev/sda2 elevator=deadline
	initrd /initrd-2.6.18-8.el5.img

Disable Last Access Timestamps

Stops the filesystem tracking the last time a file was accessed.

• To change
  1. Edit /etc/fstab
  2. Add noatime option to relevant filesystems (see example below
  3. Reboot to apply
• To check current
  • Run mount command

/dev/mapper/VolGroup00-LogVol01 /                       ext4    noatime,defaults        1 1
UUID=264a246c-1823-4ef5-87f2-1a976d272a74 /boot                   ext4    defaults        1 2
/dev/mapper/VolGroup00-LogVol02 /home                   ext4    noatime,defaults        1 2
/dev/mapper/VolGroup00-LogVol04 /tmp                    ext4    noatime,defaults        1 2
/dev/mapper/VolGroup00-LogVol03 /usr                    ext4    noatime,defaults        1 2
/dev/mapper/VolGroup00-LogVol05 /var                    ext4    noatime,defaults        1 2
/dev/mapper/VolGroup00-LogVol00 swap                    swap    defaults        0 0
tmpfs                   /dev/shm                tmpfs   defaults        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0

MySQL Server Changes

The MySQL config file is normally found at /etc/my.cnf

InnoDB Buffer Size

Amount of memory that can be used by InnoDB to store data and indexes, should be as high as possible whilst leaving enough for other system processes (EG 12GB for a 16GB system)

innodb_buffer_pool_size                 = 10G

Requires restart to apply.

InnoDB Buffer Pools

Number of pools to split the buffer space into. Should equal the number of CPUs.

innodb_buffer_pool_instances            = 4

Requires restart to apply.

InnoDB Redo Log Size

Needs to be sufficiently large enough to allow good write performance.

innodb_log_file_size                    = 64M

To apply change...

1. Update config file
2. Shutdown MySQL
   • service mysql stop
3. Delete the old logfiles
   • EG mv /var/lib/mysql/ib_logfile* /var/tmp/.
4. Start MySQL
   • service mysql start

InnoDB Statistics

Setting this option to OFF is recommended to avoid that some queries on the information_schema database become very slow.

innodb_stats_on_metadata                = off

Sync Binary Log

Forces writes to the binary log to be written to disk immediately (can't be write-cached by OS).

sync_binlog     = 1

Query Cache

Disable the query cache completely, this entire cache is governed by a single mutex which hurts performance, in later versions of MySQL is it disabled by default.

query_cache_limit = 0
query_cache_size = 0

Maximum Connections

Maximum number of concurrent client connections

max_connections = 400

Requires restart to apply.

Disable DNS Resolution

skip-name-resolve

Requires restart to apply.