Convert your MySQL database from MyISAM to InnoDB

Before you go any further backup your database before doing any steps below. If you 'splode your database for any reason, you'll need it.

Here are the steps:

1. Shutdown MySQL

2. Move/copy/change the name of ib_logfile0 and ib_logfile1 files. (find where MySQL exists on your system - locations can vary greatly). MySQL will recreate these files when you start it up again. Not anytime you change the innodb_log_file_size parameter you will need to shutdown MySQL, move these files, and start up MySQL again.

3. Tune it up a bit
Based on a lot of searching around and benchmarking with JMeter I arrived at the setting below for running on my Macbook Pro. See the links at the end of this post for articles which can help you determine what to adjust these numbers to for other machines (ones with more RAM/CPU, for instance. The production server for this particular site ended up with 5000M setting for innodb_buffer_pool_size. So settings will, and should, vary greatly just depending).

With some more time spent fine tuning and benchmarking, and taking into account the specifics of your application, these setting could be improved upon I'm sure, but they're a decent starting point at least. These parameters go inside your my.cnf file. Anytime you edit your my.cnf file remember to restart my.sql in order to have the new parameters take affect. Finally, in addition to adding the settings below, I also lowered values for things that are more MyISAM specific like key_buffer_size.

Here are the relevant InnoDB related paremeters, which you should merge into your existing my.cnf. IMPORTANT - be sure to comment out skip-innodb parementer if it exists.

# skip-innodb needs to commented out if it already exists in your my.cnf - shown here as a reminder
#skip-innodb

default-storage-engine = innodb
innodb_buffer_pool_size = 200M
innodb_log_file_size = 100M
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 4M
innodb_additional_mem_pool_size = 20M

# num cpu's/cores *2 is a good base line for innodb_thread_concurrency
innodb_thread_concurrency = 4

#Tried the following parameters to no good effect, actually (very) small decrease in performance, thus they are commented out but document here, just because.
#innodb_data_home_dir = /path/to/mysql
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /path/to/mysql
#transaction-isolation=READ-COMMITTED

4. Time for the actual conversion of the tables
Borrowing heavily from some commandline magic from this post we'll get the database tables converted. It's worth noting that I've changed the sequence of the other steps here around because the order of them did not work for me. I've also disregarded the part about leaving certain tables MyIASM (*see notes at the end of this post for more). My database did not seem to want to be converted (via the queries found inside alter_table.sql file you'll generate) until after the my.cnf file was all setup and the steps with ib_logfile0 and ib_logfile1 were completed and MySQL had been stop and restarted. Also, remember that if it makes it easier, you can .

To generate the queries you need to run the following commands:

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

To run these queries against your database run the following command, or else take the contents of the alter_table.sql file you generate with the instructions and just many paste it in as a query in favorite MySQL interface (Sequel Pro for me, perhaps PHPMyadmin for others)

mysql -u [USER_NAME] -p [DATABASE_NAME] < alter_table.sql

After you've ran the queries check your database to make sure that it shows InnoDB as the storage engine and not MyISAM.

5. Restart MySQL and take your site for a spin.

6. Done


Subject: