5

I am running PHPUnit to test a CodeIgniter application using CIUnit (a third party interface between the two). A number of the tests select data from an empty MySQL database that is populated with 5-10 records in setUp(). On Windows and the web server (Ubuntu 10.04/Apache 2.2/MySQL 5.1/PHP 5.3), the 105 tests run in 2-3 seconds with a memory usage of around 30mb. On my local (Ubuntu 12.04/Apache 2.2/MySQL 5.5/PHP 5.3), the 105 tests run with the same memory usage, but take approx 45 seconds.

I have narrowed down the slowness to tests which utilise the database; are there any configuration settings that I am possibly missing that are making the tests run 15 times slower? If not, would my best bet be to try downgrading MySQL, or maybe even Ubuntu (I have already tried downgrading from 12.10 to 12.04)?

Any answers much appreciated.

Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
jr1242
  • 53
  • 1
  • 3
  • Can you run the tests verbosely, so you can see which one(s) are slow (i.e. more specific that 'use the database')? – halfer Feb 16 '13 at 20:08
  • Have you tried to attach a `strace -c` to the mysql or the php process during the test run? Maybe the db is running low on memory with the default my.cnf. – complex857 Feb 16 '13 at 20:16
  • does the test script create the test DB tables when the tests run, or is it running against an existing table? If the latter, are the indexes the same across both systems? – Spudley Feb 16 '13 at 20:48
  • @halfer The database queries are very basic. The tests which 'use the database' are just running a select on a table with at most 10 records and at most 2/3 joins. So the queries should be extremely fast. Then the actual methods that are being tested just populate the returned data into an array. – jr1242 Feb 16 '13 at 21:03
  • The other aspect which could be slow is the population of the table (which answers @Spudley's question); I believe CIUnit truncates the (pre-existing) table and populates it from an array when setUp() is run. Again, this should be fast - 3 seconds is how long I'd expect it all to take. – jr1242 Feb 16 '13 at 21:04
  • @complex857 I have limited Linux experience.. but I ran an strace -c on the PID of the MySQL process, ran phpunit, and the time it reported was about half a second. I have tried tinkering with the memory settings in the MySQL conf but to no avail. – jr1242 Feb 16 '13 at 21:06
  • In my experience, running the `truncate` query makes the difference. When starting a transaction in `setUp` and just rollback in `tearDown` it's much faster. CIUnit afaik doesn't touch the database on it's own. I convinced that it's IO causing the slowdown. On my strace outputs the `io_getevents` takes the majority of mysql time. (`strace -c -f -p `). – complex857 Feb 16 '13 at 21:40
  • OK, I agree they should be fast, but my point was that you could run the tests verbosely and time each one - so you can be sure that it is database access that is causing the slow-down. Once you have isolate slow tests, you can add `microtime` measurements inside the test. – halfer Feb 16 '13 at 21:43
  • @halfer, or just use xdebug to generate [profiling info](http://www.xdebug.org/docs/profiler) of the php process. – complex857 Feb 16 '13 at 22:01

2 Answers2

6

You are most likely running into the performance hit created by barriers being default on in the ext4 filesystem. Read more of them here:

Here's what they do from the docs:

barrier=<0|1(*)>
the jbd code. barrier=0 disables, barrier=1 enables. This also requires an IO stack which can support barriers, and if jbd gets an error on a barrier write, it will disable again with a warning. Write barriers enforce proper on-disk ordering of journal commits, making volatile disk write caches safe to use, at some performance penalty. If your disks are battery-backed in one way or another, disabling barriers may safely improve performance.

You can try to remount your filesystem without them like this (use the mount point where the mysql data files are living)

mount -o remount,nobarrier /

In my environment this makes a Tests: 83, Assertions: 194 suite's runtime reduce from 48 seconds to 6.

complex857
  • 20,425
  • 6
  • 51
  • 54
  • You're my new favorite internet person! My test suite took 32 minutes before running this, and only 9 minutes after. I still find it rather long for a test, but this is a glorious decrease indeed. – Daan Oct 11 '17 at 13:00
  • Thanks. Now 2022 and the answer is still valid. The test runtime reduces from 1m25s to 5s – Meirza Sep 28 '22 at 07:42
1

I was able to significantly speed up my PHPUnit tests by following this suggestion [1]:

If you are using the InnoDB engine (the default on Fedora), try putting this in your my.cnf database configuration:

[mysqld]

...

innodb_flush_log_at_trx_commit=2

...

then restart your server.

It does seem to reduce the reliability of your database, IF you get a power loss WHILE writing etc. It was definitely an acceptable rick for my development machine, but I wouldn't recommend it for production. See more details about the reliability at [2].

[1] http://aventinesolutions.nl/mediawiki2/index.php/PHPUnit:_a_Quick_Way_to_Speed_Up_Test_Suites?goback=.gde_1685627_member_107087295

[2] http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

Community
  • 1
  • 1
nicholas.capo
  • 11
  • 1