Mysql – Convert MyISAM to InnoDB

innodbmigrationmyisamMySQLpercona-toolkit

What are the considerations needed when converting myisam to innodb.

I have a table around 80 GB with engine myisam. I would like to convert into innodb. What are the considerations needed to convert.

I'm using percona MySQL. I checked the table indexes and there is no full text searching indexes for the above table.

I plan to use percona online scheme change.

What are the considerations/pre-requisite to keep in mind to do this activity?

Best Answer

  1. InnoDB tables tend to occupy more space than MyISAM tables do.
  2. While converting, new physical files are being created, so you have to have free space on the disk (Hard to estimate, but to be safe I would say 120G)
  3. If you use Percona tools to make the change online, all modifications on the original table will be stored in a temporary table using triggers. So depending on the DML operations,i.e. inserts/updates/deletes, you may need more space. Be careful that on high traffic tables, triggers may affect the performance.
  4. It could be that some InnoDB related configuration should be tuned.

If in your setup you have two or more servers, like master-master, or master-slave(s), I think it is better to do it offline, one server at a time.

Related Question