MySQL performance with SSD-backed SWAP?

[KTWalrus]

Has anyone done any performance testing for MySQL testing what effect using a fast SATA III SSD for the system SWAP space on transaction throughput?

That is, configure a test system with 8GBs of RAM and 56GBs of SWAP (on a dedicated SSD).  The test system should have a second SSD for holding the entire test database (hopefully, much larger than 64GBs).  And, then configure MySQL to run a benchmark so that it uses around 48-64GBs of virtual memory for the duration of the benchmark.  Then, install 64GBs of RAM (disabling SWAP) in the test system and rerun the benchmark.  Finally, go back to 8GBs of RAM, disable SWAP, and rerun the benchmark so that MySQL only uses around 8GBs of virtual memory and the database SSD.  

This would result in 3 sets of performance data which should indicate whether using SWAP on a dedicated boot SSD would be any faster than just using a fast SSD to hold the database.

My thinking is that SWAP (aka virtual memory) might be noticeably faster than just using an SSD for the database.  And, I wonder how much slower SWAP on SSD is than all RAM.  For my servers, RAM is around $10/GB but SSD storage is around $1/GB.  Rather than adding the expensive RAM to squeeze out more performance, I'd like to expand the DB servers with SWAP on SSD.

Maybe this is a dumb idea, but I haven't read anything about using SWAP on SSD for servers.  Most articles I have read on setting up DB servers discourage having any SWAP at all, but I'm thinking SSDs might be fast enough to use for virtual memory over paying 10 times as much to load up the servers with RAM.  I know my iPhone uses SSD for virtual memory and only has a small amount of RAM, so I'm hoping this isn't a dumb idea.

[Bill Karwin]

Using an SSD-backed swap partition does not seem like a net benefit.  SSD is faster than spinning drives, but RAM is still orders of magnitude faster in both access time and throughput.

Ideally you should not be swapping at all.  I understand your point about the price per GB having a ratio of 10:1, but I believe the relative penalty to performance for swapping will have a greater ratio.  It's false economy to rely on swap because it's cheaper.

Swap is not simply an extended area of system memory, it's used as a "safety net" to avoid out-of-memory fatal errors when your processes overallocate virtual memory and it can't fit in physical RAM.  Not all the memory is in demand at all times, so the least in demand memory is put onto swap temporarily.  Not only is the swap device much slower than RAM, but there's overhead for the kernel to do the swapin/swapout operations.  In other words, even if you could somehow put your swap partition on a ramdisk, it would not be a good idea; you'd be better off just using that RAM to expand system memory.

The following article recommends putting your swap partition on a conventional drive, even if you use an SSD for your Linux partition.

http://apcmag.com/how-to-maximise-ssd-performance-with-linux.htm

Here's the relevant statement:

Linux is pretty good at only using swap if it really needs to, but even so if you're installing to an SSD and you have a mechanical hard drive in your system, be sure to put the swap partition on it instead of the SSD.

Regards,

Bill Karwin

[KTWalrus]

I've decided that this is a promising cost saver for db servers.  I'm going to standardize on each db server having limited RAM, but 2 SSDs in RAID 0 for OS/SWAP/DB Logs and 1 SSD for the DB.  Since I use dual socket servers which take DIMMs in pairs, the servers will have 4 x 8GB DIMMs ($350) and 3 128GB SSDs ($350).  The SWAP space will be configured at 2 times the DB size on disk.  The entire 3rd SSD will be for the MySQL database.  Logs (including the replication binary logs) will be placed on the RAID SSDs.

The result should be massively capable DB servers with the storage system only costing $700 for serving a DB under 100GBs.  If the DB grows past 100GBs, the 3 SSDs can be swapped out for 256GB SSDs (for less than $700 and probably much less since SSD prices per GB are declining rapidly).

I'm thinking that this set up will give fast SWAP (since IOPs are split between 2 SATA III drives).  I believe that mostly the SWAP will be used to read in the DB files into memory and no real page outs will happen (the writeable virtual memory to support MySQL will entirely fit in 32GBs of RAM).  Therefore, SWAP will be mostly used as a READ cache (I have to figure out how Linux Kernel can be made to store non-dirty pages in the SWAP space to get this to work - like a RAM disk).  The DB SSD will only see writes for changes to the DB (with the entire DB read into SWAP on start up of MySQL).  Other writes (like for log files) will go to the fast RAID SSDs.  I think this will balance the server so that it takes advantage of the 3 separate SSDs to their maximum.

