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

3 Answers 3

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

DISCLAIMER : Not Familiar With TokuDB Storage Engine

My guess for the growth would that data and indexes are being written contiguously. This is what InnoDB experiences when running OPTIMIZE TABLE against a table with innodb_file_per_table disabled. This makes ibdata1 grow. The Undo Logs grow with. Although I do not know TokuDB internals, optimization would lead to file growth.

SUGGESTION

Create the table from scratch. For a TokuDB table mydb.mytable do the following:

USE mydb
CREATE TABLE mytablenew LIKE mytable;
INSERT INTO mytablenew SELECT * FROM mytable;
ALTER TABLE mytable RENAME mytableold;
ALTER TABLE mytablenew RENAME mytable;

If mytable is smaller and is DB accessible, then get rid of the old one with

DROP TABLE mytableold;

Give it a Try !!!

As for TokuDB itself, I wrote an earlier post stating how the Storage Engine spends a lot of its time doing data compression (See my post for the question TokuDB not much faster than MySQL and another answer from a TokuDB Engineer). IMHO I suppose running data compression on a table already optimized may just result in more CPU and Server Load.

share|improve this answer
1  
After extensive experience with TokuDB compression I can tell you that TokuDB's compression is clearly way faster than InnoDB compression. I don't see that the storage engine "spends a lot of time doing data compression". –  Shlomi Noach Jan 11 at 17:58

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.