Change large mysql table 700 gb

Question

The production table I want to change is about 700 GB. I want to remove FK from this table. Mysql version is 5.1.61-log.

I found the following options:

pt-online-schema-change: changes the structure of tables without blocking read or write. This seems to be most effective in terms of change time. It also takes care of loading the server by dividing a copy of the table into pieces and pausing the process of copying data in case of high load on the server.

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

Upgrade to Mysql 5.6: With ONLINE DDL equal to 5.6, most ALTER types will not block writing to a table that is being modified. It also supports adding / removing foreign key constraints without the need for a full copy of the table.

But I'm not sure how he takes care of loading the server and how long it will take to change it.

Is there another option, for example pt-online-schema-change, that does not need to copy the table? Any experience / comments on ONLINE DDL from Mysql 5.6?

Thanks in advance!

Answer 1

Here is a link to the official documentation on online DDL

It is inside and does not require a copy table. I just uninstalled FK during the sysbench test (by the way, feel free to play the test on a local install of 5.6).