patternsqlMinor
Best way to add a new column to a large table? (MySQL - MyISAM)
Viewed 0 times
newcolumnwaymysqllargemyisamtableaddbest
Problem
Here's more info on the table that needs to be altered:
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?
- 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?
Solution
To make online changes to a table you can use pt-online-schema-change
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.
pt-online-schema-change --alter "ADD COLUMN c1 INT" D=sakila,t=actorThe 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.
Code Snippets
pt-online-schema-change --alter "ADD COLUMN c1 INT" D=sakila,t=actorContext
StackExchange Database Administrators Q#60570, answer score: 8
Revisions (0)
No revisions yet.