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.