Percona MySQL swapping

[Cybex]

Dear community,

I have an issue with MySQL swapping. I tried a lot of things to make sure it doesn’t swap and read lot’s of articles, but I can’t seem to solve the problem. Could you please point me in the right direction?

Info about the server:
Processor: 2x Intel® Xeon® CPU E5-2650 (8 cores)
Memory: 64GB
Storage: 2x SSD 250GB
Software: Percona Server (GPL), Release rel33.0, Revision 611

MySQL conf: http://pastebin.com/BrffKQiU

Things I tried:

Added numa-interleave to my.cnf

[mysqld_safe]
numa-interleave

I tested if it works, the result:

cat /proc/19670/numa_maps | cut -d ' ' -f 2 | uniq
interleave:0-1

So that works…

I also set vm.swappiness at 2. I didn’t set it to 0 because the server isn’t a dedicated MySQL server, it’s a webserver, mailserver, etc also.

When I run MySQL for about 10 hours it uses about 2GB swap space. How can I make sure it doesn’t do that?

[Ringdingcoder]

Why do you think that this is a problem?

[Cybex]

because it slows down MySQL obviously?
Check this also: http://www.mysqlperformanceblog.com/2013/04/19/swap-insanity-tome-iii-the-write-cache/

[spgriffinjr]

i’ve been having the same problem. I enabled vm.zone_reclaim_mode to 1 and that seems to be doing the trick. If you find another solution please post. I’m not sure how this will affect performance on the server. I’m currently working on increase the innodb buffer pool from 30G to 40G on a server with 64GB and 16 cores.

[Cybex]

I’m surprised there aren’t more replies to this topic. I’ll try the tip with “vm.zone_reclaim to 1”, maybe that will help. My InnoDB buffer pool is already 40GB and that didn’t do the trick unfortunately.

I noticed however that MySQL is using less swap after letting it run for a while. Like I said, after 10 hours the swap usage was 2GB and now it’s only 440MB. Still I’d rather have it not swap at all of course.

[Ringdingcoder]

Have you measured an adverse effect of swapping?

[Cybex]

Not measured, but noticed slowness when browsing the sites when swapping occurred. Why do you keep asking if swapping actually degrades performance? Isn’t that obvious?

[Ringdingcoder]

It only degrades performance when the stuff that gets swapped out is actually used again later, which is not always the case.

[scott.nemes]

Are there any other applications running on the same server? I.e. web server, FTP server, etc.? And what does your memory usage actually look like (free -g)?

[Cybex]

Yes, it’s a multi purpose server. It’s running apache, FTP, etc.

free -g:

total used free shared buffers cached
Mem: 62 59 3 0 0 15
-/+ buffers/cache: 44 18
Swap: 15 2 13

[Ringdingcoder]

I somehow doubt that a short period of slightly lower performance is really a problem in this setting, especially since it should not be that bad considering that you use SSD storage. It also does not sound like the MySQL server is used full throttle around the clock. If it were, it would likely not get swapped out.

[Cybex]

Okay, I’ll double check if that’s the cause. But what’s the reason for MySQL to swap then? It has more then enough memory allocated…

[scott.nemes]

Yeah with only 2GB of swap listed, I’d be surprised if this was causing an issue, especially on SSD (somewhat moot point, but a point none the less). You have your buffer pool set to about 62.5% of your total memory, which may be too high considering you are running multiple services on the same DB server that will be competing for memory. Have you done any checks to see what your buffer pool situation looks like, such as how much is actually being used (show global status – > Innodb_buffer_pool_pages_free and Innodb_buffer_pool_pages_total), what your churn is (show engine innodb status --> evicted without access)? Point being with some analysis you might be able to lower the buffer pool without any performance hit depending on your DB load.

[Cybex]

I’m not sure how to read those values. It says total 2.6M and free 2.3M. If it’s really true that there’s so much free, then it’s ridiculous that I have such a big InnoDB storage I’ll create a InnoDB pool of 20GB and see what happens.