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.sqlIf 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. The value you most likely need to adjust for your server is “innodb_buffer_pool_size”.
innodb_buffer_pool_size = 128M
innodb_additional_mem_pool_size = 10M
innodb_log_file_size = 16M
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 0When changing innodb_log_file_size you will need to:
- Stop the MySQL server
- Move away ib_logfile0 and ib_logfile1 (On Debian they are in /var/lib/mysql)
- Change the innodb_log_file_size
- Restart the MySQL server
- Confirm new ib_logfile0 and ib_logfile1 has been created
- 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-innodbIf 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 = innodbMyISAM 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
- MySQL Engines: MyISAM vs. InnoDB | Tag1 Consulting, Inc.
- InnoDB Performance Tuning | Tag1 Consulting, Inc.
- How to Convert MySQL from MyISAM to InnoDB Using a Script « Ryan’s Tech Notes
- Performance tuning tips for Drupal 7 testing. | testing.drupal.org
- There is no place like 127.0.0.1: Differences Between innodb_data_file_path and innodb_file_per_table
Comments
Is it really faster?
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
Did you optimise MySQL for
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.
Re: Did you optimise MySQL for
No, I did almost no other changes - so that might explain it. Thanks for the tip, I will investigate further.
You haven`t converted
You haven`t converted search_* tables to InnoDB engine.
Why?
Performance reasons and to avoid the large index
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.
Backups change!
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!
This will effect a lot of
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.
Post new comment