Should you have your swap file enabled while running MySQL ?Peter Zaitsev
So you’re running dedicated MySQL Linux box with plenty of memory, so the good question arises if you should have swap file enabled or disable it ? I’ve seen production successfully running on boxes both with and without swap file so it is not the question of you must do it this or that way but rather understanding advantages of both approaches.
I also would like to hear what you do yourself, and why 🙂
The rationale behind disabling swap is what there is nothing you want to swap out on such box anyway and if you disable swap file kernel will not swap and possibly will be able to manage memory smarter knowing it does not need to look for pages to swap out or balance memory for reducing a cache or swapping something out.
And indeed if you run with swap disabled you would not have the issue of swapping happening on the box as there is just nowhere to swap.
So what is about enabled swap – if we assume the kernel is smart (as we did for second case) we will be able to save a bit of memory as there are some programs which are started but never never really used. These would be first valid candidates to swap out. Second if you have swap file you get a bit more flexibility. What happens if you miscounted something or there is a gotcha in your application and you ended up with a lots of connections creating large temporary tables and so MySQL running out of memory (and getting killed by kernel). It could not even be MySQL but some script cron script or something similar with same affect.
In practice there are additional issues for both of the configurations – if you keep swap enabled you may have hard time keeping MySQL in memory because Kernel would love to swap it out. Recent kernels become much better than few years ago but I still run in workloads which expose bad kernel behavior and swap buffer space out even with /proc/sys/vm/swappiness =0. You can lock MySQL in memory by using –memlock option but it opens its own can of worms.
If you keep swap disabled you can run into another problems. A lot of code in the kernel assumes there is a swap space and I’ve seen kernel starting to behave crazy when there is memory pressure and there is nothing to swap out. This may actually mean the buffer pool (or other caches) which you can safely use can be less if you have swap file disabled. Though I must note again this can be workload and kernel version specific.
Besides general considerations there are many case specific ones. For example if you would rather spend more memory (allowing more reserve for spikes) than to have slowdown caused by swapping activities in case memory overcommit occurs you may be better off without swap file. If on the contrary you want to use as much memory as possible to get last bit of performance and do not mind slowdowns if you mixedup as well as have tools to resolve them quickly (ie killing runaway queries) keeping swap file enabled may be right for you.
Myself I tend to keep swap enabled on MySQL server but of course ensure there is not swapping happening (si/so columns in VMSTAT are zero or close to zero).
If you want to keep swap file disabled that also fine but you should make sure your workload is stable so there are no large spikes in memory requirements, as well as you have processes and discipline to make sure it is kept stable.