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.
Check swappiness:
cat /proc/sys/vm/swappiness
Temporarily change swappiness:
sudo sysctl vm.swappiness=10
Permanently change swappiness:
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:
vm.swappiness=10
Save and reboot