Speeding up SimpleTest on Drupal 7

Craig's picture
Comments (8)
Post a new comment
Craig
10 January, 2012 - 17:05

Ever wondered why SimpleTest runs are much slower in Drupal 7 compared to their Drupal 6 counterparts? The answer lies in the way that SimpleTest creates lots of test tables, which is slow in Drupal 7 due to changing to InnoDB as the MySQL table type default.

Our internal Drupal testing uses SimpleTest quite extensively to perform validation that we wrote our code correctly. Recently, I was tasked with porting over a Drupal 6 module to Drupal 7. After porting all of the tests for the module to the new format, I noticed that actually running the tests took about 4-5 times as long as they did on Drupal 6. When each test took several minutes in the first place, you can imagine how long the entire run was realistically taking!

Having traced through the code, it appeared that the creation of the SimpleTest 'sandbox' environment creates all of the Drupal tables again, and that this was taking a very long time. Narrowing it down, we found that in Drupal 7, tables are created as InnoDB by default, and that DDL operations on this type (i.e. CREATE / DROP / ALTER, etc) are in general a lot slower than the MyISAM type. Therefore, in order to speed the tests up, I thought 'lets use MyISAM' in our testing environment. However this turned out to be a bit tricky.

The first method, of turning off InnoDB support in MySQL, seemed like the way to go. However, with a dozen or so Drupal 7 sites on the test enviroment that were already in InnoDB format, this was impractical, as disabling the type stops those tables from functioning correctly. Whilst we could have converted the tables to MyISAM, ideally we want to leave existing tables alone, and only affect our newly-created 'Test' tables.

The second method conjured up was to simply tell Drupal 7 that any new tables we create are to be in MyISAM format. Unfortunately, Drupal 7 lacks the ability to set the option of what type to create, so we need to resort to core hacking to do this.

In DRUPAL_ROOT/includes/database/mysql/schema.inc, around line 83, Drupal defines the default table type it will create. You will note that this is 'InnoDB' by default. Changing this line to 'MyISAM' forces new table creations to adopt this format.

Making this change and re-running a 'stub' SimpleTest, that only sets up the enviroment and nothing else, reduced the runtime from 4 minutes to around 30 seconds - a vast improvement! We could further improve this in theory using transient MEMORY tables, but alas Drupal does not yet support this. Running the tables from a ramdisk would also have the same effect - we may try this at a later date and post again about what we find out!

Whilst this is fine for a development site, I would not suggest changing this on a production site, especially in light of the fact that all new tables would be created in this way (e.g. those added by new modules, etc)! InnoDB is better performing for general use, but MyISAM is more suited to the type of database usage that SimpleTest invokes.

8

Comments

RdeBoer's picture
RdeBoer10 January, 2012 - 23:34

I thought InnoDB was superior to MyISAM.

If not why did D7 change the default?

Craig's picture
Craig12 January, 2012 - 09:22
InnoDB is indeed superior for general use, that is regular queries, etc, but is a lot slower with creation/deletion of tables. It's only really this use case where MyISAM is advantageous.
Hack Twitter's picture
Hack Twitter13 June, 2012 - 15:07

Hello! This is my first comment here so I just
wanted to give a quick shout out and say I genuinely enjoy reading through your
posts. Can you suggest any other blogs/websites/forums that go over the same
subjects? Thank you!

ygerasimov's picture
ygerasimov11 January, 2012 - 05:23

There is very good manual about tuning mysql http://qa.drupal.org/performance-tuning-tips-for-D7

Best thing to speed up tests from my experience is to move mysql to memory completely (described in manual above). And this is what I have in VM.

Albert Albala's picture
Albert Albala11 January, 2012 - 21:34

I found that using SQLite on my testing environment halved the testing time.

sun's picture
sun12 January, 2012 - 20:52

http://drupal.org/node/915922 holds a pretty much ready core patch for customizing the table engine used for tests to MyISAM since late 2010.

However, my personal testing of patches on a regular dev machine showed that InnoDB vs. MyISAM ultimately didn't really make that much of a difference, so the core patch stalled.

If you're sure this makes a difference in your environment/setup, I'd be glad to see some traction on that core patch and would happily review your work. Unless @webchick objects, it should perfectly be backportable to D7.

pozycjonowanie bielsko's picture
pozycjonowanie ...6 March, 2012 - 22:20

Need to test it, I'll tell you how it was

amysite's picture
amysite2 April, 2014 - 08:45

To improve this approch you can replace the line with:
mysql_engine' => preg_match("/simpletest\d+/",$info['prefix']['default']) ? 'MyISAM' : 'InnoDB',

That's hack, I know, but i can't see another way

Add new comment