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.

Was running an optimize table command against one of my tables about 11Gig in size, surprisingly used disk space grew by 2Gig immediately after optimization process finished. I would expect the optimization to reclaim a space.

What's happening?

After the optimization finished MySQL CPU utilization 2-3 times higher than it used to be with load average 18-20 on 24 core machine. CPU and load average went down slightly after few hours but still higher than usual. Any had similar experience? Thanks.

share|improve this question
    
@ZardoshtKasheff Could you answer this one ??? –  RolandoMySQLDBA Aug 15 '13 at 15:44

2 Answers 2

I've had the same experience. The solution is to rebuild the table via this command:

ALTER TABLE my_table ENGINE=TokuDB ROW_FORMAT=TOKUDB_SMALL;

Replace TOKUDB_SMALL with your favourite compression algorithm.

With InnoDB an OPTIMIZE TABLE command simply does a trivial ALTER. But in TokuDB it does not, and tablespace is not reclaimed. By forcing an ALTER with ENGINE=TOKUDB you're taking this outside TokuDB's hands, and let the MySQL server tell TokuDB to create a (really) new table.

share|improve this answer

TokuDB's optimize behavior is to dirty all nodes in all Fractal Tree indexes for a given table, which can cause the size of the file in the file system to grow because we'll keep the old version of the node around until the next checkpoint completes. This depends on how quickly the nodes are being dirtied/written to disk and how long your checkpoints take to complete. If it does happen, a second optimize table command will usually get you back to your original (or smaller) size.

The one operation I'd recommend following up with optimize table is if you decide to change your compression, as it allows for all nodes to be rewritten into the new compression format quickly.

share|improve this answer
    
since I also hit this on my first attempts with TokuDB, it might be worthwhile to produce a warning on this operation, that reads something like "You should know that an OPTIMIZE does that... and does not do that...", similarly to the warning issued by InnoDB that an OPTIMIZE simply makes for an ALTER. –  Shlomi Noach Jan 11 at 18:01

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.