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.
From: MySQL Performance Blog
Related posts:
- Great tips to improve your MySQL performance Some great articles on how to improve the performance of...
- The 20 best practices when using MySQL Just came across this great article with 20 best practices...
- How to connect to MySQL with SSH ...
- How to compress your InnoDB Backup Read the complete article on the MySQL Performance Blog Playing...
- Some cool PHP classes to make a developers life easier! I’ve collected some great PHP classes which could really help...
Related posts brought to you by Yet Another Related Posts Plugin.
[...] Read this article: The reason why swapping is bad for MySQL performance | JortK.nl [...]