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.

Kayal said...

This was a very good thing and it is also a very interesting blog. I like your great post, Thanks for giving the valuable post. Do well!!!

Oracle DBA Training in Chennai
Oracle DBA Course in Chennai
Excel Training in Chennai
Oracle Training in Chennai
Spark Training in Chennai
Tableau Training in Chennai
Embedded System Course Chennai
Unix Training in Chennai
Oracle DBA Training in OMR

Lakshi said...

This post is really nice and informative. The explanation given is really comprehensive and informative..

Web Designing Course Training in Chennai | Web Designing Course Training in annanagar | Web Designing Course Training in omr | Web Designing Course Training in porur | Web Designing Course Training in tambaram | Web Designing Course Training in velachery

Data Science Hyderabad said...

Awesome article with top quality information and found very interesting looking forward for next blog thank you.
Data Science Course in Hyderabad 360DigiTMG