CODEWITHSUNDEEP
mysqlsqlinnodbstorage-engines
agarwal_achhnera
agarwal_achhnera

Reputation: 2456

consequences of changing mysql storage engine while application is running in production

We have a currently running application which is using Mysql in multithreaded environment. We have approx 25 tables out of which 20 tables are using InnoDb as storage engine while 5 tables are using MyISAM as storage engine.

We are going to change storage engine of these 5 tables to InnoDB. Will it cause some issue if change it during running application

Upvotes: 3

Views: 1451

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562631

Any change you make with ALTER TABLE that requires a table-copy will lock the table. This is the most significant issue for applications. The table will be inaccessible from queries by any application.

How long this lock lasts depends on the size of the table, and the performance of your server, and the other load the server is under.

You can make changes with minimal downtime if you use an online schema change tool like pt-online-schema-change.

Another strategy is to make alterations to tables on a replica database instance. It's okay for tables to have a different storage engine on a replica than on the master. You don't care about tables being locked, it will just make replication lag behind and then when the alterations are done, replication should catch up. When that's all done, you can switch your application to use the replica, and then decommission the former master database instance.

Upvotes: 2

Related Questions