MySQL Forums
Forum List  »  Partitioning

Re: Partitioning (ibd file size) vs non-partitioned file size
Posted by: Rick James
Date: February 13, 2009 12:49AM

InnoDB data is laid out in a BTree ordered by the PRIMARY KEY. It is a bunch of (about 3 million in your case) 16K blocks. Each blocks contains as many records as the engine feels like putting in it.

If you insert data into an InnoDB table in PRIMARY KEY order, it will be always adding to the last block. I think it has an optimization to fill the block nearly full (15/16ths) before allocating a new block.

If you insert randomly, it has to find the right block, possibly split it, etc. The average block will be only 69% full.

So, what happened in your case? Some guesses...

* Your original partitions were filled in PRIMARY KEY order, or nearly so. This lead to relatively compact data.

* The un-partition operation failed (for whatever reason) to build the result in PK order, leading to more wasted space.

How do those theories fit with what you did, and what the PK is, etc.?

Options: ReplyQuote

Written By
Re: Partitioning (ibd file size) vs non-partitioned file size
February 13, 2009 12:49AM

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.