Should you have your swap file enabled while running MySQL ?

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.

Chris Rigby

Where abouts in the script? I had a brief look in /etc/init.d/mysql but there’s alot of ulimit commands in various function, I didn’t want to mess with any of them or possibly override something I shouldn’t.. I’d be grateful if you could provide a snippet around the ulimit command you’ve put in.

Ok, so the way we do it on our production machines may seem a little crazy, but here it is:

– We don’t create a swap partition on disk – On boot, we create a small RAM disk and create a swapfile on it

Why on earth do we do this? Well, we *definitely* don’t want Linux swapping the DB out onto disk. On even the most recent kernels, with the vm tuneables set properly, this can still occur and when it does, it really really sucks.

But, as Peter said, the kernel sometimes does some crazy things when there’s no swap available. We’ve seen kswapd start thrashing and chew up most of a CPU core even though there was plenty of free RAM available. Some kernel developers have told me that you need to have at least a handful of MBs available, so we tested it to see using our RAM disk method.

Sure enough, the problems went away and we are only using a tiny amount of RAM (like 32MB or something like that) for swap.

Yes, I realize how crazy using RAM->RAM disk->swap file->swap sounds, but it works.

I alway enable the swap partition on a production machine MySQL or not. I don’t make a dedicated partition though, I just make a file on disk. As long as there is plenty of memory in the system, it shouldn’t dig into swap unless it really needs it.

You don’t want to get caught with no memory or swap and have the dreaded OOM killer start killing off things.

Just make sure your swapiness is set correctly (default of 60 should be good) and a little bit of swap should be fine. I have machines up for several years that haven’t yet touched swap.

Mike Wallace

We definitely keep swap enabled, as much as anything as a safeguard against *something* going haywire, chewing through all of our system memory, and bringing down the machine. Our current production DBs aren’t in any danger at the moment – 5 of 8G free in the kernel, mysql using 35% continuously, but on other (non-database) machines, we’ve seen rogue processes eat all 16G RAM, all 16G swap, and boom, down go the machines.

If things do start running outside normal parameters, alerts go off – I like the idea that if things go nuts enough, the swapfile can slow things down enough to allow us at least a chance of recovery.

safari

Actually, we’ve never got swap out to disk with our Mysql servers:-D (Hugepages in our mysql case helps to reduce context switching a lots) But I once experienced the problem happened on an Oracle DB server, and hugepages helped to resolve it.

QQLinux

Jens-Petter Salvesen

I prefer to turn off swap and keep relatively low settings for the MySQL caching (key cache, speficially). That way, disk sectors get cached in OS memory (both key pages and data pages, according to what we access at the time) and you can set the sort buffers etc to fairly high values without risking running out of memory.

I always prefer to have swap enabled on my boxes and just keep a check on it. I do believe that if swapping (or any other unwanted issue) is happening, the first place one should look is at the application and not the server.

As safari mentioned, large-pages always come handy.

Clint Byrum

On our big central DB server, we only have swap enabled because of tmpfs usage for tmpdir. We use a 20G tmpfs partition for tmpdir, and while we generally do have about 20G of RAM available, making tempfiles go quite fast, it also must be used for connection buffers/etc… if the server gets a lot of concurrent connections, I’d rather have it swap out the tempfiles and service the requests, than crash MySQL, or lower max_connections and have to give a ‘max connects reached’ error.

On a somewhat OT note, I see a lot of mention of large_pages here. I’ve spent the last hour trying to make them work on our new server that has 64G RAM… I keep getting these when starting though..(innodb_buffer_pool_size=31G):

safari

Set memlock (max locked memory) for mysql user in /etc/security/limits.conf to allow this user using such large of memory. After doing that and reboot the server, if you still get the error, try to restart mysqld then you can see it works.

Clint Byrum

Awesome. That worked well, thanks safari. Reboot was not necessary to test the setting btw, just had to run ‘ulimit -l xxxx’, though I rebooted to make sure it stuck. Somebody really should write the same kind of article for MySQL.

Are there any benchmarks published for large_pages? Seems like it would help a lot for a high degree of concurrency given MySQL’s history of memory allocation bottlenecks.