MySQL Swapping to Disk

Posted

Over the past couple of months we've been finding some of our MySQL servers swapping to disk. This behaviour is very unusual because we allocate a lot of memory to every MySQL server and run large caches to ensure the database performs as well as it can.

We were finding that MySQL consumes memory up to about 90-95% and then chunks of memory start swapping. The majority of memory usage is cache, as the swap became active, memory was made available and MySQL would cache more, unfortunately retrieving paged caches then became slow as they were sitting in a page file on disk and not in memory as we expected.

We'll detail in this article how we have resolved this after a couple of months of investigating and testing alternative options.

The Environment

As of writing this our environment was as follows:

  • MySQL 5
  • RedHat Enterprise Linux 5.4
  • Kernel 2.6.18
  • Dell 2970 with Quad-Dual Core 2.0Ghz
  • 32Gb RAM
  • Mostly INNODB with some MyISAM tables

The Disclaimer

The issue we have experienced may not be the issue you are experiencing.  What we describe here may or may not work for you, if you don't know what you are doing - seek professional advice. Don't make changes blindly without understanding the consequences, especially to production environments. 

The Problem

MySQL server swapping large chunks of data into swap and then using reclaimed memory to cache more data.

As MySQL is the only service running on these servers, we naturally assumed it to be an issue with MySQL consuming memory incorrectly or swapping way before it needed to because of an incorrect setting - how wrong we were!

With some investigation we soon discovered the issue wasn't MySQL related but was an issue with the OS itself, the system was monitoring memory usage and after reaching a certain threshold, was paging out some memory to make more available - ironically it was only making more available to MySQL. The end result was that over time, as the caches grew bigger, swap gradually increased until their was no more data to cache, unfortunately at this point much of the cache was on disk instead of in memory.

The result of this was a system running with quite a bit of free memory but an almost full swap partition and of course disk-based retrievals are much slower than memory based ones so we could see when the swap was being used for query work.

We read up extensively on this and tested out a few ideas with very little success, the only feasible one appeared to be to drop the swap partition and run without one - this is good in theory but in practice, if you miscalculate a setting or need to burst for some reason beyond your physical memory - you've got nowhere to go, we didn't want to do it.

We use our swap as a key indicator to determine if things need configuration or upgrade, when swap is used we report on this and determine whether a server requires fine-tuning, upgrade or additional servers bringing online to support load, in essence, we aim never to use swap and if we ever do we tune/upgrade.

We were stumped by this issue for a few weeks and despite finding blog post after blog post discussing it, there were never any solutions.

The Solution

Our attention had been very much on this problem within the MySQL arena however and we switched tack focussing our attentions on how swap operates.  It was this that led us to discover swappiness.

Swappiness defines how much a system can swap, the higher the number the more swapping happens, the number can be between 0 and 100.  0 is no swapping (we don't want that) and 100 is swap whenever possible thereby keeping memory clear.

You can check on the swappiness of your system by running:

/sbin/sysctl -a|grep swappiness

That will echo out the current swappiness of the system. 

In our case the swappiness of the solution was 60, that seemed very high so we set about trying lower thresholds.  We only wanted to swap whenever we really, really needed to, so that is, when the system is in dire need of memory and won't be able to get it without swapping.

To change the swappiness settings we firstly edit the file: /etc/sysctl.conf

In here add or modify the line that says something like:

vm.swappiness = 60

to the value you want, in our case we ended up with 1:

vm.swappiness = 1

Ideally you would be able to reboot in this scenario to refresh your system settings but that's not always possible, if you want to apply this update immediately without a reboot then run:

/sbin/sysctl -p

Once applied you can check your current swappiness:

/sbin/sysctl -a | grep swappiness

Conclusion

Since we applied this change to our production MySQL servers we've not seen any sign of swapping.  If the system needs to swap for any reason it can, but it's a last resort rather than a regular thing, this is much better for us as RAM is cheap and we have a lot of it so we want to get the most from it.

I highly recommend reviewing your swappiness for solutions that may consume memory for the right purposes (such as memcache servers, MySQL servers, etc) and stop them chewing at your disks, you'll experience performance gains and your users will be much happier.

Fixing MySQL

If you have a MySQL issue that you need help with - from configuration through to optimisation, we can help.  Our team are experts in configuring complex hosting environments, if you have a problem you think we could help with, fill in our project request form and we'll send back some more details or help point you in the right direction.

Comments

Grig Gheorghiu
23:12, 09/02/2010
Thanks for posting this, I ran into the exact same scenario and you guys saved me a lot of time and hair pulling!<br />
<br />
Grig
Travis Runyard
22:51, 15/09/2011
We're seeing a similar issue, and have made the changes. Hopefully I'll remember to post back if this worked.<br />
<br />
For Debian it's a little different-<br />
<br />
Check swappiness:<br />
cat /proc/sys/vm/swappiness<br />
<br />
Temporarily change swappiness:<br />
sudo sysctl vm.swappiness=10<br />
<br />
Permanently change swappiness:<br />
edit /etc/sysctl.conf with your text editor. Search for vm.swappiness and change its value as desired. If vm.swappiness doesn't exist, add it to the end of the file:<br />
vm.swappiness=10<br />
Save and reboot
shahryar
21:41, 23/09/2011
this does not work in all cases ... even after setting swappiness to 0 you can see the same problem ie. OS using swap when there is free memory or cache available in plenty

Comments are closed.