All the Perl that's Practical to Extract and Report

MySQL on a ramdisk

Ovid on 2009-11-10T13:47:47

Two interesting thoughts about tests:

  • You often don't care if you lose the test information as you start fresh each time.
  • Reading from RAM is one heck of a lot faster than reading from disk.

We hired a new developer. He didn't have a strong background in what we do, but he has solid skills and an excellent academic background. In working with him yesterday, I was showing him the test suite and he mentioned that he heard it was slow. He tossed out various ideas to speed it up, but there were problems with many of them. Then he mentioned a ramdisk.

Oh!

Today we mounted a ram disk and pointed a test mysql instance at it. Our "fast" set of tests takes about 57 minutes to run. Now it takes 31. I just reniced it and started it again.

If this proves workable, we might even set up a dedicated test server just for this. If we lose power, we lose the data, but who cares? It's just for testing and we can bring it up again.

Thinking about this raises an obvious question: what would happen if we put our production database on a solid state drive? Their prices are dropping rapidly and we could get a huge performance boost on our production app with with no architectural changes.

Re: MySQL on a ramdisk

daxim on 2009-11-10T14:52:45

http://www.joelonsoftware.com/items/2009/03/27.html

tl;dr desktop usage was faster, compile time was same

SSD

mpeters on 2009-11-10T15:08:05

A lot of SSDs don't perform as well as people think they should because file systems haven't caught up with them yet. They are still trying to minimize seeks so they'll do extra processing that's unnecessary on SSDs.

How big is your working copy (the majority of your database that you use at a time, with indexes, etc)? It might make more sense just to buy a ton of RAM if you can fit most of your working copy in memory.

Re:SSD

Wonko on 2009-11-10T19:01:36

A good current gen SSD should provide a pretty reasonable boost in performance for a database. The most interesting, and my favorite, number I got out of benchmarking my Intel X25-M is that it can pull over 4000 seeks per second on my laptop. The cheap 4 disk RAID 10 in my web server only pulls a bit over 300.

The thing slowing the DB tests down isn't just the disks. Its the ACID properties of the database. I'm sure MySQL is calling sync quite a bit. If it didn't, the OS disk caching would work nearly as well as the RAM disk. Every sync is going to require at least 1, but probably more, seeks. A good SSD would help out a lot there, but not nearly as much as a RAM disk should.

I agree with mpeters. RAM is dirt cheap, buy lots of it and use RAM disks for testing.

If you can't buy enough RAM and you're testing on Linux you might want to try tmpfs. tmpfs is memory+swap backed, and even when swapped is pretty fast. I can't imagine that tmpfs has to respect the sync calls.

SSD != RAM

ziggy on 2009-11-10T15:39:39

SSD is a kind of flash, and doesn't perform at all like RAM. It's similar in that there's no rotational delay. Writes tend to be staggered so that each bit gets a comparable number of writes (which leads to much worse fragmentation, especially in write-heavy environments). You might get decent performance off of the SSD initially, but performance will likely degrade over time given a balanced or write-heavy workload.

MEMORY storage engine

mickeyc on 2009-11-10T16:19:19

You may want to look at the MEMORY storage engine for MySQL. I'm surprised you got such a huge boost. Are your tests write heavy? I assume you've already tweaked the query cache so you're not reading the same data over and over from disk?

Re:MEMORY storage engine

Ovid on 2009-11-10T16:37:17

The MEMORY storage engine does not support transactions, thus making it unsuitable for our tests. And I doubt tweaking our query cache on the test databases will do much good as, at the start of every test, we're flushing the data and loading a new set of fixtures.

TMPFS on Linux

adhoc on 2009-11-11T02:03:47

I'm not sure which 'ramdisk' you are using or which OS, so if you're on Linux I'd suggest you have a look at TMPFS. I've had some good results running large builds with make and lots of linking.

It does require copying all the data in first, but this may not be an issue if you set the db path and issue all the sql from "create database test" onwards.

Steal from the otaku

Alias on 2009-11-12T23:02:55

http://www.ramsan.com/success/ccpgames.htm

The thing you want is called a RAMSAN, and I know this because I've read a ton of developer reports from EVE Online.

Those guys have a MASSIVE performance sensitivity on their database, so they run this gigantic RAMSAN-backed database.

If you can reach serious performance savings or developer time savings (and so can justify the cost) you probably want something like that.

Of course, you may not need the size of the gear they use, but in principle you want the same thing.