Converting your Drupal MySQL databas from MyISAM to InnoDB

frjo – 31 October, 2009 - 17:32

Tags:
convert
drupal
innodb
myisam
mysql
planetdrupal
Development

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.

#skip-innodb

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

Some good links for more reading

Comments

You – 1 November, 2009 - 04:14

I converted many tables on a site to InnoDB thinking it would be better since atleast one of the modules had many writes - tribune is a chat system where every message needs to be stored in the database.

However, my site started stuttering frequently after converting to InnoDB. converting back to MyISAM, all the sutters went away.

So I don't think InnoDB is a one size fits all solution

frjo – 1 November, 2009 - 07:46

Did you optimise MySQL for InnoDB on your server? The default settings for InnoDB will result in very bad performance in most cases. The innodb_buffer_pool_size, the most important setting, will e.g. by default be set to only 8M.

frjo – 3 November, 2009 - 12:28

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.

Tag1 Consulting and others mentioned that the search_* tables would be a better fit for MyISAM than InnoDB. Since they have many reads and few writes it makes sense to me. InnoDB also creates mush bigger indexes than MyISAM and the search_* tables already has big indexes.

Anonymous – 25 May, 2011 - 20:52

Be aware that the code you show here will in fact attempt to convert these MyISAM tables to MyISAM. If you take out the MyISAM ALTERs in the script before you run it, it will take less time.

Randy Fay – 8 November, 2009 - 03:55

One key element of the switch is its impact on your backup strategy. mysqlhotcopy does not work at all, for example. But mysqldump adds the –single-transaction argument which is beautiful.

But don't switch without considering your backup strategy!

frjo – 8 November, 2009 - 10:33

This will effect a lot of people when Drupal 7 starts to toll out. You should write some documentation about it.

Do you know if it’s safe to use the –single-transaction argument when you have some MyISAM tables mixed in?

P.S Enjoyed your “Debugging Drupal” session at DrupalCon Paris.

ลงประกาศฟรี – 25 April, 2010 - 07:00

I've changed my engine several times. First use myisam and then innodb then myisam and last use innodb. LOL

Conclusion
I think innodb is faster than myisam even many read.
My website has many read but sometime has write. It can slow often when database is wrote because of table lock.

regards,
ลงประกาศฟรี

ps. sorry for my english.

frjo – 10 July, 2011 - 13:57

MyISAM is the default for Drupal 6 and MySQL and for small and medium sites it normally works well.

If the site grows and you start getting a lot of writes to the database, new comments, new nodes etc. etc., then it can be a benefit to switch to InnoDB.