Permalink
Browse files

Expose the index fill factor as a configurable setting.

When extending a clustered index to the left or right, InnoDB attempts
to fill leaf pages only up to a certain percentage, at which point the
page is split. The remaining space is reserved for updating rows
belonging to the page, attempting to make it likely that an updated row
will be stored on the same page. This percentage is also know as the
index fill factor and is used to determine how much to fill leaf pages
and how much space to reserve for future expansion, with the aim of
reducing the need to split pages as the data grows. Currently, InnoDB
reserves 1/16 (6.25%) of the page for future updates of records.

A problem with this imposed fill factor is that for tables whose entries
are never updated, or whose records are of fixed size (meaning that the
record can be update in place), the reserved space might lead to
premature page splits and also cause a lot of space to be wasted. In any
case, if the pattern of insertion/modification can be determined, it
might be worthwhile to tweak the fill factor to reduce or increase the
amount of space that is reserved.

This change introduces a new variable name innodb_index_fill_factor that
can be used to control the fill factor for clustered indexes. Its value
specifies the percentage of space on a leaf page to be used, reserving
the remainder as free space. The default value is set to 94%.
  • Loading branch information...
Davi Arnaut
Davi Arnaut committed Nov 13, 2012
1 parent 39a5199 commit b5918441fb04875a57f21f77b9dc44346cb1d8e7
@@ -0,0 +1,37 @@
+#
+# Expose the index fill factor as a configurable setting.
+#
+SET @old_innodb_file_per_table = @@GLOBAL.innodb_file_per_table;
+SET @old_innodb_index_fill_factor = @@GLOBAL.innodb_index_fill_factor;
+SET GLOBAL innodb_file_per_table = ON;
+SET @innodb_page_size = 16384.0;
+# Create and populate a table.
+CREATE TABLE t1 (a INT UNSIGNED PRIMARY KEY, b INT UNSIGNED) ENGINE=InnoDB;
+# Rebuild index, 40% fill factor.
+SET GLOBAL innodb_index_fill_factor = 40;
+ALTER TABLE t1 ENGINE=InnoDB;
+SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE
+TABLE_NAME LIKE "%t1" AND PAGE_TYPE = 'index' AND
+CEIL((DATA_SIZE * 100.0) / @innodb_page_size) > @innodb_index_fill_factor;
+COUNT(*)
+0
+# Rebuild index, 80% fill factor.
+SET GLOBAL innodb_index_fill_factor = 80;
+ALTER TABLE t1 ENGINE=InnoDB;
+SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE
+TABLE_NAME LIKE "%t1" AND PAGE_TYPE = 'index' AND
+CEIL((DATA_SIZE * 100.0) / @innodb_page_size) > @innodb_index_fill_factor;
+COUNT(*)
+0
+# Rebuild index, 100% fill factor.
+SET GLOBAL innodb_index_fill_factor = 100;
+ALTER TABLE t1 ENGINE=InnoDB;
+SELECT COUNT(*) > 0 FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE
+TABLE_NAME LIKE "%t1" AND PAGE_TYPE = 'index' AND
+CEIL((DATA_SIZE * 100.0) / @innodb_page_size) > 50;
+COUNT(*) > 0
+1
+# Cleanup.
+DROP TABLE t1;
+SET @@GLOBAL.innodb_file_per_table = @old_innodb_file_per_table;
+SET @@GLOBAL.innodb_index_fill_factor = @old_innodb_index_fill_factor;
@@ -0,0 +1,54 @@
+--echo #
+--echo # Expose the index fill factor as a configurable setting.
+--echo #
+
+SET @old_innodb_file_per_table = @@GLOBAL.innodb_file_per_table;
+SET @old_innodb_index_fill_factor = @@GLOBAL.innodb_index_fill_factor;
+
+SET GLOBAL innodb_file_per_table = ON;
+SET @innodb_page_size = 16384.0;
+
+--echo # Create and populate a table.
+CREATE TABLE t1 (a INT UNSIGNED PRIMARY KEY, b INT UNSIGNED) ENGINE=InnoDB;
+
+disable_query_log;
+let $c = 512;
+while ($c)
+{
+ eval INSERT INTO t1 VALUES ($c, $c);
+ dec $c;
+}
+enable_query_log;
+
+--echo # Rebuild index, 40% fill factor.
+SET GLOBAL innodb_index_fill_factor = 40;
+
+ALTER TABLE t1 ENGINE=InnoDB;
+
+SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE
+ TABLE_NAME LIKE "%t1" AND PAGE_TYPE = 'index' AND
+ CEIL((DATA_SIZE * 100.0) / @innodb_page_size) > @innodb_index_fill_factor;
+
+--echo # Rebuild index, 80% fill factor.
+SET GLOBAL innodb_index_fill_factor = 80;
+
+ALTER TABLE t1 ENGINE=InnoDB;
+
+SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE
+ TABLE_NAME LIKE "%t1" AND PAGE_TYPE = 'index' AND
+ CEIL((DATA_SIZE * 100.0) / @innodb_page_size) > @innodb_index_fill_factor;
+
+--echo # Rebuild index, 100% fill factor.
+SET GLOBAL innodb_index_fill_factor = 100;
+
+ALTER TABLE t1 ENGINE=InnoDB;
+
+SELECT COUNT(*) > 0 FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE
+ TABLE_NAME LIKE "%t1" AND PAGE_TYPE = 'index' AND
+ CEIL((DATA_SIZE * 100.0) / @innodb_page_size) > 50;
+
+--echo # Cleanup.
+DROP TABLE t1;
+
+SET @@GLOBAL.innodb_file_per_table = @old_innodb_file_per_table;
+SET @@GLOBAL.innodb_index_fill_factor = @old_innodb_index_fill_factor;
@@ -0,0 +1,89 @@
+SET @old_innodb_index_fill_factor = @@global.innodb_index_fill_factor;
+SELECT @old_innodb_index_fill_factor;
+@old_innodb_index_fill_factor
+94
+# Default value
+SET @@global.innodb_index_fill_factor = 0;
+SET @@global.innodb_index_fill_factor = DEFAULT;
+SELECT @@global.innodb_index_fill_factor;
+@@global.innodb_index_fill_factor
+94
+# Scope
+SET innodb_index_fill_factor = 1;
+ERROR HY000: Variable 'innodb_index_fill_factor' is a GLOBAL variable and should be set with SET GLOBAL
+SELECT @@innodb_index_fill_factor;
+@@innodb_index_fill_factor
+94
+SET GLOBAL innodb_index_fill_factor = 0;
+SELECT @@global.innodb_index_fill_factor;
+@@global.innodb_index_fill_factor
+0
+# Min/Max
+SET @@global.innodb_index_fill_factor = 0;
+SELECT @@global.innodb_index_fill_factor;
+@@global.innodb_index_fill_factor
+0
+SET @@global.innodb_index_fill_factor = 1;
+SELECT @@global.innodb_index_fill_factor;
+@@global.innodb_index_fill_factor
+1
+SET @@global.innodb_index_fill_factor = 99;
+SELECT @@global.innodb_index_fill_factor;
+@@global.innodb_index_fill_factor
+99
+SET @@global.innodb_index_fill_factor = 100;
+SELECT @@global.innodb_index_fill_factor;
+@@global.innodb_index_fill_factor
+100
+SET @@global.innodb_index_fill_factor = 101;
+Warnings:
+Warning 1292 Truncated incorrect innodb_index_fill_factor value: '101'
+SELECT @@global.innodb_index_fill_factor;
+@@global.innodb_index_fill_factor
+100
+# Invalid value
+SET @@global.innodb_index_fill_factor = -1;
+Warnings:
+Warning 1292 Truncated incorrect innodb_index_fill_factor value: '-1'
+SELECT @@global.innodb_index_fill_factor;
+@@global.innodb_index_fill_factor
+0
+SET @@global.innodb_index_fill_factor = "T";
+ERROR 42000: Incorrect argument type to variable 'innodb_index_fill_factor'
+SELECT @@global.innodb_index_fill_factor;
+@@global.innodb_index_fill_factor
+0
+SET @@global.innodb_index_fill_factor = "Y";
+ERROR 42000: Incorrect argument type to variable 'innodb_index_fill_factor'
+SELECT @@global.innodb_index_fill_factor;
+@@global.innodb_index_fill_factor
+0
+SET @@global.innodb_index_fill_factor = 1001;
+Warnings:
+Warning 1292 Truncated incorrect innodb_index_fill_factor value: '1001'
+SELECT @@global.innodb_index_fill_factor;
+@@global.innodb_index_fill_factor
+100
+SET @@global.innodb_index_fill_factor = OFF;
+ERROR 42000: Incorrect argument type to variable 'innodb_index_fill_factor'
+SELECT @@global.innodb_index_fill_factor;
+@@global.innodb_index_fill_factor
+100
+SET @@global.innodb_index_fill_factor = ON;
+ERROR 42000: Incorrect argument type to variable 'innodb_index_fill_factor'
+SELECT @@global.innodb_index_fill_factor;
+@@global.innodb_index_fill_factor
+100
+SET @@global.innodb_index_fill_factor = TRUE;
+SELECT @@global.innodb_index_fill_factor;
+@@global.innodb_index_fill_factor
+1
+SET @@global.innodb_index_fill_factor = FALSE;
+SELECT @@global.innodb_index_fill_factor;
+@@global.innodb_index_fill_factor
+0
+# Reset
+SET @@global.innodb_index_fill_factor = @old_innodb_index_fill_factor;
+SELECT @@global.innodb_index_fill_factor;
+@@global.innodb_index_fill_factor
+94
@@ -0,0 +1,66 @@
+--source include/have_innodb.inc
+
+SET @old_innodb_index_fill_factor = @@global.innodb_index_fill_factor;
+SELECT @old_innodb_index_fill_factor;
+
+--echo # Default value
+SET @@global.innodb_index_fill_factor = 0;
+SET @@global.innodb_index_fill_factor = DEFAULT;
+SELECT @@global.innodb_index_fill_factor;
+
+--echo # Scope
+--error ER_GLOBAL_VARIABLE
+SET innodb_index_fill_factor = 1;
+SELECT @@innodb_index_fill_factor;
+
+SET GLOBAL innodb_index_fill_factor = 0;
+SELECT @@global.innodb_index_fill_factor;
+
+--echo # Min/Max
+SET @@global.innodb_index_fill_factor = 0;
+SELECT @@global.innodb_index_fill_factor;
+
+SET @@global.innodb_index_fill_factor = 1;
+SELECT @@global.innodb_index_fill_factor;
+
+SET @@global.innodb_index_fill_factor = 99;
+SELECT @@global.innodb_index_fill_factor;
+
+SET @@global.innodb_index_fill_factor = 100;
+SELECT @@global.innodb_index_fill_factor;
+
+SET @@global.innodb_index_fill_factor = 101;
+SELECT @@global.innodb_index_fill_factor;
+
+--echo # Invalid value
+SET @@global.innodb_index_fill_factor = -1;
+SELECT @@global.innodb_index_fill_factor;
+
+--error ER_WRONG_TYPE_FOR_VAR
+SET @@global.innodb_index_fill_factor = "T";
+SELECT @@global.innodb_index_fill_factor;
+
+--error ER_WRONG_TYPE_FOR_VAR
+SET @@global.innodb_index_fill_factor = "Y";
+SELECT @@global.innodb_index_fill_factor;
+
+SET @@global.innodb_index_fill_factor = 1001;
+SELECT @@global.innodb_index_fill_factor;
+
+--error ER_WRONG_TYPE_FOR_VAR
+SET @@global.innodb_index_fill_factor = OFF;
+SELECT @@global.innodb_index_fill_factor;
+
+--error ER_WRONG_TYPE_FOR_VAR
+SET @@global.innodb_index_fill_factor = ON;
+SELECT @@global.innodb_index_fill_factor;
+
+SET @@global.innodb_index_fill_factor = TRUE;
+SELECT @@global.innodb_index_fill_factor;
+
+SET @@global.innodb_index_fill_factor = FALSE;
+SELECT @@global.innodb_index_fill_factor;
+
+--echo # Reset
+SET @@global.innodb_index_fill_factor = @old_innodb_index_fill_factor;
+SELECT @@global.innodb_index_fill_factor;
@@ -11959,6 +11959,14 @@ static MYSQL_SYSVAR_ULONG(segment_fill_factor, srv_segment_fill_factor,
"extent for the segment.",
NULL, NULL, 88, 0, 100, 0);
+static MYSQL_SYSVAR_UINT(index_fill_factor, srv_index_fill_factor,
+ PLUGIN_VAR_NOCMDARG,
+ "The percentage of how much to fill a leaf page when inserting data, "
+ "the remaining space being reserved to accommodate row expansion (e.g. "
+ "update). InnoDB might perform page splits to maintain the percentage "
+ "of free space. Only applies to clustered indexes.",
+ NULL, NULL, 94, 0, 100, 0);
+
static struct st_mysql_sys_var* innobase_system_variables[]= {
MYSQL_SYSVAR(additional_mem_pool_size),
MYSQL_SYSVAR(autoextend_increment),
@@ -12036,6 +12044,7 @@ static struct st_mysql_sys_var* innobase_system_variables[]= {
MYSQL_SYSVAR(flush_dirty_pages_age),
MYSQL_SYSVAR(anticipatory_flushing),
MYSQL_SYSVAR(segment_fill_factor),
+ MYSQL_SYSVAR(index_fill_factor),
NULL
};
@@ -788,7 +788,8 @@ ulint
dict_index_get_space_reserve(void)
/*==============================*/
{
- return(UNIV_PAGE_SIZE / 16);
+ double reserve_factor = (100UL - srv_index_fill_factor) / 100.0;
+ return((ulint) (UNIV_PAGE_SIZE * reserve_factor));
}
/**********************************************************************//**
@@ -133,6 +133,7 @@ extern char** srv_log_group_home_dirs;
extern ulong srv_auto_extend_increment;
extern ulong srv_segment_fill_factor;
+extern uint srv_index_fill_factor;
extern ibool srv_created_new_raw;
@@ -174,6 +174,10 @@ UNIV_INTERN ulint* srv_data_file_is_raw_partition = NULL;
extent is added to a segment. */
UNIV_INTERN ulong srv_segment_fill_factor = 88;
+/* Target percentage for how much to fill leaf pages, reserving the
+remaining space for record growth (e.g. updates). */
+UNIV_INTERN uint srv_index_fill_factor = 94;
+
/* If the following is TRUE we do not allow inserts etc. This protects
the user from forgetting the 'newraw' keyword to my.cnf */

0 comments on commit b591844

Please sign in to comment.