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!