I am going to convert all the tables of 500MB database from MyISAM to InnoDB to see whether it will improve the overall performance of a busy Drupal 6 site. I am wondering what is the best (i.e. safest/easiest/fastest) way to do the conversion.
I've written a drush command for this a while ago.
Worked for me a year ago or so, not sure if the drush API changed since then.
You can place that in a convert.drush.inc for example in the .drush folder or execute it somehow on your site, for example with the devel execute php block. As a drush script, you can call it like this:
Warning: If someone does something with the database while these commands run, your database will be messed up completely. Unrecoverably. So, put your site into maintenance mode and make a backup before trying this! And of course, try on a development/testing site first :)
As a MySQL DBA, I trust MySQL to do the conversion by having MySQL write the script for me.
Form the Linux command run this query
The script will convert the smallest tables first. This script was also bypass any MyISAM tables that have FULLTEXT indexes.
Ater looking over the script, you can simply run it in MySQL as follows:
or if you want to see the timing of each conversion, login to mysql and run this:
This should not get messed up because a full table lock happens when the conversion is being executed.
Once all tables are converted you need to tune the MySQL settings for InnoDB usage and scale down the key_buffer.
Please read this for setting the InnoDB Buffer Pool : http://dba.stackexchange.com/questions/1/what-are-the-main-differences-between-innodb-and-myisam/2194#2194
Please read this also : What would be the optimal MySQL configuration for a Drupal 7 site?
Give it a Try !!!
|show 3 more comments|