How long will it take to rename table

How long will it take to rename a table with a size of 219 GB?

This is the specification of our server:

• Memory: 65 GB

• Available disk space: 266 GB

MySQL Ver 14.12 Distrib 5.0.77

Sorry for the confusion. Our plan is to rename a MySQL table with size of 219 GB.
How long will it take? Do we need to have a large disk space for this migration?

Table's engine: InnoDB.

Renaming a table in MySQL does not require a temporary table.

There are two statements that perform an equivalent operation.

RENAME TABLE t1 TO t2;
ALTER TABLE t1 RENAME TO t2; # as long as no other options to ALTER are also specified

Renaming a table acquires a metadata lock on the table which requires that no statements be running against the table, no transactions have row locks, and no transactions are holding on to consistent snapshots of any MVCC versions of the table.

Either style of RENAME operation will attempt to acquire the metadata lock and subsequently-started statement impacting the table will block, waiting for the pending metadata lock.

If you use ALTER TABLE tbl_name RENAME TO new_tbl_name without any other options, MySQL simply renames any files that correspond to the table tbl_name without making a copy.

— http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

Note that's a link to the 5.0 documentation, and the 5.1, 5.5, 5.6, and 5.7 all have the same information.

The major delay you could see will come from other sessions preventing the RENAME from starting because the locks can't be immediately required.

If you are able to execute a FLUSH TABLES table_name; statement and have it return within a few seconds, that should serve as confirmation that the rename operation will complete in a similar amount of time, since it will need the same locks in order to remove any entries in the query cache related to the table, close the table, and clean up some internal structures like the table share object... the table is automatically reopened by the next statement that accesses it... so it gives you a good test of what to expect and would be a good idea to run first, since it's typically going to make the actual rename operation slightly shorter by leaving less work for the rename to do.

SHOW FULL PROCESSLIST; while waiting for the FLUSH to complete (if there's a delay) will give you an idea of things that might get in the way of the rename operation, and you can safely KILL the flush if you need to.

StackExchange Database Administrators Q#53836, answer score: 20