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
1 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/
(Read More..)
