Take the 2-minute tour ×
Drupal Answers is a question and answer site for Drupal developers and administrators. It's 100% free, no registration required.

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.

share|improve this question
    
This doesn't seems to be a Drupal related question does it ? –  tostinni Jun 7 '11 at 1:13
2  
Not directly, but it is something that Drupal admins need to do on occasion. –  MPD Jun 7 '11 at 2:38
    
I updated my answer to use a new SQL command to filter out MyISAM tables that have FULLTEXT indexes. Please rerun all the steps from scratch using my updated answer. –  RolandoMySQLDBA Jun 12 '11 at 2:20
    
If your Drupal site is not configured to search using FULLTEXT indexes, you may want to go to all tables with FULLTEXT indexes and drop those indexes out of those tables. To find all tables having FULLTEXT indexes, run SELECT table_schema,table FROM information_schema.statistics WHERE index_type='FULLTEXT'; –  RolandoMySQLDBA Jun 12 '11 at 2:24
add comment

3 Answers

I've written a drush command for this a while ago.

<?php
/**
 * Implements hook_drush_command().
 */
function convert_drush_command() {
  $items = array();

  // the key in the $items array is the name of the command.
  $items['convert-engine'] = array(
    // a short description of your command
    'description' => "Convert MYSQL Table Type",
  );
  return $items;
}

function drush_convert_engine() {
  $args = func_get_args();
  $engine = $args[0];

  $result = db_query("SHOW TABLES");
  while ($row = db_fetch_array($result)) {
    $table = array_shift($row);
    drush_log(dt('Converting @table to @engine', array('@table' => $table, '@engine' => $engine)), 'success');
    db_query("ALTER TABLE $table ENGINE = $engine");
  }
}

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:

drush convert-engine InnoDB

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 :)

share|improve this answer
5  
Agree with Berdir, backup your site. While this operation is going on, you database will be LOCKED. If you want a module that can do it, give DB Tuner a shot. –  mikeytown2 Jun 6 '11 at 22:43
    
@mikeytown2: That's a pretty cool module :) –  Berdir Jun 6 '11 at 22:46
1  
Nice script but it seems quite overkill versus doing this directly un MySQL client. –  tostinni Jun 7 '11 at 1:13
2  
The script is exactly 5 lines of code. The rest is to integrate it with drush. There is way to do this in a single sql command in MySQL. One way or another, you have to write a script. –  Berdir Jun 7 '11 at 6:42
add comment

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

mysql -h... -u... -p... -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',db,'.',tb,' ENGINE=InnoDB;') FROM (SELECT A.db,A.tb,A.tbsize FROM (SELECT table_schema db,table_name tb,(data_length+index_length) tbsize FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) A LEFT JOIN (SELECT table_schema db,table_name tb FROM information_schema.statistics WHERE index_type='FULLTEXT') B USING (db,tb) WHERE B.db IS NULL) AA ORDER BY tbsize" > /root/ConvertMyISAM2InnoDB.sql

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:

mysql -h... -u... -p... -A < /root/ConvertMyISAM2InnoDB.sql

or if you want to see the timing of each conversion, login to mysql and run this:

mysql> source /root/ConvertMyISAM2InnoDB.sql

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 !!!

share|improve this answer
    
Roland, I tried your solution but after importing ConvertMyISAM2InnoDB.sql to the database, I get this error: "ERROR 1214 (HY000) at line 585: The used table type doesn't support FULLTEXT indexes". So should I know whether the conversion took place on some tables and how should I resolve this error? Thanks –  alfish Jun 12 '11 at 1:20
    
I was afraid that would happen. It simply means that one MyISAM table had a FULLTEXT index. Login to mysql and run if as if you want to see the timing. In other words, run source /root/ConvertMyISAM2InnoDB.sql –  RolandoMySQLDBA Jun 12 '11 at 1:33
    
I will try updating the SQL generation script to skip tables that have FULLTEXT indexes. –  RolandoMySQLDBA Jun 12 '11 at 1:36
    
In the meantime, repeat all these steps from scratch. The first line of the regenrated file has the MyISAM file with the FULLTEXT index. Simply delete that first line and rerun the script. –  RolandoMySQLDBA Jun 12 '11 at 1:54
    
Well, by running this cool conversion bash script (yoodey.com/…), I figured out that apparently the only table on my database that uses Full Text is 'search_index'. This cause the conversion to a halt but after canceling the conversion on it, the rest went on smoothly. By running source ConvertMyISAM2InnoDB.sql I could not pinpoint the culprit. Anyway I appreciate your help. –  alfish Jun 12 '11 at 3:30
show 3 more comments
up vote 1 down vote accepted
#!/bin/sh
DBNAME="your-database"
DBUSER="your-username"
DBPWD="your-password"
for t in $(mysql -u$DBUSER -p$DBPWD --batch --column-names=false -e "show tables" $DBNAME);
do
echo "Converting table $t"
mysql -u$DBUSER -p$DBPWD -e "alter table $t engine=InnoDB" $DBNAME;
done

source

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.