Altering the data type of a column in a HUGE table. Performance issues

Question

I want to run this on my table:

ALTER TABLE table_name MODIFY col_name VARCHAR(255)

But my table is huge, it has more than 65M (65 million) rows. Now when I execute, it takes nearly 50mins to execute this command. Any better way to alter table?

Nope, it's going to be slow. An ALTER statement is a one time action. Wait it out and hope you don't need to do it again. — Michael Berkowski, Jun 12 '12 at 11:33

score 5 · Accepted Answer · answered Jun 12 '12 at 11:41

5

Well, you need

 ALTER TABLE table_name CHANGE col_name new_name VARCHAR(255)

But, you are right, it takes a while to make the change. There really isn't any faster way to change the table in MySQL.

Is your concern downtime during the change? If so, here's a possible approach: Copy the table to a new one, then change the column name on the copy, then rename the copy.

You probably have figured out that routinely changing column names in tables in a production system is not a good idea.

answered Jun 12 '12 at 11:41

O. Jones

103,626
17
118
172

Yes. I can't afford to have downtime of 50mins, since all the rows will be locked during this which will prevent other actions(CRUD) on this table. – Kiran Jun 12 '12 at 11:49
Uh. oh. That means, if you copy the table, new and changed rows will come up in the old table while you are reorganizing the new one. If this were my project, I would now be trying to decide how important it is to change this column name. – O. Jones Jun 12 '12 at 11:51
yes. I can do one thing. Announce the downtime of 1hr and make this changes when the load is less. Probably at midnight. However, thanks for your inputs. – Kiran Jun 12 '12 at 11:53
3

Announce two hours of downtime. That way you won't annoy everybody, but rather delight them when you get done early with your downtime. – O. Jones Jun 12 '12 at 11:54

score 4 · Answer 2 · answered Nov 26 '15 at 15:18

4

another variant to use percona toolkit https://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html

answered Nov 26 '15 at 15:18

gayavat

18,910
11
45
55

score 0 · Answer 3 · edited May 23 '17 at 10:30

You can deal with schema change without downtime using Oak.

oak-online-alter-table copies schema of original table, applies your changes and then copies the data. The CRUD operations can still be invoked as oak puts some triggers on original table so no data is going to be lost during the operation.

Please refer to other question where author of oak gives detailed explanation about this mechanism and also suggests other tools.

Altering the data type of a column in a HUGE table. Performance issues

3 Answers3