Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How long will it takes to rename 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 takes? Do we need to have a large disk space for this migration?

share|improve this question
    
Renaming a table usually only takes a split second (at least with the DBMS I work with) - unless the table is locked and your rename is waiting for other transactions to complete. –  a_horse_with_no_name Nov 23 '13 at 10:17
    
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 takes? Do we need to have a large disk space for this migration? –  Katrina Nov 23 '13 at 10:23
    
@a-horse-with-no-name does the size of the table matters? –  Katrina Nov 23 '13 at 10:29
    
an RENAME In MySQL is slow command... When you execute RENAME, you cannot have any locked tables or active transactions. You must also have the ALTER and DROP privileges on the original table, and the CREATE and INSERT privileges on the new table. hard to geuss your time depens on your disk speeds (because an disk temporary table needs to be created) and yes this will comsume more diskspace.. MySQL 5.6 can do some alters very quick not sure if rename is one off them.. –  Raymond Nijland Nov 23 '13 at 13:22
1  
pastebin.com/pE9qGuGp –  jynus Nov 23 '13 at 16:11

2 Answers 2

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.

share|improve this answer
    
if 0.10 seconds is too slow for the RENAME, you can improve the performance by disabling sync_frm, at the cost of sacrificing durability in the case of a simultaneous server crash. –  jynus Nov 23 '13 at 16:41
2  
I assume that's meant as sarcasm, which is fair enough if 0.10 seconds is a problem, but the point, of course, is not about the actual rename, which is very quick, but other things like open transactions, other locks, an oversized query cache, and queries in progress can stretch it out substantially, and disrupt the application in the mean time... and the flush is a good test for this. If the flush is quick, the rename should be quick. Otherwise, disabling sync_frm is the worst idea I've heard today, but then again, it's still relatively early here. –  Michael - sqlbot Nov 23 '13 at 16:51
    
@jynus let me geuss one off your hobbies is playing russian roulette??? if you are lucky you can restore corrupted .frm from the general sql log of binary log if you had enabled them. –  Raymond Nijland Nov 23 '13 at 17:15
    
@Michael - sqlbot +1 btw for the complete explainment. –  Raymond Nijland Nov 23 '13 at 17:15
    
@Michael Sarcasm (partially) but true- everything I said is true. In fact, I've had to disable that on testing servers (not on production) with huge fsync fs problems- I think Mac had that problem some time ago when doing a lots of DDLs- common on devel. But yeah, the main problem will be metadata locks, which people do not tend to fully understand- why is my FTWRL blocked and blocking other queries? –  jynus Nov 23 '13 at 17:22

The MySQL documention see http://dev.mysql.com/doc/refman/5.6/en/rename-table.htmlabout the SQL

RENAME table a TO b

looks much worst then it accurately is because off the

When you execute RENAME, you cannot have any locked tables or active transactions. You must also have the ALTER and DROP privileges on the original table, and the CREATE and INSERT privileges on the new table.

If you read this it looks like MySQL needs to CREATE an new table b copy data from table a into table b (INSERT) and DROP the table a.

Ive analysed the source code off MySQL 5.0.77 behind the SQL command

RENAME table a TO b

And it looks like an almost instead operation because only the

  1. .frm (table definition file)
  2. .trg (trigger file)

files needs to be touched (renamed) no actual data copy between tables is needed.. This makes me wonder why you should need the INSERT privilege for this command..

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.