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