MySQL swapping with Fsync

2 min read

Jun 28, 2018 12:00:00 AM

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.

[sylvester@host~]$ 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.

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

MySQL swapping with Fsync

Conclusion

Share this

Share this

More resources

Backup Oracle databases to AWS S3

Exploring Backup/Restore Comparison Using MySQL Shell Utility vs. Percona XtraBackup Utility vs. MySQLDump

Are You Switching to BULK_LOGGED Recovery Model? Know Your Risks.

Ready to unlock value from your data?