How can I speed up my PHPUnit + DBUnit test suite execution?

Question

I'm running in to some real speed issues with PHPUnit/DBUnit. Anything that extends PHPUnit_Extensions_Database_TestCase takes forever to run. With 189 tests, the suite takes around 8-9 minutes. I was kind of hoping it would take 30 seconds at most ;-)

It looks like restoring the database to its initial state is the process that takes the time, so we've made our datasets as small as possible and limit the number of tables we require for each test case. I am using fixtures and sharing as much as possible.

Are there any settings or modifications I can use to speed the execution up? Looking at what the MySQL server is doing throughout the tests it seems that lots of truncate/inserting is happening, but surely it would be faster to pack the test data sets into temporary tables and then simply select into them for each test?

The driver I'm using is PDO/MySQL with an XML test dataset.

Answer 1

Upon Googling I've managed to reduce the time it takes from 10 minutes down to 1 minute. It turns out that changing some InnoDB configuration settings in my.ini/my.cnf will help.

Setting innodb_flush_log_at_trx_commit = 2 seems to do the job. After you change it, restart your MySQL server.

More on dev.mysql.com: innodb_flush_log_at_trx_commit

The setting controls how ACID compliant the flushing of the logs is. The default value is 1 which is full ACID compliance which means

the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file.

With a value of 2, the following happens:

The log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it.

The key difference here is that because the log isn't written out at every commit, an operating system crash or power outage can wipe it out. For production, stick to a value of 1. For local development with a test database, the value of 2 should be safe.

If you're working with data that will be transferred to the live database, I would suggest sticking with the value of 1.