MySQL Bugs: #102044: Contribution by Facebook: Make InnoDB FSEG

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:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	8.0.17	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Background innformation provided by Facebook:
Abstract:

* 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
fragmentation.

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

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
pages. 

Thanks to Facebook for the contribution.

Documentation:
https://dev.mysql.com/doc/refman/8.0/en/innodb-file-space.html#innodb-config-reserved-file...

Posted by developer:
 
This is related to:

[Bug#MySQL Bugs: #64673: InnoDB allocates too many empty pages in ibd files](https://bugs.mysql.com/bug.php?id=64673)

[WL#14521 InnoDB: Introduce dynamic system variable to configure file segment fill factor](https://wl.mysql.oraclecorp.com/worklog/InnoDB-RawIdeaBin/?catname=Server-Sprint;tid=14521)

and more recently to

[Bug#MySQL Bugs: #89158: "inplace" table rebuild creates much larger ibd file than "copy" rebuild!](https://bugs.mysql.com/bug.php?id=89158)