MySQL configuration for running tests faster

Question

I want to speed up testing web applications by configuring MySQL.

I create web applications with PHP and MySQL. When developing, tests are run every time files are saved. Therefore, faster execution of tests saves development time and makes me feel comfortable.

Though there are sereval ways to speed up tests, its database must be one of them. As a testing environment, all I need is quicker response. I don't need durability or efficiency of handling multiple connections.

In that case, what is the best configuration?

Answer 1

It depends on test database setup.

If the database is running all the time, you'll have to tweak settings like buffer sizes and io threads. Like this answer suggests, there are tools that can test and produce recommandations for your server setup.

In unit tests where data integrity isn't that important, you can use in memory tables (ENGINE=MEMORY) and disable INNODB's double write (innodb_doublewrite=OFF). This will reduce data integrity but increase performance.

I'm using a library for setting up a the database every time the tests are run. By using these settings, the database are running in about a second:

• innodb_fast_shutdown=2 (fast shut down)
• innodb_log_file_size=1048576 (smallest possible log size)
• innodb_data_file_path=ibdata1:10M;ibdata2:10M:autoextend (smallest possible data file sizes for quicker startup)