Performing Alter Table on Large Innodb table

Question

I've recently been thrust into the position of db admin for our server so I'm having to learn as I go. We recently found that one of our tables had maxed out the id column and needs to be migrated to bigint. This is for an INNODB table with roughly roughly 301GB of data. We are running mysql version 5.5.38. The command I'm running to migrate the table is

ALTER TABLE tb_name CHANGE id id BIGINT NOT NULL;

I kicked off the migration and we are now 18 hours into the migration, but I'm not seeing our disk space on the server change at all which makes me think nothing is happening. We have plenty of memory so no concern there, but it still shows the following message state when I run "show processlist;"

copy to tmp table

Does anyone have any ideas or know what I'm doing incorrectly? Please ask if you need more information.

Also there are no processes running against the database besides the alter table — mcsoup, Jun 19 '15 at 19:27
possible duplicate of [Changing Large MySQL InnoDB Tables](http://stackoverflow.com/questions/11450089/changing-large-mysql-innodb-tables) — Mike Brant, Jun 19 '15 at 19:31

score 1 · Answer 1 · answered Jun 20 '15 at 01:12

Yes, it will take a looooong time. The disks are probably spinning as fast as they can. (SSDs employ faster hamsters.)

You can kill the ALTER, since all it is doing is, as it says, "copying to tmp table", after which it will rename the tmp table to be the real table and drop the old copy.

I hope you had innodb_file_per_table = ON when you started the ALTER. Else it will be expanding ibdata1, which won't shrink afterwards.

pt-online-schema-change is an alternative. It will still take a loooooong time (with one extra 'o' because it will be slightly slower). It will do the job without blocking other activity.

This might have been a good time to check all the columns and indexes in the table:

Could some INTs be turned into MEDIUMINT or something smaller?
Are some of the INDEXes unused?
How about normalizing some of the VARCHARs?
Maybe even PARTITIONing (but not without a good reason)? Time-series is a typical use for Data Warehousing.
Summarize the data, and toss at least the older data?

If you would like further guidance, please provide SHOW CREATE TABLE.

Performing Alter Table on Large Innodb table

1 Answers1