Bug #13617 enhancement to fillfactor

• Submitted: 29 Sep 2005 17:52
• Modified: 14 Oct 2005 22:15
• Reporter: Rick James
• Status: Open
• Category: MySQL Server: InnoDB storage engine
• Severity: S4 (Feature request)
• OS: Any
• CPU Architecture: Any

[29 Sep 2005 17:52] Rick James

Description:
<quote>
15.14.1. Physical Structure of an Index
All indexes in InnoDB are B-trees where the index records are stored in the leaf pages of the tree. The default size of an index page is 16KB. When new records are inserted, InnoDB tries to leave 1/16 of the page free for future insertions and updates of the index records. 
</quote>

Suggested improvement...
Instead of 1/16, leave "room for one more record".  Here's the logic...

* If records are large (larger than 1/16 block), 1/16 usually leaves the block effectively full, requiring a split the next time an insert occurs.  (I assume splits are expensive and worth avoiding.)
* If records are small and inserts are rare, why waste the space?  And speed -- emptier blocks means more cache needed to hold the same number of rows.
* If records are small and inserts are frequent, it makes little difference.  You will quickly get to the steady state of an avg of 69% full.

How to repeat:
N/A -- feature

Suggested fix:
In description.

[29 Sep 2005 18:04] MySQL Verification Team

Leave room for one record is not precise enough.

With variable length records, record size within same table can vary from less then 10 bytes to more then thousand bytes.

[14 Oct 2005 22:15] Rick James

For fixed length rows, it works.
For variable length rows, you have some estimate.  Yes, it could be wrong, but I suggest it still might be right more often.