How to prevent swapping on a MySQL server?

Swapping occurs when system moves some data between memory and a special area on disk called swap space. The process is called swapping in or swapping out depending on the direction in which it happens. System swaps out when it makes a decision to free up some physical memory (RAM) and pushes data out to disk. It swaps in when an application needs to access data that was swapped out. MySQL is like any other application and any memory it holds can also be sent to disk. It may have severe negative impact on performance.

The foremost step to prevent swapping is ensuring that not database, not any other application can either independently or collectively use up all available memory. The peak usage may not exceed a threshold that still leaves comfortable buffer for any remaining system activity. If this condition is not met, nothing can help and swapping may occur.

Further tuning can be done on the system level. Linux comes with a configurable parameter called swappiness. Swappiness defines kernel attitude to swap. The larger the value the more willing it is to evict data pages from physical memory to a swap space on disk rather than free some memory from its own buffers. By default any Linux uses a relatively high value of sixty from the range of 0 to 100:

garfield ~ # sysctl vm.swappiness
vm.swappiness = 60

With that setting the Linux kernel feels encouraged to use the available swap. As MySQL is commonly the biggest memory consumer in the system, it is also the most likely candidate application to be hit by swapping. In such scenario, server does not even need to run out of memory per se. It is enough that kernel evaluates expanding its own caches to being more important than some of MySQL buffers. This is why on a dedicated MySQL server, swappiness should be set to zero to discourage kernel, as much as it is possible, from swapping out application memory. Changing it is simple:

garfield ~ # sysctl -w vm.swappiness=0
vm.swappiness = 0

This changes the runtime setting. To allow the change to survive system restarts, just add vm.swappiness=0 as a separate line into /etc/sysctl.conf.

Take care of your MySQL performance.

MySQL audits available from only $129 per server. Learn More
blog comments powered by Disqus