2

Here's more info on the table that needs to be altered:

  • 15GB size MyISAM table with Index around 5GB
  • Over 100 million rows
  • MySQL 5.1 on RHEL 5 with 4GB RAM
  • Currently live online

What's the best way to add a couple of new column (minimum 2 columns) to a large table like this? With minimum downtime. Just using direct ALTER table commands would take hours if not a day or two to finish.

Additional question: Will it affect the length of time needed to add new columns if the new column default value are set to NULL instead of having a 'pre-defined' value?

7

To make online changes to a table you can use pt-online-schema-change

    pt-online-schema-change --alter "ADD COLUMN c1 INT" D=sakila,t=actor

The table will be blocked for a few seconds when it switches new and old tables. The table though must have a primary keys and not have triggers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.