Optimizing MySQL for ALTER TABLE of InnoDB
- by schuilr
Sometime soon we will need to make schema changes to our production database. We need to minimize downtime for this effort, however, the ALTER TABLE statements are going to run for quite a while. Our largest tables have 150 million records, largest table file is 50G.
All tables are InnoDB, and it was set up as one big data file (instead of a file-per-table).
We're running MySQL 5.0.46 on an 8 core machine, 16G memory and a RAID10 config.
I have some experience with MySQL tuning, but this usually focusses on reads or writes from multiple clients. There is lots of info to be found on the Internet on this subject, however, there seems to be very little information available on best practices for (temporarily) tuning your MySQL server to speed up ALTER TABLE on InnoDB tables, or for INSERT INTO .. SELECT FROM (we will probably use this instead of ALTER TABLE to have some more opportunities to speed things up a bit).
The schema changes we are planning to do is adding a integer column to all tables and make it the primary key, instead of the current primary key. We need to keep the 'old' column as well so overwriting the existing values is not an option.
What would be the ideal settings to get this task done as quick as possible?
