Converting your Drupal MySQL databas from MyISAM to InnoDB

In Drupal 7 InnoDB will replace MyISAM as the default storage engine for increased scalability and data integrity. Most big sites are already using InnoDB, drupal.org does since some time.

InnoDB is generally a better choice for Drupal so why wait for Drupal 7. Lets go ahead and convert our Drupal 6 tables to InnoDB.

As always, make sure you have backups of everything before you do this on a production site!

Convert a database to InnoDB

Here follows some commands to run on the command line that will make the conversion a breeze.

The first command I found on “Ryan’s Tech Notes”, se below. It will generate a file with sql commands to altar every table in the specified databas to InnoDB.

The second command is a Perl one liner to set the search_* tables back to MyISAM. This is done for performance reasons and to avoid the large index that InnoDB will generate on these tables.

Update 2010-01-03: According to Steve Rude at divx.com the menu_router table should also be MyISAM for best performance.

The last command executes the sql commands that converts the tables to InnoDB.

mysql -u [USER_NAME] -p -e "SHOW TABLES IN [DATABASE_NAME];" | tail -n +2 | xargs -I '{}' echo "ALTER TABLE {} ENGINE=INNODB;" > alter_table.sql
perl -p -i -e 's/(search_[a-z_]+ ENGINE=)INNODB/\1MYISAM/g' alter_table.sql
mysql -u [USER_NAME] -p [DATABASE_NAME] < alter_table.sql

If you are using Drush, and you really should, you can use these commands instead.

cd /path/to/drupal/directory
drush sql-query "SHOW TABLES" | tail -n +2 | xargs -I '{}' echo "ALTER TABLE {} ENGINE=INNODB;" > alter_table.sql
perl -p -i -e 's/(search_[a-z_]+ ENGINE=)INNODB/\1MYISAM/g' alter_table.sql
cat alter_table.sql | `drush sql-connect`

If you have a big database it can take some minutes to convert all the tables to InnoDB.

InnoDB parameters

Here are the current InnoDB settings I use for the server xdeb.org runs on, a small VPS. The values you most likely need to adjust for your server is “innodb_buffer_pool_size” and “innodb_log_file_size”.

innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 10M
innodb_log_file_size = 32M
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 0

When changing innodb_log_file_size you will need to:

  1. Stop the MySQL server
  2. Move away ib_logfile0 and ib_logfile1 (On Debian they are in /var/lib/mysql)
  3. Change the innodb_log_file_size
  4. Restart the MySQL server
  5. Confirm new ib_logfile0 and ib_logfile1 has been created
  6. Have a look in the logs to make sure everything is in order.

Make sure to comment out “skip-innodb” in your MySQL config file if it exists.


If you want to make InnoDB the default storage engine for new tables, where no engine is specified, you can add this to your MySQL config file.

default-storage-engine = innodb

MyISAM only parameters

These are MyISAM only parameters that you most likely can set to lower values to save RAM now that most tables are converted to InnoDB.

  • bulk_insert_buffer_size
  • key_buffer_size
  • key_cache_age_threshold
  • key_cache_block_size
  • key_cache_division_limit
  • read_buffer_size
  • read_rnd_buffer_size