Bug #102044 Contribution by Facebook: Make InnoDB FSEG_FILLFACTOR dynamic
Submitted: 21 Dec 2020 22:44 Modified: 22 Jun 2021 15:46
Reporter: FBContrib Admin Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:8.0.17 OS:Any
Assigned to: CPU Architecture:Any

[21 Dec 2020 22:44] FBContrib Admin
Background innformation provided by Facebook:

* segment_reserve_factor determines the ratio of empty pages, the last
segment in an ibd can have before innodb creates a new extent. If a new
page is needed and the last segment has less than
segment_reserve_factor * (current number of pages in this segment)
empty pages, then a new extent is created and the new page is allocated
from the new extent. The empty pages are useful when B-tree grows so
that pages in the upper level of the B-tree can be allocated
contiguously. An extent is 64 pages, and a segment is 256 extents.

Before this diff, this variable was not configurable and it was
determined by 1/x where x was the value defined by the compile time
than 1/8th pages were empty, then InnoDB would allocate a new extent
leaving many empty pages in the current extent. This caused lots of

The ideal value for this variable needs investigation. The current
default will have the previous behavior. A segment has 16384 pages, so
for eg. If we are okay with leaving at most 16 empty pages per segment,
then we can set innodb_segment_reserve_factor=0.001.

innodb_segment_reserve_factor.test loads data into two identical tables
but sets a lower segment_reserve_factor before loading data to the
second table. This makes the second table about 10% smaller than the
first table.

The default value for this variable is set to 0.01 which should mean 1%
of the pages will be allocated for growth.

Use case:
The default factor of 8 allocates too much storage and overhead for us. Making this adjustable allows us to reduce the number of pages allocated. Otherwise, we see 10% more flash storage needed for our database, which is significant in cost.

Repo: https://github.com/mysql/mysql-server
Patch on top of 8.0.17: https://github.com/mysql/mysql-server/commit/4869291f7ee

How to repeat:
See description

Suggested fix:
See contribution code attached
[22 Jun 2021 15:42] Daniel Price
Posted by developer:
Fixed as of the upcoming 8.0.26 release, 

The new innodb_segment_reserve_factor system variable permits configuring
the percentage of tablespace file segment pages that are reserved as empty

Thanks to Facebook for the contribution.