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.
[[email protected]~]$ free -m
total used free shared buff/cache available
Mem: 16046 10861 242 16 4941 4735
Swap: 15255 821 14434
The point that needs a bit more attention here is the amount of memory being used by the OS cache. As you can see, there is a total of 16046M of physical memory available to the host, with only 10861M in use and the majority of what’s left over being used by the OS cache. This typically isn’t a problem. When requests for more memory come in from threads running in the OS, it should evict pages from the OS cache in order to make memory available to the requesting process. In this case, this did not occur. Instead, we observed that it held onto that cache memory and forced MySQL to turn to swap. But why?
As it turns out, the system in question had recently been converted from MYISAM to InnoDB and hadn’t had any server configuration set to accommodate for this. As such it was still configured for innodb_flush_method at the default value, which in 5.7 is still fsync. Both Ivan Groenwold and I have both written blog posts in regards to flush methods, and it’s been generally accepted that O_DIRECT is a much better way to go in most use cases on Linux, including this one, so we wanted to get the system in question more aligned with best practices before investigating further. As it turns out, we didn’t have to look any further than this, as switching the system over to innodb_flush_method = O_DIRECT resolved the issue. It appears that fsync causes the kernel to want to hang onto its data pages, so when innodb attempted to expand its required amount of memory, it was unable to do so without accessing swap, even with swappiness set to 0 to test.
Ever since we did the change to O_DIRECT, the OS cache usage has dropped and there have been no problems with OS cache page eviction.
Conclusion
MySQL swapping can really ruin your day and it’s something you want to avoid if at all possible. We still run into issues with swapping every now and then and want to continue to provide the community with our findings as they become available. So if you have a server that is swapping, and the OS cache isn’t making room for MySQL, and if you’re still using fsync for InnoDB flush, consider switching to O_DIRECT.
1 Comment. Leave new
If you run ZFS which does not support O_DIRECT, constrain your filesystem cache with zfs_arc_max (and make sure zfs_arc_min is lower than zfs_arc_max). The default zfs_arc_max of 0 allows zfs to take up to 50% of available memory. Also it should be noted (and the author did a fine job of stating the issue of MySQL’s requested memory going to swap). Swap itself is good and I would never run a Linux system without a little bit of swap space. Active swapping for MySQL is bad. Why is swap good? Because you don’t want active memory polluted with items that are not frequently used. Linux is actually more efficient if it can put unused or less frequently used pages into swap space. Swap “used” on a database server is completely fine. It’s the active swapping in and out that you can see in vmstat (si/so) that you want to avoid. If you see lots of swapping in and out on your MySQL server, as the author says “it will ruin your day”.