MySQL Forums
Forum List  »  Newbie

Re: mysqldump and import to de-fragment tables
Posted by: Rick James
Date: January 13, 2011 10:07PM

First question is whether it is necessary.

Assuming that all your tables are InnoDB, the next question is whether you have innodb_file_per_table turned on. That is, is each table in an .ibd file, or is it part of a huge ibdata1 file?

InnoDB reuses blocks that are freed up. It does not, however, return blocks to the OS. So, only if you have deleted a lot of stuff, will there be many blocks to return to the OS. Run SHOW TABLE STATUS and look in the last column.

Well, returning blocks is different than "defragmenting". Defragmenting is essentially never needed for InnoDB; the blocks quickly get jumbled anyway. And the OS does not keep files from being fragmented. So, I would argue that it is useless (in Unix/Windows with MySQL) to fret over defragmentation.

Back to you. Which of these operations do you want to do, and why? Do you have any supporting data to indicate the need? After that, we can discuss the details of how best to do it. (Hint, you are not 'way off'; your ideas involving the Slave are pretty good.)

Options: ReplyQuote


Subject
Written By
Posted
Re: mysqldump and import to de-fragment tables
January 13, 2011 10:07PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.