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!

15 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.

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

Tech Institute said...

Top quality blog with very informative information found very useful thanks for sharing and hopping for some more informative posts.
Data Analytics Course Online

Data Science Training said...

I will very much appreciate the writer's choice for choosing this excellent article suitable for my topic. Here is a detailed description of the topic of the article that helped me the most. PMP Training in Hyderabad

Data Science said...

Great article with valuable information found very resourceful and enjoyed reading it waiting for next blog updated thanks for sharing.
typeerror nonetype object is not subscriptable

Data Science Training said...

I want to thank you for your efforts in writing this article. I look forward to the same best job from you in the future. PMP Certification in Hyderabad

Cyber Security said...

Nice Information Your first-class knowledge of this great job can become a suitable foundation for these people. I did some research on the subject and found that almost everyone will agree with your blog.
Cyber Security Course in Bangalore

Cyber Security Course said...

Writing in style and getting good compliments on the article is hard enough, to be honest, but you did it so calmly and with such a great feeling and got the job done. This item is owned with style and I give it a nice compliment. Better!
Cyber Security Training in Bangalore

TECHNOLOGY said...

I will very much appreciate the writer's choice for choosing this excellent article suitable for my topic. Here is a detailed description of the topic of the article that helped me the most.
unindent does not match any outer indentation level

Techprograms said...

I'm glad I found this blog! Occasionally, students want to know the keys to writing productive literary essays. Your first-class knowledge of this great job can become a suitable foundation for these people. Good
unindent does not match any outer indentation level python

EXCELR said...

Im really impressed by it.I am happy for sharing on this blog its awesome blog I really impressed. thanks for sharing. Great efforts. data science training