Posted on 8 Comments

Converting all tables in a db to another engine

Happens a lot these days, like when people (finally) discover the virtues of InnoDB. Of course there’s more to it than just the conversion, for instance some server tuning will be required also.

Anyway, there are a few methods:

  1. Most MySQL installations will have the mysql_convert_table_format script (Perl) included. The script does not contain an option to tweak server parameters though. For instance, when converting to MyISAM (as an intermediate step for moving to innodb_file_per_table), index building will be much faster if you have a large myisam_sort_buffer_size setting. So you’d have to either tweak the script or make that change globally (SET GLOBAL myisam_sort_buffer_size=1024*1024*1024 for 1G). I prefer to have such changes local to a setting as they can be dangerous, but in this case it should be ok since this particular buffer is only used for alter table and repairs.
  2. You could write a stored procedure to use information_schema and dynamic queries. But unless you’re stuck with the way you can access the server, the above method should suffice!

What I actually often do when dealing with such conversions, is extract the table names and prepare a script. Then I look at the table structures and the slow query log, and tweak the indexes. Removing duplicates/useless ones, adding some composite/covering indexes where appropriate, etc. After all, an alter table requires the entire table to be copied, so adding/removing/sanitising indexes at the same time as converting the engine type saves hassle.

Note: contrary to what I wrote earlier, you can’t do the bulk change with phpMyAdmin; I thought you could but Marc Delisle from the project corrected me (see comments).

Posted on 8 Comments

8 thoughts on “Converting all tables in a db to another engine

  1. Hi Arjen,
    please explain me how to do this with phpMyAdmin; do you mean using the “With selected” menu? Engine change is not one of the possible choices.

    Marc Delisle

  2. Thanks Marc, and of course you’re right.
    I was pretty sure I’d seen it, but I can’t find it anywhere.
    Feature request? 😉

  3. mk-find –engine MyISAM –exec ‘ALTER TABLE %D.%N ENGINE=InnoDB’

    What could be easier 🙂

  4. Oh, very good!
    Was looking at maatkit-options earlier but didn’t see this one 😉
    Great

  5. Arjen,
    we considered it but rejected the feature, due to performance issues. ALTER TABLE being a blocking statement, I imagine the server being not so happy about doing this for all tables.

  6. Diddums for OPTIMIZE TABLE, but phpMyAdmin does support that for all tables?

  7. Yes it does, so we might have to change that. At least we never got a complaint about this one. Maybe people did not try “check all” and “Optimize” on huge tables.

  8. Well I don’t know the internals of the server, but the doc says “While ALTER TABLE is executing … updates and writes to the table are stalled until the new table is ready.”

    For OPTIMIZE TABLE, the doc does not use the word “stalled”; but of course for InnoDB, OPTIMIZE TABLE is mapped to ALTER TABLE.

Comments are closed.