The Linux OS from major vendors, including Red Hat, Ubuntu, and Debian, has defaults set out of the box that works well with minimally spec’d hardware. However, if you want the best performance for your MySQL databases, you need to make changes in how Linux handles swap, disk io scheduler, and disk mount options. This article will lead you through that process.
Swap
By default, most Linux distributions set swappiness to a value of 60 out (from a range of 0 to 60). This is done because there’s not enough physical memory to run all the processes on low memory systems. The default setting of 60 permits the OS to page memory out to a buffer on disk. This disk buffer is 100-1000 times slower than physical memory, so if the system has enough RAM installed, you want it to only page out to disk when absolutely necessary. You can do this by setting the Linux OS tunable variable vm.swappiness to 1. You might be tempted to set this to 0, but this is not a good practice because it completely disables the disk buffer. Additionally, the OS loses the ability to use the swap space in case of emergency; thus, a setting of 1 still allows emergency use but prevents the OS from doing things like caching the MySQL buffer pool and other in-memory objects during regular operation. Setting the swappiness appropriately will significantly improve MySQL performance.
A good general rule of thumb is that if you have a dedicated database server with 8GB of memory or more, change the vm.swappiness kernel tunable from 60 to 1.
To view the current swap setting run the sysctl -a command as root and grep for swap:
# sysctl -a | grep swap
vm.swappiness = 60
Changing this setting is a two-step process. First, we set it for the running kernel:
# sysctl -w vm.swappiness=1
And the second step is to make this persistent across reboots by appending this line to the /etc/sysctl.conf file, either by using your favorite editor (i.e., vim or nano) or by echoing it in:
# echo “vm.swappiness=1” >> /etc/sysctl.conf
Disk IO Schedulers
Modern Linux systems offer at least three disk io schedulers, and they each have different purposes. The default disk io scheduler is cfq (Completely Fair Queuing) and is suitable for most desktop and server io loads except for virtual server hosts and database io loads. Oracle’s official documentation for both the Oracle and MySQL database products uses either the Noop or the deadline disk io scheduler. A good description of most available disk io scheduler can be found at the following link; however, the less common options require you to recompile the kernel for support: https://wiki.ubuntu.com/Kernel/Reference/IOSchedulers
Common Disk IO Scheduler and what they do:
cfq: The cfq disk io scheduler separates incoming disk io requests into many queues (multi-queue) and permits each with a measured amount of disk io, switching context between queues and io requests. This means that many requests for disk io will read/write some data, move to other io requests, and then read/write more data, and this will keep occurring until the io request is complete. The result is improved speed for running games on the Linux desktop, enhances web server response, and is a great match for mixed-use servers. By the same token, this io scheduler is sub-optimal for the type of io required by dedicated database servers as the frequent context switching leads to queries taking longer, perhaps 5-15% longer than when using a single queue FIFO disk io scheduler like Noop or deadline.
Noop: The Noop disk io scheduler is a FIFO (First In, First Out disk io scheduler that operates on a single queue). It operates, in order, on disk io requests and allows that disk io request to run until it completes before moving on to the next disk io request. One downside of this is that if you have some truly monster requests like a full table scan of a 1TB table, your other normally quick queries will be delayed. This Noop disk io scheduler is the best choice when the Linux OS is a virtual machine as the host OS is usually configured with the deadline scheduler, so the downside I discussed is mitigated.
deadline: The deadline disk io scheduler is Noop disk io scheduler with deadlines attached to disk io so that starvation of io requests doesn’t occur. It also sorts io requests by sector and bunches them together to speed reads for multiple io requests. In the above example of a full table scan of a 1TB table, if there are pending read or right requests near their expiration deadlines (500ms for reads, 5s for writes), the expiring request is a prioritized run. This is ideal for database loads and is the recommended disk io scheduler for Oracle and MySQL databases on dedicated hardware. On virtualized hardware, use Noop instead.
How to determine which disk io scheduler you’re using as root, ignoring the loopback devices we see that the disks this server has are sda, sdb, and sr0:
# ls /sys/block/
loop0 loop1 loop2 loop3 loop4 loop5 loop6 loop7 sda sdb sr0
Now, let’s find out which disk io scheduler is in use for each device:
# cat /sys/block/sda/queue/scheduler
noop deadline [cfq]
# cat /sys/block/sdb/queue/scheduler
noop deadline [cfq]
# cat /sys/block/sr0/queue/scheduler
noop deadline [cfq]
Changing the disk io scheduler is a two-part process.
Part 1: First, we change it on the running system and then we make it persistent across reboots:
root@SH-MYSQL01:~# echo “deadline” > /sys/block/sda/queue/scheduler
root@SH-MYSQL01:~# echo “deadline” > /sys/block/sdb/queue/scheduler
root@SH-MYSQL01:~# echo “deadline” > /sys/block/sr0/queue/scheduler
We can recheck this by using the cat commands again:
# cat /sys/block/sda/queue/scheduler
noop [deadline] cfq
# cat /sys/block/sda/queue/scheduler
noop [deadline] cfq
# cat /sys/block/sda/queue/scheduler
noop [deadline] cfq
Part 2: Next, we need to make this persistent for reboots. We do this by editing the /etc/sysconfig/grub and add elevator=deadline to the line starting GRUB_CMDLINE_LINUX or GRUB_CMDLINE_LINUX_DEFAULT:
# vim /etc/sysconfig/grub
Change GRUB_CMDLINE_LINUX_DEFAULT=”quiet splash” to GRUB_CMDLINE_LINUX_DEFAULT=”quiet splash elevator=deadline”.
Then, you need to apply the changes to the grub boot updater with update-grub2:
# update-grub2
Disk Mount Options
ext4
Most clients choose to use the default Linux disk file system ext4 with the default options. Some clients will use xfs, zfs, or another disk file system, and while there are ways to tune those, this article focuses on tuning for the ext4 file system. First, you should know that the defaults for the ext4 file system make sense for a general-purpose server running on low-end commodity hardware. I recommend the following changes to the defaults:
notime: regardless of the hardware, not writing last access time to files speed read and write io. This increases performance by a few percent.
nobarrier: use this only if the disk has battery backup or is ssd. No barrier turns off writes to the disk barrier before they’re written to disk. Inexpensive and old hardware need this in case of crashes, but most Enterprise level hardware has battery backup or is ssd and turning off the barrier increases performance by at least 3%.
data=writeback: The default mode is data=ordered. Data=ordered buffers the data for sequential writes, which slows down writes. This is the safest option but is only needed on non-journaled file systems like ext2. Linux Torvalds recommends this be set to data=writeback, which writes directly to the disk without buffering for sequential writes. There’s no real-world difference between the two on reads, but my testing showed a 4.6% increase in disk io writes with data=writeback.
Change mount options:
Setting the disk mount options is a two-step process. First, we set them on the live file systems, then we make these persistent across reboots:
Part 1: We use the mount command with the remount option to set these new options on the live file systems, example:
# mount -o remount -o noatime,nobarrier,data=writeback /myDB
And view changes with the mount -v command
Part 2: We edit /etc/fstab and replace the defaults with the above options:
# vim /etc/fstab
So we change:
/dev/sda2 none swap defaults 0 0
to:
/dev/sda2 none swap rw,noatime,nobarrier,data=writeback 0 0
With that completed, your swap, IO scheduler, and mount options are configured and ready to deliver excellent performance for a MySQL or Oracle database.