[KTWalrus]

BTW, this is all contingent on benchmarking the db server to show that using OS SWAP on SSD is faster than using MySQL's built in cacheing of read only db pages is less efficient than the OS virtual memory system backed by SSD storage.  

If MySQL's built in virtual memory is as efficient, then the only advantage is that the reads are mostly on the RAIDed SSDs and the writes are mostly done on the DB SSD.  

I'm thinking OS SWAP (configured properly) will out perform MySQL's virtual memory system (fewer memory block copies).

On Saturday, July 21, 2012 9:41:46 PM UTC-4, KTWalrus wrote:

[Alfie John]

Before thinking about using an SSD for swap, maybe you should consider if you want swap at all! If you haven't heard of this before, it's called the Linux/MySQL "swap insanity" problem:

  http://blog.jcole.us/2010/09/28/mysql-swap-insanity-and-the-numa-architecture/

Have a read from the links in that post by Jeremy. Some interesting ones are:

  http://blog.jcole.us/2012/04/16/a-brief-update-on-numa-and-mysql/

  http://www.mysqlperformanceblog.com/2008/04/06/should-you-have-your-swap-file-enabled-while-running-mysql/

  http://mysqldba.blogspot.com.au/2008/05/linux-64-bit-mysql-swap-and-memory.html

  http://feedblog.org/2009/02/14/the-middle-path-and-the-solution-to-linux-swap/

  http://don.blogs.smugmug.com/2008/05/01/mysql-and-the-linux-swap-problem/

If you do go down the path of having swap, SSD would seem to be better compared to spinning rust. But then again, going off Don's solution, a 14Mb RAM disk would seem faster :)

I haven't done the numbers so maybe it's worth performing some tests and writing up your results. I'm sure lots of people would be interested to see an update to this issue (given that most of the above posts are from 2008/2009/2010).

Alfie

[Tim Chadwick]

I exactly echo Bill and Alfie's sentiment.

I manage many large mysql instances on Linux.  I'd argue that if you're gearing hardware for SWAP, you're already in a bad place.  The memory usage on a database box should be stable.  If you have volatile processes on the box, budget for another machine to get them off of there.  If it is mysqld that is jumpy, then you probably have the buffer pool over provisioned and should pare down.  If these are not options to you, SSD might make sense - but I'd consider the RAM disk first.

Also, setting swappiness to 0 can help some.

~tim

--
You received this message because you are subscribed to the Google Groups "Percona Discussion" group.

To view this discussion on the web visit https://groups.google.com/d/msg/percona-discussion/-/U5Xcp_P0zY0J.

To post to this group, send email to percona-d...@googlegroups.com.
To unsubscribe from this group, send email to percona-discuss...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/percona-discussion?hl=en.

[Peter Zaitsev]

Hi, 

Every few years someone comes up with this "wonderful" idea.   When fast flash such as FusionIO came out this was one of ideas... for MySQL as well for other memory hungry applications such as memcache.

It does not work.

It specially does not work for databases because they are designed very carefully in regards to what data is in memory and what needs IO to be brought from disk.    Trying to "cheat" database make it to use

memory focused algorithms on data which is actually on disk causing a lot of trouble... including holding important latches while pages are being paged in/out. 

You're welcome to do some benchmarks though I would be very interested to see what you get.  I expect results a lot lower and especially more unstable than using memory as innodb buffer pool. 

--

You received this message because you are subscribed to the Google Groups "Percona Discussion" group.

To view this discussion on the web visit https://groups.google.com/d/msg/percona-discussion/-/T8CFxlPPx2EJ.

To post to this group, send email to percona-d...@googlegroups.com.
To unsubscribe from this group, send email to percona-discuss...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/percona-discussion?hl=en.

--
Peter Zaitsev, CEO, Percona Inc.
Tel: +1 888 401 3401 ext 501   Skype:  peter_zaitsev
24/7 Emergency Line +1 888 401 3401 ext 911