Not the answer you need?
Register and ask your own question!

Percona MySQL swapping

CybexCybex EntrantInactive User Role Beginner
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(R) Xeon(R) 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?

Comments

  • RingdingcoderRingdingcoder Contributor Current User Role Beginner
    Why do you think that this is a problem?
  • CybexCybex Entrant Inactive User Role Beginner
    because it slows down MySQL obviously?
    Check this also: http://www.mysqlperformanceblog.com/2013/04/19/swap-insanity-tome-iii-the-write-cache/
  • spgriffinjrspgriffinjr Entrant Inactive User Role Beginner
    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.
  • CybexCybex Entrant Inactive User Role Beginner
    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.
  • RingdingcoderRingdingcoder Contributor Current User Role Beginner
    Have you measured an adverse effect of swapping?
  • CybexCybex Entrant Inactive User Role Beginner
    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?
  • RingdingcoderRingdingcoder Contributor Current User Role Beginner
    It only degrades performance when the stuff that gets swapped out is actually used again later, which is not always the case.
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    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)?
  • CybexCybex Entrant Inactive User Role Beginner
    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
    
  • RingdingcoderRingdingcoder Contributor Current User Role Beginner
    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.
  • CybexCybex Entrant Inactive User Role Beginner
    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.nemesscott.nemes MySQL Sage Current User Role Patron
    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.
  • CybexCybex Entrant Inactive User Role Beginner
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.