Optimizing MySQL for ALTER TABLE of InnoDB

by

You might want to look at pt-online-schema-change from Percona toolkit. Essentially what it does is:

  • Copies original table structure, runs ALTER.
  • Copies rows from old table to newly created one.
  • Uses triggers to track and sync changes while copying.
  • When everything is finished it swaps tables by renaming both.

Works very well for single instance databases, but might be quite tricky if you use replication and you can’t afford stopping slaves and rebuilding them later.

There’s also a nice webinar about this here.

PS: I know it’s an old question, just answering in case someone hits this via search engine.

More Related Contents:

  1. MyISAM versus InnoDB [closed]
  2. How can i optimize MySQL’s ORDER BY RAND() function?
  3. Why does MYSQL higher LIMIT offset slow the query down?
  4. Is there a REAL performance difference between INT and VARCHAR primary keys?
  5. add column to mysql table if it does not exist
  6. Why is MySQL InnoDB insert so slow?
  7. MySQL stored procedures use them or not to use them
  8. MySQL: ALTER TABLE if column not exists
  9. UUID performance in MySQL?
  10. How can I speed up a MySQL query with a large offset in the LIMIT clause?
  11. Optimal MySQL settings for queries that deliver large amounts of data?
  12. How to add not null constraint to existing column in MySQL
  13. DOs and DONTs for Indexes [closed]
  14. MySql views performance [closed]
  15. MySQL performance: multiple tables vs. index on single table and partitions
  16. “SELECT COUNT(*)” is slow, even with where clause
  17. Does Order of Fields of Multi-Column Index in MySQL Matter
  18. MySQL load data infile – acceleration?
  19. Create an index on a huge MySQL production table without table locking
  20. MySQL: ALTER IGNORE TABLE ADD UNIQUE, what will be truncated?
  21. How to change MySQL column definition?
  22. Alter table if exists or create if doesn’t
  23. is php sort better than mysql “order by”?
  24. How can “set timestamp” be a slow query?
  25. Would it help to add index to BIGINT column in MySQL?
  26. ALTER TABLE ADD COLUMN takes a long time
  27. `MODIFY COLUMN` vs `CHANGE COLUMN`
  28. Mysql performance on 6 million row table
  29. Composite Primary Key performance drawback in MySQL
  30. Default sort-ordering in MySQL (ALTER TABLE … ORDER BY …;)

Leave a Comment