So you get MySQL or other applications using too much memory on the box or OS behaving funny and using more memory for cache and pushing application to the swap. This causes swapping and causes performance problems. This much is obvious. But how bad is it ? Should you count it same as normal Disk IO as the box is having or is it worse than that ?
Swapping is going to impact your performance more than just normal IO and here are 3 reasons why. If you know more please let me know, for my taste these 3 are bad enough so I have not been looking for more.
Cache in the Swap File will multiply IO compared to just having less cache. What happens when page in cache is replaced which is swapped out itself ? First you have to find space to swap in the page (we’re speaking about memory pressure right?) which means swapping out some page. This would normally happen in background but still it has to be done. When the pages is swapped in which is second IO and finally you get page being cached read from the hard drive to the cache. This gives us 3 IOs instead of one. Nice 🙂
Flushing dirty pages or even discarding the page will cause extra IO slowing things down.
Skewing up all Algorithms The database internals algorithms are tuned for things being in memory and if they start dealing with data which is on disk they just often stop working with any reasonable level of efficiently – when database deals with on disk data it often uses different set of algorithms which are optimized to limit number of IOs or make them more sequential. Most of these were designed before SSD era. For example Insert Buffer in MySQL makes a special effort to avoid (delay) IO. If it happens to go to swap file it will more than defeat its purpose. Number of background threads are designed with assumptions they can check page statuses in buffer pool very efficiently which again stops working as soon as page accesses cause disk IO.
Escalated Locking/Latching If breaking of internal operations is not bad enough on its own lets see what swapping does for concurrent (multi CPU, multi client) processing. Database Locks/Latches are typically designed to be held for as short time as possible. The less portion of execution time thread spends holding exclusive locks the better system will scale. It is a big no-no to hold any critical locks while you’re doing disk IO as IOs take a long time. When system is swapping all of these gets messed up – when database is thinking it is taking the locks for few instructions only it can be a long while while IO completes – if this is critical lock it is possible to see everything in the system waiting on this IO, even transactions which work with data which is not in the swap file.
The bottom line: You should configure system so no swapping activity is going on during normal operations. The swap file itself may be justified – if you have some unexpected memory consumption spike you may prefer to see slowdown instead of MySQL being killed because of out of memory but do react on them promptly and do not treat such situation as normal.
So is there any option where we can allow SWAP to only work for everything else but MySQL. And what are the ways we can limit and control SWAP?
I totally agree, but sometimes the excessive memory consumption is coming from MySQL itself and is out of your (direct) control (see http://www.masterzen.fr/2009/10/15/mysql-innodb-and-table-renaming-dont-play-well/ and http://bugs.mysql.com/bug.php?id=47991).
It’s not only a matter of configuration, as I discovered myself 🙂
Peter,
Would be good to give some input why having 1.5GB is OS cached Linux may prefer to swap instead of freeing OS cache. And also how to fix it. This issues happens every so often.
Vadim,
I think that what you want is to tweak /proc/sys/vm/swapiness (more info on http://lwn.net/Articles/83588/). Set it to 0.
But Peter is sure to have a better answer.
Bye,
So, the only possible solution for that: increase virtual memory on the server?
@Pedro: Even with swapiness to zero, at some point a system still swaps 🙁
@Peter: I would be interested to hear your comments about solving this via large pages (which I don’t believe are swapped out). It’s a common recommendation for other databases, but I’ve not seen many comment on it for MySQL:
http://dev.mysql.com/doc/refman/5.0/en/large-page-support.html
http://harrison-fisk.blogspot.com/2009/01/enabling-innodb-large-pages-on-linux.html
Swapping out hashes, which are data structures with no locality of reference, creates a lot of excessive disk seeks. And hashes there are a lot inside the node heap.
# echo vm.swappiness = 0 >> /etc/sysctl.conf
# sysctl -p
will make sure the kernel decreases the fs buffer cache to a minimum before initiating swap. The system can still swap, but it is the last thing the kernel tries in a low memory situation. You want that in all your mysqld-machines and in all your httpd-machines.
CYTech,
There is “memlock” option which locks MySQL process in memory. The danger with it is increased change of MySQL Server being killed if no memory is available. It also not very commonly used and people reported problems with some Linux kernel versions.
Brice,
MySQL may have memory leaks like any software. Its best to report it and get them fixed.
Vadim,
Yes… Depending on Kernel version you can get rather “fun” swapping behavior, even with swappiness=0 kernel may decide it is better to start swapping rather than shrink cache. The silly but practical “solution” is to ensure kernel can have some space for cache as it wants. Happily we’re probably speaking about 10% of physical memory here.
@Peter,
Of cource you’re right. And I reported the bug several month ago:
http://bugs.mysql.com/bug.php?id=47991
Since it’s not an high traffic issue I’m not sure it will be fixed, though.
For Linux is easy. Just put vm.swappiness = 0
But is the same problem exists for windows server ? How to handle them ?