Bug #64673 InnoDB allocates too many empty pages in ibd files

Description:
When innodb needs to allocate a new page fseg_alloc_free_page_low() is called. This function either returns a page that's already on the ibd file, or extends the ibd file and returns a page. In doing so, it considers the current ratio of empty pages to the reserved pages on the last segment, and if the segment is too full, it creates a new extent and allocates the page from there. This fullness threshold is currently controlled by the compile time define FSEG_FILLFACTOR (it's actually 1/FSEG_FILLFACTOR) which is currently 8. This wastes too much space. Currently some of our tables have around 13% empty pages. It would be better to make this a dynamic variable. I was able to get the empty page percentage down to 3% by setting this reservation factor to 0.002. I am still measuring performance. 
Can you comment on what may be the possible ramifications of this change in terms of performance?

How to repeat:
read code.

Suggested fix:
diff --git a/storage/innodb_plugin/fsp/fsp0fsp.c b/storage/innodb_plugin/fsp/fsp0fsp.c
index f600e96..624e737 100644
--- a/storage/innodb_plugin/fsp/fsp0fsp.c
+++ b/storage/innodb_plugin/fsp/fsp0fsp.c
@@ -165,10 +165,10 @@ typedef   byte    fseg_inode_t;
 
 #define FSEG_MAGIC_N_VALUE     97937874
 
-#define        FSEG_FILLFACTOR         8       /* If this value is x, then if
+double fseg_reserve_factor = 0.125;    /* If this value is x, then if
                                        the number of unused but reserved
                                        pages in a segment is less than
-                                       reserved pages * 1/x, and there are
+                                       reserved pages * x, and there are
                                        at least FSEG_FRAG_LIMIT used pages,
                                        then we allow a new empty extent to
                                        be added to the segment in
@@ -2630,7 +2630,7 @@ fseg_alloc_free_page_low(
                ret_page = hint;
                /*-----------------------------------------------------------*/
        } else if ((xdes_get_state(descr, mtr) == XDES_FREE)
-                  && ((reserved - used) < reserved / FSEG_FILLFACTOR)
+                  && ((reserved - used) < fseg_reserve_factor * reserved)
                   && (used >= FSEG_FRAG_LIMIT)) {
 
                /* 2. We allocate the free extent from space and can take
@@ -2652,7 +2652,7 @@ fseg_alloc_free_page_low(
                ret_page = hint;
                /*-----------------------------------------------------------*/
        } else if ((direction != FSP_NO_DIR)
-                  && ((reserved - used) < reserved / FSEG_FILLFACTOR)
+                  && ((reserved - used) < fseg_reserve_factor * reserved)
                   && (used >= FSEG_FRAG_LIMIT)
                   && (!!(ret_descr
                          = fseg_alloc_free_extent(seg_inode,
diff --git a/storage/innodb_plugin/handler/ha_innodb.cc b/storage/innodb_plugin/handler/ha_innodb.cc
index ccb9bd3..3af41b0 100644
--- a/storage/innodb_plugin/handler/ha_innodb.cc
+++ b/storage/innodb_plugin/handler/ha_innodb.cc
@@ -12030,6 +12030,15 @@ static MYSQL_SYSVAR_BOOL(log_compressed_pages, srv_log_compressed_pages,
   " makes InnoDB assume that the compression algorithm doesn't change.",
   NULL, NULL, FALSE);
 
+static MYSQL_SYSVAR_DOUBLE(segment_reserve_factor, fseg_reserve_factor,
+  PLUGIN_VAR_OPCMDARG,
+  "If this value is x, then if the number of unused but reserved"
+  " pages in a segment is less than    reserved pages * x, and there are"
+  " at least FSEG_FRAG_LIMIT used pages, then we allow a new empty extent to"
+  " be added to the segment in fseg_alloc_free_page. Otherwise, we"
+  " use unused pages of the segment.",
+  NULL, NULL, 0.125, 0.0003, 0.4, 0);
+
 static MYSQL_SYSVAR_UINT(comp_fail_samples,
   srv_comp_fail_samples, PLUGIN_VAR_OPCMDARG,
   "Number of page size samples collected from pages that fail to compress to"
@@ -12551,6 +12560,7 @@ static struct st_mysql_sys_var* innobase_system_variables[]= {
   MYSQL_SYSVAR(malloc_cache_len),
   MYSQL_SYSVAR(compress_malloc_cache_len),
   MYSQL_SYSVAR(decompress_malloc_cache_len),
+  MYSQL_SYSVAR(segment_reserve_factor),
   NULL
 };
diff --git a/storage/innodb_plugin/include/fsp0fsp.h b/storage/innodb_plugin/include/fsp0fsp.h
index 7abd391..6397a89 100644
--- a/storage/innodb_plugin/include/fsp0fsp.h
+++ b/storage/innodb_plugin/include/fsp0fsp.h
@@ -34,6 +34,8 @@ Created 12/18/1995 Heikki Tuuri
 #include "page0types.h"
 #include "fsp0types.h"
 
+extern double fseg_reserve_factor;
+
 /**********************************************************************//**
 Initializes the file space system. */
 UNIV_INTERN

Thank you for the problem report and patch contributed.

This was too hard for me to find ...

Without this change there are too many pages in state FIL_PAGE_TYPE_ALLOCATED

Mark, any share information for the performance with this patch?

Posted by developer:
 
This is related to:

[Bug#32312743: CONTRIBUTION BY FACEBOOK: MAKE INNODB FSEG_FILLFACTOR DYNAMIC](https://mybug.mysql.oraclecorp.com/orabugs/site/bug.php?id=32312743)

[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)