Converting to InnoDB
We have a live web application that's been running successfully for 3 years. Over time, our data has become more sensitive and requires support for MySQL transactions to guarantee data to the end user.
All of our existing tables are using the MyISAM engine and we'd like to convert to InnoDB. It seems like the switch is relatively painless (just fire the queries). However, I do have a couple of questions:
- Are there any specific kinds of queries that would need to be rewritten because of the switch to InnoDB (i.e. queries that would begin to fail because of InnoDB)?
- Any recommendations for InnoDB tuning?
- How much more RAM would an entire InnoDB database consume versus a predominantly MyISAM database?
- What does InnoDB not "play friendly" with that works with ease using MyISAM, if any at all? I know full-text search doesn't exist with InnoDB.
Essentially, I don't want to crash the application because of this switch. Any pointers/tips will be much appreciated for the migration...
Thanks in advanced.
I found that via a google search, don't know how true each bit is, but before you do migrate over make sure you do a full backup of all tables before you proceed. If you have a development server, you could test whether it would work either with a copy of the live data or with a sample data set.
To convert an already existing table from one engine type to another you just need to ALTER the table and use a new engine. For example, if you have a table called "countries" which is using MyISAM to convert this you simply issue: ALTER TABLE countries ENGINE = InnoDB;
This same technique can also be used to reorganize the table when lots of INSERTs and DELETEs have taken place which leaves the table fragmented. This will defragment your tables.
In most cases the switch should be painless. If you have lots of data you may need to fine-tune some queries or add indexes as mysql can choose different optimisation paths for innodb.
One thing that comes to my head is about inserts - if you do a lot of them in a loop somewhere in your code then you really must surround them in a transaction since isolated inserts are very slow for innodb.
If you have access to the server administration then it's a good idea to turn on innodb_file_per_table before you change the engine type. If it's off then the server may never free up the hard drive space when you delete some large amounts of data in the innodb tables.