TokuDB optimize table does not reclaim disk space

Question

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.

Answer 1

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.

Answer 2

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.