The MySQL “swap insanity” problem and the effects of the NUMA architecture

The “swap insanity” problem, in brief

When running MySQL on a large system (e.g., 64GB RAM and dual quad core CPUs) with a large InnoDB buffer pool (e.g., 48GB), over time, Linux decides to swap out potentially large amounts of memory, despite appearing¹ to be under no real memory pressure. Monitoring reveals that at no time is the system in actual need of more memory than it has available; and memory isn’t leaking, mysqld‘s RSS is normal and stable.
Normally a tiny bit of swap usage could be OK (we’re really concerned about activity—swaps in and out), but in many cases, “real” useful memory is being swapped: primarily parts of InnoDB’s buffer pool. When it’s needed once again, a big performance hit is taken to swap it back in, causing random delays in random queries. This can cause overall unpredictable performance on production systems, and often once swapping starts, the system may enter a performance death-spiral.
While not every system, and not every workload experiences this problem, it’s common enough that it’s well known, and for those that know it well it can be a major headache.

The history of “swap insanity”

Over the past two to four years, there has been an off-and-on discussion about Linux swapping and MySQL, often titled “swap insanity” (I think coined by Kevin Burton). I have followed it closely, but I haven’t contributed much because I didn’t have anything new to add. The major contributors to the discussion over the past years have been:

• Kevin Burton — Discussion of swappiness and MySQL on Linux.
• Kevin Burton — Proposed IO_DIRECT as a solution (doesn’t work) and discussed memlock (may help, but not a full solution).
• Peter Zaitsev — Discussed swappiness, memlock, and fielded a lot of discussion in the comments.
• Don MacAskill — Proposed an innovative (albeit hacky) solution using swap on ramdisk, and a lot more interesting discussion in the comments.
• Dathan Pattishall — Describes how Linux behavior can be even worse with swap disabled, and proposes using swapoff to clear it, but no real solution.
• Rik van Riel on the LKML — A few answers and proposal of the Split-LRU patch.
• Kevin Burton — Discussion of Linux Split-LRU patch with some success.
• Mark Callaghan — Discussion of vmstat and monitoring things, and a recap of a few possible solutions.
• Kevin Burton — More discussion that Linux Split-LRU is essential.
• Kevin Burton — Choosing the middle road by enabling swap, but with a small amount of space, and giving up the battle.
• Peter Zaitsev — More discussion about why swapping is bad, but no solution.

Despite all the discussion, not much has changed. There are some hacky solutions to get MySQL to stop swapping, but nothing definite. I’ve known these solutions and hacks now for a while, but the core question was never really settled: “Why does this happen?” and it’s never sat well with me. I was recently tasked with trying to sort this mess out once and for all, so I’ve now done quite a bit of research and testing related to the problem. I’ve learned a lot, and decided a big blog entry might be the best way to share it. Enjoy.
There was a lot of discussion and some work went into adding the relatively new swappiness tunable a few years ago, and I think that may have solved some of the original problems, but at around the same time the basic architecture of the machine changed to NUMA, which I think introduced some new problems, with the very same symptoms, masking the original fix.


http://blog.jcole.us/2010/09/28/mysql-swap-insanity-and-the-numa-architecture/