Most MySQL deployments today run on Linux. Here are some general and straightforward strategies for optimizing MySQL on a Linux operating system. These methods can all help improve MySQL performance.
Without further ado, let’s get straight to the point.
1. CPU
Let’s start with the CPU.
If you check carefully, you might notice an interesting phenomenon on some servers: when you run cat /proc/cpuinfo, you’ll find the CPU frequency differs from its rated speed:
- #cat /proc/cpuinfo
- processor : 5
- model name : Intel(R) Xeon(R) CPU E5–2620 0 @2.00GHz
- …
- cpu MHz : 1200.000
This is an Intel E5-2620 CPU, which is a 2.00G * 24 core processor, but as we can see, the frequency of the 5th CPU core is 1.2G.
Why does this happen?
This stems from the latest CPU technology: power-saving mode. The operating system cooperates with the CPU hardware so that when the system is not busy, it reduces the CPU frequency to save power and lower temperature. This is a boon for environmentalists and the fight against global warming, but for MySQL, it could be a disaster.
To ensure MySQL can fully utilize CPU resources, it is recommended to set the CPU to maximum performance mode. This can be configured in both the BIOS and the operating system—doing it in the BIOS is better and more thorough. Due to the wide variety of BIOS types, the method for setting the CPU to maximum performance mode varies greatly, so we won’t detail the specific setup steps here.
2. Memory
Next, let’s look at what we can optimize regarding memory.
i) First, let’s look at NUMA
Non-Uniform Memory Access (NUMA) is also a recent memory management technology. It contrasts with Symmetric Multi-Processor (SMP). A simple comparison is shown below:
As shown in the diagram, we won’t go into detailed NUMA specifics here. But we can intuitively see: in SMP, the cost of accessing memory is uniform; however, under NUMA architecture, the cost of accessing local memory versus non-local memory differs. Correspondingly, based on this characteristic, the operating system allows us to set the memory allocation policy for processes. The currently supported modes include:
- —interleave=nodes
- —membind=nodes
- —cpunodebind=nodes
- —physcpubind=cpus
- —localalloc
- —preferred=node
In short, you can specify memory to be allocated locally, on specific CPU nodes, or via round-robin distribution. Unless the –interleave=nodes round-robin policy is set, which means memory can be allocated on any NUMA node, other policies will not allow Linux to allocate remaining free memory from other NUMA nodes to the process. Instead, it will resort to using SWAP. Any experienced sysadmin or DBA knows just how disastrous the performance degradation from SWAP can be.
Therefore, the simplest method is to just disable this feature.
The methods to disable it include: from the BIOS, from the operating system, or by temporarily disabling the feature when starting a process.
a) Due to the variety of BIOS types, how to disable NUMA varies greatly, so we won’t detail the specific setup steps here.
b) To disable it in the operating system, add numa=off directly at the end of the kernel line in /etc/grub.conf, as shown below:
- kernel /vmlinuz–2.6.32–220.el6.x86_64 ro root=/dev/mapper/VolGroup–root
rd_NO_LUKS LANG=en_US.UTF–8 rd_LVM_LV=VolGroup/root rd_NO_MD quiet
SYSFONT=latarcyrheb–sun16 rhgb crashkernel=auto rd_LVM_LV=VolGroup/swap
rhgb crashkernel=auto quiet KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM numa=off
Additionally, you can set vm.zone_reclaim_mode=0 to try to reclaim memory as much as possible.
c) Disable the NUMA feature when starting MySQL:
- numactl —interleave=all mysqld &
Of course, the best approach is to disable it in the BIOS.
ii) Next, let’s look at vm.swappiness.
vm.swappiness is the strategy the operating system uses to control swapping out physical memory. Its allowed value is a percentage, with a minimum of 0 and a maximum of 100, defaulting to 60. Setting vm.swappiness to 0 means trying to swap as little as possible, while 100 means trying to aggressively swap out inactive memory pages.
Specifically: when memory is nearly full, the system uses this parameter to determine whether to swap out rarely used inactive memory, or to release data cache. The cache holds data read from the disk, which, according to the principle of program locality, might be read again soon; inactive memory, as the name suggests, is memory mapped by applications but not used for a “long time.”
We can use vmstat to see the amount of inactive memory:
- #vmstat -an 1
- procs ———–memory———- —swap— —–io—- —system— —–cpu—–
- r b swpd free inact active si so bi bo in cs us sy id wa st
- 1 0 0 27522384 326928 1704644 0 0 0 153 11 10 0 0 100 0 0
- 0 0 0 27523300 326936 1704164 0 0 0 74 784 590 0 0 100 0 0
- 0 0 0 27523656 326936 1704692 0 0 8 8 439 1686 0 0 100 0 0
- 0 0 0 27524300 326916 1703412 0 0 4 52 198 262 0 0 100 0 0
You can see more detailed information via /proc/meminfo:
- #cat /proc/meminfo | grep -i inact
- Inactive: 326972 kB
- Inactive(anon): 248 kB
- Inactive(file): 326724 kB
Here we delve deeper into inactive memory. In Linux, memory can be in three states: free, active, and inactive. As is well known, the Linux Kernel internally maintains multiple LRU lists to manage memory, such as LRU_INACTIVE_ANON, LRU_ACTIVE_ANON, LRU_INACTIVE_FILE, LRU_ACTIVE_FILE, and LRU_UNEVICTABLE. Among these, LRU_INACTIVE_ANON and LRU_ACTIVE_ANON are used to manage anonymous pages, while LRU_INACTIVE_FILE and LRU_ACTIVE_FILE manage page caches. The system kernel will periodically move active memory to the inactive list based on the access patterns of memory pages, and this inactive memory can then be swapped out to swap.
Generally speaking, MySQL, especially InnoDB, manages its own memory cache and occupies a relatively large amount of memory, including a significant portion of infrequently accessed memory. If this memory is incorrectly swapped out by Linux, it will waste substantial CPU and IO resources. InnoDB manages its own cache, and having cached file data occupy memory offers almost no benefit to InnoDB itself.
Therefore, it is best to set vm.swappiness=0 on MySQL servers.
We can achieve this by adding a line to sysctl.conf:
- echo "vm.swappiness = 0" >>/etc/sysctl.conf
And use sysctl -p to apply the parameter.
III. File System
Finally, let’s look at file system optimization.
i) We recommend adding the noatime and nobarrier options to the file system mount parameters.
When mounted with noatime, the file system will not update the corresponding access time when a program accesses a file or folder. Generally, Linux records three times for a file: change time, modify time, and access time.
We can view these three times for a file using stat:
- stat libnids–1.16.tar.gz
- File: `libnids-1.16.tar.gz'
- Size: 72309 Blocks: 152 IO Block: 4096 regular file
- Device: 302h/770d Inode: 4113144 Links: 1
- Access: (0644/-rw-r–r–) Uid: ( 0/ root) Gid: ( 0/ root)
- Access : 2008-05-27 15:13:03.000000000 +0800
- Modify: 2004-03-10 12:25:09.000000000 +0800
- Change: 2008-05-27 14:18:18.000000000 +0800
Here, access time refers to the last time the file was read, modify time refers to the last time the file’s content changed, and change time refers to the last time the file’s inode changed (such as location, user attributes, group attributes, etc.). Generally, files are read much more often than written, and we rarely care about the last time a specific file was accessed.
Therefore, we recommend using the noatime option so the file system does not record access time, avoiding wasted resources.
Many modern file systems force the underlying device to flush its cache when committing data to prevent data loss, which is known as write barriers. However, our database servers’ underlying storage devices typically use either RAID cards, whose onboard batteries provide power-failure protection, or Flash cards, which have their own self-protection mechanisms to ensure data is not lost. Therefore, we can safely mount file systems using the nobarrier option. The configuration method is as follows:
For ext3, ext4, and reiserfs file systems, you can specify barrier=0 during mount; for xfs, you can specify the nobarrier option.
ii) There is also a master key for improving IO on the file system, and that is deadline.
Before Flash technology, we used mechanical disks to store data. The seek time of mechanical disks is the most critical factor affecting their speed, directly limiting their achievable IO per second (IOPS). To sort and merge multiple requests as much as possible, so that one seek could satisfy multiple IO requests, Linux file systems designed various IO scheduling strategies to suit different scenarios and storage devices.
Linux IO scheduling strategies include: Deadline scheduler, Anticipatory scheduler, Completely Fair Queuing (CFQ), and NOOP. We will not detail the specific scheduling methods of each strategy here. We will mainly introduce CFQ and Deadline. CFQ has been the default scheduling strategy since Linux kernel 2.6.18. It claims to be fair to every IO request, and this scheduling strategy is suitable for most applications. However, if a database has two requests, one with 3 IOs and another with 10000 IOs, due to absolute fairness, the request with 3 IOs must compete with the other 10000 IO requests and might have to wait for hundreds of IOs to complete before returning, leading to very slow response times. Moreover, if many more IO requests arrive during processing, some IO requests may even never be scheduled and become “starved”. Deadline, on the other hand, ensures that no single request waits in the queue for too long, preventing starvation, making it more suitable for applications like databases.
For real-time setting, we can use:
- echo deadline >/sys/block/sda/queue/scheduler
to set the scheduling strategy for sda to deadline.
We can also make it permanent by adding elevator=deadline at the end of the kernel line in /etc/grub.conf.
Summary
CPU:
Disable power saving modes
Memory:
vm.swappiness = 0
Disable NUMA
File System:
Mount system with noatime, nobarrier
Change the IO scheduling strategy to deadline.