Speeding up database-bound tests with MySQL

In my day job we use MySQL a lot, and we also like to pretend that we have tests.

It's a good idea to run tests using the same database you're using in production, but MySQL ain't exactly a speed demon compared to memory-backed databases like Hypersonic, H2 or even Derby.

But wait - we can have our cake and eat it, too!

The trick is to tell MySQL to store its data files in memory; more specifically, the InnoDB files should be stored on a memory-backed filesystem like tmpfs.

So, create a directory somewhere (I use /var/lib/mysql/innodb) and mount tmpfs on it (mount -t tmpfs none /var/lib/mysql/innodb, or set it up in /etc/fstab).

After that, tell MySQL to store the InnoDB files there by sticking the following lines in /etc/mysql/my.cnf:


Now restart MySQL, and run your now-speedier tests!


Dan Dukeson said...

Nice idea, do you have any figures on the actual speedup that you achieved?

espenhw said...

It's not really scientific, but for one set of database-bound integration tests total runtime for the test set went from 48 to 31 seconds; that's a speedup of ~35%.

Adam Monsen said...

Great idea, thanks! The "tests" for the project I'm working on (Mifos) are also insanely database-intensive, and I was able to reduce test time by placing MySQL data on a tmpfs partition.