I've got a mysql 5.1 server with a database of approximately 450 tables, taking up 4GB . The vast majority of these tables (all but 2) are MyIsam. This has been fine for the most part (don't need transactions), but the application has been gaining traffic and certain tables have been impacted because of table-locking on updates. That's the reason 2 of the tables are InnoDB now.
The conversion on the smaller tables (100k rows) don't take long at all, causing minimal downtime. However a few of my tracking tables are approaching 50 million rows. Is there a way to speed up an ALTER TABLE...ENGINE InnoDB
on large tables? And if not, are there other methods to convert minimizing downtime on these write-heavy tables?
Best Answer
Let me start by saying, I hate ALTER. It's evil, IMHO.
Say, this is your current table schema -
Here's the path I recommend -
Create a new table object that will replace the old one:
Insert all the rows from the old table by name into the new table:
Smoke test your migration:
Swap table names so that you can maintain a back up in case you need to rollback.
Proceed to regression testing.
This approach becomes more and more preferable with tables with multiple indexes and millions of rows.
Thoughts?