2010-02-17

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:

innodb_data_home_dir=/var/lib/mysql/innodb
innodb_log_group_home_dir=/var/lib/mysql/innodb

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

3 comments:

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.