One problem that’s a lot less common these days is swapping. Most of the issues that cause swapping with MySQL have been nailed down to several different key configuration points, either in the OS or MySQL, or issues like the swap insanity issue documented by Jeremy Cole back in 2010. As such, it’s usually pretty easy to resolve these issues and keep MySQL out of swap space. Recently, however, we had tried all of the usual tricks but had an issue where MySQL was still swapping.

The server with the issue was a VM running with a single CPU socket (multiple cores), so we knew it wasn’t NUMA. Swappiness and MySQL were both configured correctly and when you checked the output of free -m it showed 4735M of memory available.

[sylvester@host~]$ free -m
              total        used        free      shared  buff/cache   available …

In this blog, I’ll look at the impact of swapping on MySQL performance. 

It’s common sense that when you’re running MySQL (or really any other DBMS) you don’t want to see any I/O in your swap space. Scaling the cache size (using

innodb_buffer_pool_size

 in MySQL’s case) is standard practice to make sure there is enough free memory so swapping isn’t needed.   

But what if you make some mistake or miscalculation, and swapping happens? How much does it really impact performance? This is exactly what I set out to investigate.

My test system has the following:

• 32GB of physical memory
• OS (and swap space) on a (pretty old) Intel 520 SSD device
• Database stored on Intel 750 NVMe storage

To simulate a worst case scenario, I’m using Uniform Sysbench Workload:

sysbench …

The behavior of vm.swappiness was always a bit confusing for novice linux users, as setting vm.swappiness to 0 would not completely disable swapping in the system during a memory crunch. vm.swappiness would only affect the agressiveness of swapping.

Following upstream commit tried to give more control to parameter. This commit tried to avoid swapping completely when vm.swappiness is set to 0.

commit fe35004fbf9eaf67482b074a2e032abb9c89b1dd
Author: Satoru Moriya
Date: Tue May 29 15:06:47 2012 -0700
mm: avoid swapping out with swappiness==0

With above commit, setting vm.swappiness to “0” instructs the kernel not to initiate swapping until the amount of free and file-backed pages is less than the high water mark in a memory zone. In other words, it tries to reclaim as much memory that can be reclaimed, before swapping starts.
This greatly reduced the chances of swapping.

When this …

Swapping has always been something bad for MySQL performance but it is even more important for HA systems. It is so important to avoid swapping with HA that NDB cluster basically forbids calling malloc after the startup phase and hence its rather complex configuration.

Probably most readers of this blog know (or should know) about Linux swappiness setting, which basically controls how important is the file cache for Linux. Basically, with InnoDB, since the file cache is not important we add “vm.swappiness = 0″ to “/etc/sysctl.conf” and run “sysctl -p” and we are done.

Swappiness solves part of the swapping issue but not all. With Numa systems, the picture is more complex and swapping can occur because of a memory imbalance between the physical cpus, the sockets and not cores. Jeremy Cole explained this here and …

MySQL, like a lot of other software, has many knobs you can tweak. Most of these knobs may affect behaviour, but more importantly most affect the memory usage of the server, so getting these settings right is very important.

Most of MySQL’s memory is really used just as a cache, in one form or another, information that otherwise is on disk. So ensuring you have as large a cache as possible is important. However, making these memory sizes too large will trigger the server to start swapping and possibly can cause it to crash or cause the kernel to kill the process when it runs out of memory.  So that’s something we want to avoid.

Certain settings affect memory allocation on a per connection/thread basis, being bounded by thread_cache_size and max_connections.  If you configure for the worst behaviour (max_connections) you may end up not actually using all the memory you have available, memory which normally could be …

Various people have complained about Linux swapping unexpectedly on boxes running mysqld, when apparently mysqld was not using all the memory and there was quite a bit of free memory available.

There’s also an article by Jeremy Cole. However, his solution requires a one-line change to mysqld_safe which while it’s small does not work very well if you have to maintain a large number of servers and are using packages asa package upgrade will overwrite the modified file mysqld_safe and then restart mysqld with the unmodified script. This leads to the need to repatch the script and then restart mysqld. Not very helpful.

So I was looking for another solution and came up with this option which basically requires a minor change to /etc/my.cnf and the use of a small shell wrapper script. The change to my.cnf is simply to …