Top 7 Linux Optimizations For MySQL

That’s all. Like a cake recipe. The DBA and sysadmin dream. You don’t think, you can just apply. Here are the 7 crushing tips for basic OS tunning:

Swappiness

The swappiness parameter controls the behavior of the operating system in the swap area. Swap is the process of sending memory blocks in/out from the disk. And it is well known that disks are at least a magnitude higher slow than memory access. Check the Numbers Every Programmer Should Know and you will see it.

The recommended value is 1 which means: do not swap until it is absolutely necessary for OS to be functional.

Use jemalloc memory allocator

Using jemalloc (along with Transparent Huge Pages disabled) you have less memory fragmentation, and thus more efficient resource management of the available server memory. Percona has a good blog post about how to enable/disable it.

Disable Transparent Huge Pages

There’s a number of reported cases where THP is behind VSZ(Virtual memory size) increasing further than RSS(Resident Set Size).

For Debian/Ubuntu:

For RHEL/CentOS 6 and 7

IO Scheduler

By default drives currently use: CFQ (completely fair queuing) which is geared towards shared servers or interactive desktop environments, but for dedicated database servers, the best options are “deadline” or “noop”. Percona has a great benchmark blogpost.

And to have the change persist after a reboot, you should edit your grub configuration file and add “elevator=deadline” at the end of the kernel lines.

CPU Governor

If CPU frequency is lower and MySQL chooses the core with a low clock speed then query performance will be slower. It is recommended to set the CPU governor to performance. Red Hat as a post explaining how to set it.

NUMA architecture

Non-uniform memory access (NUMA) is a memory design where an SMP’s system processor can access its own local memory faster than non-local memory (the one assigned local to other CPUs).  Here is an example of a system that has NUMA enabled:

A bit of a story. In the year 2010 MySQL (any fork) did not have proper NUMA support. This was explained in the great article by Jeremy Cole at the Swap Insanity and NUMA Architecture.

To avoid swapping you can set innodb_numa_interleave=1 under the [mysqld] section of your my.cnf file.

Filesystems mount options

Sysadmins and DBAs usually neglect the effects of having proper mount options. Setting proper options can create interesting performance improvement.

Make sure your drives are mounted with noatime and also if the drives are behind a RAID controller make sure that the battery is healthy. A proper battery-backed cache should be mount with nobarrier. Note that it is possible to remount on the fly:

Leave a Reply