Alter a large mysql table 700 gb

Question

The production table I want to alter is around 700 Gb. I want to remove a FK from that table. Mysql version is 5.1.61-log.

I found following options:

pt-online-schema-change: alters a table’s structure without blocking reads or writes. This looks most efficient in terms alteration time. This also takes care of server load by dividing table copy into chunks and pauses data copy process in case of high load on the server.

The only problem with tool for me is available space on server is 450 GB and there is no room for another drive. This tool works on a copy of the table which is around 700 GB.

Upgrade to Mysql 5.6: With ONLINE DDL feature of 5.6, most of the ALTER types won’t block writes to a table that is being changed. Also it supports Add/Drop a foreign key constraint without the need of full table copy.

But I am not sure how it takes care of server load and how much time it going to take for alteration.

Is there any other option like pt-online-schema-change which doesn't have to copy table? Any experience/comments on ONLINE DDL of Mysql 5.6?

Thanks in advance!

Answer 1

A little late, but here we go...

If you can't add more disk space and can't promote a replica I think your only option (short of adding more disk space) is upgrading to MySQL 5.6 and using the online alter feature. The third-party tools that I know of all require a second copy of the table to be created before performing the swap. As you mentioned, the good news is that as per the Summary of Online Status for DDL Operations dropping a FK constraint should not require a full copy to be made.

I've had moderate success using the online alter feature but there are a couple caveats to be aware of:

• There is no throttling of the operation and this can heavily load the server
• Due to the above this can cause large replication spikes to replicas
• Adding/dropping a column can cause primary key errors

The last caveat doesn't apply in this situation but it's a big gotcha.

I'd also be sure to carefully read the known limitations of the online alter feature too.