From 52c63a6cbe815c7a9882914b89e58f8b8e6b41a1 Mon Sep 17 00:00:00 2001 From: Davi Arnaut Date: Wed, 2 Jan 2013 16:40:21 -0800 Subject: [PATCH] InnoDB reserves an excessive amount of space in large tables for write operations. When performing operations that are expected to expand a table (for example, allocate new pages due to a page split), InnoDB currently preallocates and reserves up to 1% of the total size of the tablespace as a measure to ensure that enough free extents (that is, disk space) are available for the operation and to ensure that if running out of disk space, these operations are preemptively failed as to reserve any remaining free space to operations that end up freeing space (that is, delete data). The percentage is reasonable for tables smaller than a few gigabytes, but not for tables sized at tens of gigabytes or more, at which point the percentage won't correctly estimate the free space needed to perform operations and may cause an excessive amount of free extents to be preallocated. Also, this reservation approach is of dubious need considering that disk space usage is normally monitored and that transactions normally won't cause large expansions. In the worst case, it is even possible to expand the underlying filesystem if the database goes into a deadlock where it lacks free space for cleaning operations. The solution is to provide a way to either completely disable free extents reservation or to control the amount of free extents that are reserved for such operations. This changes introduces two new system variables to accomplish both. The variable innodb_reserve_free_extents can be used to enable or disable free extents reservation and innodb_free_extents_reservation_factor can be used to control what percentage of a space size is reserved for operations that may cause more space to be used. --- .../innodb/r/innodb_reserve_free_extents.result | 38 +++++++ .../innodb/t/innodb_reserve_free_extents.test | 45 ++++++++ ...db_free_extents_reservation_factor_basic.result | 119 +++++++++++++++++++++ .../r/innodb_reserve_free_extents_basic.result | 92 ++++++++++++++++ ...nodb_free_extents_reservation_factor_basic.test | 84 +++++++++++++++ .../t/innodb_reserve_free_extents_basic.test | 65 +++++++++++ sql/share/errmsg-utf8.txt | 8 +- storage/innobase/btr/btr0cur.c | 8 +- storage/innobase/fsp/fsp0fsp.c | 80 ++++++++------ storage/innobase/handler/ha_innodb.cc | 15 +++ storage/innobase/include/srv0srv.h | 3 + storage/innobase/srv/srv0srv.c | 10 ++ storage/innobase/trx/trx0undo.c | 10 +- 13 files changed, 537 insertions(+), 40 deletions(-) create mode 100644 mysql-test/suite/innodb/r/innodb_reserve_free_extents.result create mode 100644 mysql-test/suite/innodb/t/innodb_reserve_free_extents.test create mode 100644 mysql-test/suite/sys_vars/r/innodb_free_extents_reservation_factor_basic.result create mode 100644 mysql-test/suite/sys_vars/r/innodb_reserve_free_extents_basic.result create mode 100644 mysql-test/suite/sys_vars/t/innodb_free_extents_reservation_factor_basic.test create mode 100644 mysql-test/suite/sys_vars/t/innodb_reserve_free_extents_basic.test diff --git a/mysql-test/suite/innodb/r/innodb_reserve_free_extents.result b/mysql-test/suite/innodb/r/innodb_reserve_free_extents.result new file mode 100644 index 0000000..a7c2710 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_reserve_free_extents.result @@ -0,0 +1,38 @@ +# +# Test that free extents reservation can be disabled. +# +SET @old_innodb_file_per_table = @@GLOBAL.innodb_file_per_table; +SET @old_innodb_reserve_free_extents = @@GLOBAL.innodb_reserve_free_extents; +SET GLOBAL innodb_file_per_table = ON; +SET GLOBAL innodb_reserve_free_extents = TRUE; +# Create and populate a table so that new extents are allocated. +CREATE TABLE t1 (a BIGINT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(512), +c VARCHAR(512)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (0, REPEAT('a', 512), REPEAT('b', 512)); +INSERT INTO t1 SELECT 0,b,c FROM t1; +INSERT INTO t1 SELECT 0,b,c FROM t1; +INSERT INTO t1 SELECT 0,b,c FROM t1; +INSERT INTO t1 SELECT 0,b,c FROM t1; +INSERT INTO t1 SELECT 0,b,c FROM t1; +INSERT INTO t1 SELECT 0,b,c FROM t1; +INSERT INTO t1 SELECT 0,b,c FROM t1; +INSERT INTO t1 SELECT 0,b,c FROM t1; +INSERT INTO t1 SELECT 0,b,c FROM t1; +INSERT INTO t1 SELECT 0,b,c FROM t1; +INSERT INTO t1 SELECT 0,b,c FROM t1; +# Show the number of free extents. +SELECT (DATA_FREE / 1048576) AS FREE_EXTENTS +FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1'; +FREE_EXTENTS +4.0000 +# Disable free extents reservation and rebuild the table. +SET GLOBAL innodb_reserve_free_extents = OFF; +ALTER TABLE t1 ENGINE=InnoDB; +# Show the number of free extents. +SELECT (DATA_FREE / 1048576) AS FREE_EXTENTS +FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1'; +FREE_EXTENTS +0.0000 +DROP TABLE t1; +SET @@GLOBAL.innodb_file_per_table = @old_innodb_file_per_table; +SET @@GLOBAL.innodb_reserve_free_extents = @old_innodb_reserve_free_extents; diff --git a/mysql-test/suite/innodb/t/innodb_reserve_free_extents.test b/mysql-test/suite/innodb/t/innodb_reserve_free_extents.test new file mode 100644 index 0000000..1d9509b --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_reserve_free_extents.test @@ -0,0 +1,45 @@ +--source include/have_innodb.inc + +--echo # +--echo # Test that free extents reservation can be disabled. +--echo # + +SET @old_innodb_file_per_table = @@GLOBAL.innodb_file_per_table; +SET @old_innodb_reserve_free_extents = @@GLOBAL.innodb_reserve_free_extents; + +SET GLOBAL innodb_file_per_table = ON; +SET GLOBAL innodb_reserve_free_extents = TRUE; + +--echo # Create and populate a table so that new extents are allocated. +CREATE TABLE t1 (a BIGINT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(512), + c VARCHAR(512)) ENGINE=InnoDB; + +INSERT INTO t1 VALUES (0, REPEAT('a', 512), REPEAT('b', 512)); +INSERT INTO t1 SELECT 0,b,c FROM t1; +INSERT INTO t1 SELECT 0,b,c FROM t1; +INSERT INTO t1 SELECT 0,b,c FROM t1; +INSERT INTO t1 SELECT 0,b,c FROM t1; +INSERT INTO t1 SELECT 0,b,c FROM t1; +INSERT INTO t1 SELECT 0,b,c FROM t1; +INSERT INTO t1 SELECT 0,b,c FROM t1; +INSERT INTO t1 SELECT 0,b,c FROM t1; +INSERT INTO t1 SELECT 0,b,c FROM t1; +INSERT INTO t1 SELECT 0,b,c FROM t1; +INSERT INTO t1 SELECT 0,b,c FROM t1; + +--echo # Show the number of free extents. +SELECT (DATA_FREE / 1048576) AS FREE_EXTENTS + FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1'; + +--echo # Disable free extents reservation and rebuild the table. +SET GLOBAL innodb_reserve_free_extents = OFF; +ALTER TABLE t1 ENGINE=InnoDB; + +--echo # Show the number of free extents. +SELECT (DATA_FREE / 1048576) AS FREE_EXTENTS + FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1'; + +DROP TABLE t1; + +SET @@GLOBAL.innodb_file_per_table = @old_innodb_file_per_table; +SET @@GLOBAL.innodb_reserve_free_extents = @old_innodb_reserve_free_extents; diff --git a/mysql-test/suite/sys_vars/r/innodb_free_extents_reservation_factor_basic.result b/mysql-test/suite/sys_vars/r/innodb_free_extents_reservation_factor_basic.result new file mode 100644 index 0000000..4c94060 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/innodb_free_extents_reservation_factor_basic.result @@ -0,0 +1,119 @@ +SET @old_innodb_free_extents_reservation_factor = @@global.innodb_free_extents_reservation_factor; +SELECT @old_innodb_free_extents_reservation_factor; +@old_innodb_free_extents_reservation_factor +1 +# Default value +SET @@global.innodb_free_extents_reservation_factor = 0; +SET @@global.innodb_free_extents_reservation_factor = DEFAULT; +SELECT @@global.innodb_free_extents_reservation_factor; +@@global.innodb_free_extents_reservation_factor +1.000000 +# Scope +SET innodb_free_extents_reservation_factor = 1; +ERROR HY000: Variable 'innodb_free_extents_reservation_factor' is a GLOBAL variable and should be set with SET GLOBAL +SELECT @@innodb_free_extents_reservation_factor; +@@innodb_free_extents_reservation_factor +1.000000 +SET GLOBAL innodb_free_extents_reservation_factor = 0; +SELECT @@global.innodb_free_extents_reservation_factor; +@@global.innodb_free_extents_reservation_factor +0.000000 +# Min/Max +SET @@global.innodb_free_extents_reservation_factor = -1; +Warnings: +Warning 1292 Truncated incorrect innodb_free_extents_reservation_factor value: '-1' +SELECT @@global.innodb_free_extents_reservation_factor; +@@global.innodb_free_extents_reservation_factor +0.000000 +SET @@global.innodb_free_extents_reservation_factor = -0.01; +Warnings: +Warning 1292 Truncated incorrect innodb_free_extents_reservation_factor value: '-0.01' +SELECT @@global.innodb_free_extents_reservation_factor; +@@global.innodb_free_extents_reservation_factor +0.000000 +SET @@global.innodb_free_extents_reservation_factor = 0.0; +SELECT @@global.innodb_free_extents_reservation_factor; +@@global.innodb_free_extents_reservation_factor +0.000000 +SET @@global.innodb_free_extents_reservation_factor = 0.001; +SELECT @@global.innodb_free_extents_reservation_factor; +@@global.innodb_free_extents_reservation_factor +0.001000 +SET @@global.innodb_free_extents_reservation_factor = 0.005; +SELECT @@global.innodb_free_extents_reservation_factor; +@@global.innodb_free_extents_reservation_factor +0.005000 +SET @@global.innodb_free_extents_reservation_factor = 0.01; +SELECT @@global.innodb_free_extents_reservation_factor; +@@global.innodb_free_extents_reservation_factor +0.010000 +SET @@global.innodb_free_extents_reservation_factor = 99; +SELECT @@global.innodb_free_extents_reservation_factor; +@@global.innodb_free_extents_reservation_factor +99.000000 +SET @@global.innodb_free_extents_reservation_factor = 99.9; +SELECT @@global.innodb_free_extents_reservation_factor; +@@global.innodb_free_extents_reservation_factor +99.900000 +SET @@global.innodb_free_extents_reservation_factor = 100; +SELECT @@global.innodb_free_extents_reservation_factor; +@@global.innodb_free_extents_reservation_factor +100.000000 +SET @@global.innodb_free_extents_reservation_factor = 100.1; +Warnings: +Warning 1292 Truncated incorrect innodb_free_extents_reservation_factor value: '100.1' +SELECT @@global.innodb_free_extents_reservation_factor; +@@global.innodb_free_extents_reservation_factor +100.000000 +SET @@global.innodb_free_extents_reservation_factor = 101; +Warnings: +Warning 1292 Truncated incorrect innodb_free_extents_reservation_factor value: '101' +SELECT @@global.innodb_free_extents_reservation_factor; +@@global.innodb_free_extents_reservation_factor +100.000000 +# Invalid value +SET @@global.innodb_free_extents_reservation_factor = -1; +Warnings: +Warning 1292 Truncated incorrect innodb_free_extents_reservation_factor value: '-1' +SELECT @@global.innodb_free_extents_reservation_factor; +@@global.innodb_free_extents_reservation_factor +0.000000 +SET @@global.innodb_free_extents_reservation_factor = "T"; +ERROR 42000: Incorrect argument type to variable 'innodb_free_extents_reservation_factor' +SELECT @@global.innodb_free_extents_reservation_factor; +@@global.innodb_free_extents_reservation_factor +0.000000 +SET @@global.innodb_free_extents_reservation_factor = "Y"; +ERROR 42000: Incorrect argument type to variable 'innodb_free_extents_reservation_factor' +SELECT @@global.innodb_free_extents_reservation_factor; +@@global.innodb_free_extents_reservation_factor +0.000000 +SET @@global.innodb_free_extents_reservation_factor = 1001; +Warnings: +Warning 1292 Truncated incorrect innodb_free_extents_reservation_factor value: '1001' +SELECT @@global.innodb_free_extents_reservation_factor; +@@global.innodb_free_extents_reservation_factor +100.000000 +SET @@global.innodb_free_extents_reservation_factor = OFF; +ERROR 42000: Incorrect argument type to variable 'innodb_free_extents_reservation_factor' +SELECT @@global.innodb_free_extents_reservation_factor; +@@global.innodb_free_extents_reservation_factor +100.000000 +SET @@global.innodb_free_extents_reservation_factor = ON; +ERROR 42000: Incorrect argument type to variable 'innodb_free_extents_reservation_factor' +SELECT @@global.innodb_free_extents_reservation_factor; +@@global.innodb_free_extents_reservation_factor +100.000000 +SET @@global.innodb_free_extents_reservation_factor = TRUE; +SELECT @@global.innodb_free_extents_reservation_factor; +@@global.innodb_free_extents_reservation_factor +1.000000 +SET @@global.innodb_free_extents_reservation_factor = FALSE; +SELECT @@global.innodb_free_extents_reservation_factor; +@@global.innodb_free_extents_reservation_factor +0.000000 +# Reset +SET @@global.innodb_free_extents_reservation_factor = @old_innodb_free_extents_reservation_factor; +SELECT @@global.innodb_free_extents_reservation_factor; +@@global.innodb_free_extents_reservation_factor +1.000000 diff --git a/mysql-test/suite/sys_vars/r/innodb_reserve_free_extents_basic.result b/mysql-test/suite/sys_vars/r/innodb_reserve_free_extents_basic.result new file mode 100644 index 0000000..3249413 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/innodb_reserve_free_extents_basic.result @@ -0,0 +1,92 @@ +SET @start_global_value = @@global.innodb_reserve_free_extents; +SELECT @start_global_value; +@start_global_value +1 +Valid values are 'ON' and 'OFF' +SELECT @@GLOBAL.innodb_reserve_free_extents IN (0, 1); +@@GLOBAL.innodb_reserve_free_extents IN (0, 1) +1 +SELECT @@GLOBAL.innodb_reserve_free_extents; +@@GLOBAL.innodb_reserve_free_extents +1 +SELECT @@SESSION.innodb_reserve_free_extents; +ERROR HY000: Variable 'innodb_reserve_free_extents' is a GLOBAL variable +SHOW GLOBAL VARIABLES LIKE 'innodb_reserve_free_extents'; +Variable_name Value +innodb_reserve_free_extents ON +SHOW SESSION VARIABLES LIKE 'innodb_reserve_free_extents'; +Variable_name Value +innodb_reserve_free_extents ON +SELECT * FROM information_schema.global_variables WHERE variable_name='innodb_reserve_free_extents'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_RESERVE_FREE_EXTENTS ON +SELECT * FROM information_schema.session_variables WHERE variable_name='innodb_reserve_free_extents'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_RESERVE_FREE_EXTENTS ON +SET GLOBAL innodb_reserve_free_extents='OFF'; +SELECT @@GLOBAL.innodb_reserve_free_extents; +@@GLOBAL.innodb_reserve_free_extents +0 +SELECT * FROM information_schema.global_variables WHERE variable_name='innodb_reserve_free_extents'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_RESERVE_FREE_EXTENTS OFF +SELECT * FROM information_schema.session_variables WHERE variable_name='innodb_reserve_free_extents'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_RESERVE_FREE_EXTENTS OFF +SET @@GLOBAL.innodb_reserve_free_extents=1; +SELECT @@GLOBAL.innodb_reserve_free_extents; +@@GLOBAL.innodb_reserve_free_extents +1 +SELECT * FROM information_schema.global_variables WHERE variable_name='innodb_reserve_free_extents'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_RESERVE_FREE_EXTENTS ON +SELECT * FROM information_schema.session_variables WHERE variable_name='innodb_reserve_free_extents'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_RESERVE_FREE_EXTENTS ON +SET GLOBAL innodb_reserve_free_extents=0; +SELECT @@GLOBAL.innodb_reserve_free_extents; +@@GLOBAL.innodb_reserve_free_extents +0 +SELECT * FROM information_schema.global_variables WHERE variable_name='innodb_reserve_free_extents'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_RESERVE_FREE_EXTENTS OFF +SELECT * FROM information_schema.session_variables WHERE variable_name='innodb_reserve_free_extents'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_RESERVE_FREE_EXTENTS OFF +SET @@GLOBAL.innodb_reserve_free_extents='ON'; +SELECT @@GLOBAL.innodb_reserve_free_extents; +@@GLOBAL.innodb_reserve_free_extents +1 +SELECT * FROM information_schema.global_variables WHERE variable_name='innodb_reserve_free_extents'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_RESERVE_FREE_EXTENTS ON +SELECT * FROM information_schema.session_variables WHERE variable_name='innodb_reserve_free_extents'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_RESERVE_FREE_EXTENTS ON +SET SESSION innodb_reserve_free_extents='OFF'; +ERROR HY000: Variable 'innodb_reserve_free_extents' is a GLOBAL variable and should be set with SET GLOBAL +SET @@SESSION.innodb_reserve_free_extents='ON'; +ERROR HY000: Variable 'innodb_reserve_free_extents' is a GLOBAL variable and should be set with SET GLOBAL +SET GLOBAL innodb_reserve_free_extents=1.1; +ERROR 42000: Incorrect argument type to variable 'innodb_reserve_free_extents' +SET GLOBAL innodb_reserve_free_extents=1e1; +ERROR 42000: Incorrect argument type to variable 'innodb_reserve_free_extents' +SET GLOBAL innodb_reserve_free_extents=2; +ERROR 42000: Variable 'innodb_reserve_free_extents' can't be set to the value of '2' +NOTE: The following should fail with ER_WRONG_VALUE_FOR_VAR (BUG#50643) +SET GLOBAL innodb_reserve_free_extents=-3; +SELECT @@GLOBAL.innodb_reserve_free_extents; +@@GLOBAL.innodb_reserve_free_extents +1 +SELECT * FROM information_schema.global_variables WHERE variable_name='innodb_reserve_free_extents'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_RESERVE_FREE_EXTENTS ON +SELECT * FROM information_schema.session_variables WHERE variable_name='innodb_reserve_free_extents'; +VARIABLE_NAME VARIABLE_VALUE +INNODB_RESERVE_FREE_EXTENTS ON +SET GLOBAL innodb_reserve_free_extents='AUTO'; +ERROR 42000: Variable 'innodb_reserve_free_extents' can't be set to the value of 'AUTO' +SET @@GLOBAL.innodb_reserve_free_extents = @start_global_value; +SELECT @@GLOBAL.innodb_reserve_free_extents; +@@GLOBAL.innodb_reserve_free_extents +1 diff --git a/mysql-test/suite/sys_vars/t/innodb_free_extents_reservation_factor_basic.test b/mysql-test/suite/sys_vars/t/innodb_free_extents_reservation_factor_basic.test new file mode 100644 index 0000000..cdfd43f --- /dev/null +++ b/mysql-test/suite/sys_vars/t/innodb_free_extents_reservation_factor_basic.test @@ -0,0 +1,84 @@ +--source include/have_innodb.inc + +SET @old_innodb_free_extents_reservation_factor = @@global.innodb_free_extents_reservation_factor; +SELECT @old_innodb_free_extents_reservation_factor; + +--echo # Default value +SET @@global.innodb_free_extents_reservation_factor = 0; +SET @@global.innodb_free_extents_reservation_factor = DEFAULT; +SELECT @@global.innodb_free_extents_reservation_factor; + +--echo # Scope +--error ER_GLOBAL_VARIABLE +SET innodb_free_extents_reservation_factor = 1; +SELECT @@innodb_free_extents_reservation_factor; + +SET GLOBAL innodb_free_extents_reservation_factor = 0; +SELECT @@global.innodb_free_extents_reservation_factor; + +--echo # Min/Max +SET @@global.innodb_free_extents_reservation_factor = -1; +SELECT @@global.innodb_free_extents_reservation_factor; + +SET @@global.innodb_free_extents_reservation_factor = -0.01; +SELECT @@global.innodb_free_extents_reservation_factor; + +SET @@global.innodb_free_extents_reservation_factor = 0.0; +SELECT @@global.innodb_free_extents_reservation_factor; + +SET @@global.innodb_free_extents_reservation_factor = 0.001; +SELECT @@global.innodb_free_extents_reservation_factor; + +SET @@global.innodb_free_extents_reservation_factor = 0.005; +SELECT @@global.innodb_free_extents_reservation_factor; + +SET @@global.innodb_free_extents_reservation_factor = 0.01; +SELECT @@global.innodb_free_extents_reservation_factor; + +SET @@global.innodb_free_extents_reservation_factor = 99; +SELECT @@global.innodb_free_extents_reservation_factor; + +SET @@global.innodb_free_extents_reservation_factor = 99.9; +SELECT @@global.innodb_free_extents_reservation_factor; + +SET @@global.innodb_free_extents_reservation_factor = 100; +SELECT @@global.innodb_free_extents_reservation_factor; + +SET @@global.innodb_free_extents_reservation_factor = 100.1; +SELECT @@global.innodb_free_extents_reservation_factor; + +SET @@global.innodb_free_extents_reservation_factor = 101; +SELECT @@global.innodb_free_extents_reservation_factor; + +--echo # Invalid value +SET @@global.innodb_free_extents_reservation_factor = -1; +SELECT @@global.innodb_free_extents_reservation_factor; + +--error ER_WRONG_TYPE_FOR_VAR +SET @@global.innodb_free_extents_reservation_factor = "T"; +SELECT @@global.innodb_free_extents_reservation_factor; + +--error ER_WRONG_TYPE_FOR_VAR +SET @@global.innodb_free_extents_reservation_factor = "Y"; +SELECT @@global.innodb_free_extents_reservation_factor; + +SET @@global.innodb_free_extents_reservation_factor = 1001; +SELECT @@global.innodb_free_extents_reservation_factor; + +--error ER_WRONG_TYPE_FOR_VAR +SET @@global.innodb_free_extents_reservation_factor = OFF; +SELECT @@global.innodb_free_extents_reservation_factor; + +--error ER_WRONG_TYPE_FOR_VAR +SET @@global.innodb_free_extents_reservation_factor = ON; +SELECT @@global.innodb_free_extents_reservation_factor; + +SET @@global.innodb_free_extents_reservation_factor = TRUE; +SELECT @@global.innodb_free_extents_reservation_factor; + +SET @@global.innodb_free_extents_reservation_factor = FALSE; +SELECT @@global.innodb_free_extents_reservation_factor; + +--echo # Reset +SET @@global.innodb_free_extents_reservation_factor = @old_innodb_free_extents_reservation_factor; +SELECT @@global.innodb_free_extents_reservation_factor; diff --git a/mysql-test/suite/sys_vars/t/innodb_reserve_free_extents_basic.test b/mysql-test/suite/sys_vars/t/innodb_reserve_free_extents_basic.test new file mode 100644 index 0000000..cb81e89 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/innodb_reserve_free_extents_basic.test @@ -0,0 +1,65 @@ +--source include/have_innodb.inc + +SET @start_global_value = @@global.innodb_reserve_free_extents; +SELECT @start_global_value; + +# +# Global scope only +# +--echo Valid values are 'ON' and 'OFF' +SELECT @@GLOBAL.innodb_reserve_free_extents IN (0, 1); +SELECT @@GLOBAL.innodb_reserve_free_extents; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@SESSION.innodb_reserve_free_extents; +SHOW GLOBAL VARIABLES LIKE 'innodb_reserve_free_extents'; +SHOW SESSION VARIABLES LIKE 'innodb_reserve_free_extents'; +SELECT * FROM information_schema.global_variables WHERE variable_name='innodb_reserve_free_extents'; +SELECT * FROM information_schema.session_variables WHERE variable_name='innodb_reserve_free_extents'; + +# +# Read-write variable +# +SET GLOBAL innodb_reserve_free_extents='OFF'; +SELECT @@GLOBAL.innodb_reserve_free_extents; +SELECT * FROM information_schema.global_variables WHERE variable_name='innodb_reserve_free_extents'; +SELECT * FROM information_schema.session_variables WHERE variable_name='innodb_reserve_free_extents'; +SET @@GLOBAL.innodb_reserve_free_extents=1; +SELECT @@GLOBAL.innodb_reserve_free_extents; +SELECT * FROM information_schema.global_variables WHERE variable_name='innodb_reserve_free_extents'; +SELECT * FROM information_schema.session_variables WHERE variable_name='innodb_reserve_free_extents'; +SET GLOBAL innodb_reserve_free_extents=0; +SELECT @@GLOBAL.innodb_reserve_free_extents; +SELECT * FROM information_schema.global_variables WHERE variable_name='innodb_reserve_free_extents'; +SELECT * FROM information_schema.session_variables WHERE variable_name='innodb_reserve_free_extents'; +SET @@GLOBAL.innodb_reserve_free_extents='ON'; +SELECT @@GLOBAL.innodb_reserve_free_extents; +SELECT * FROM information_schema.global_variables WHERE variable_name='innodb_reserve_free_extents'; +SELECT * FROM information_schema.session_variables WHERE variable_name='innodb_reserve_free_extents'; +--error ER_GLOBAL_VARIABLE +SET SESSION innodb_reserve_free_extents='OFF'; +--error ER_GLOBAL_VARIABLE +SET @@SESSION.innodb_reserve_free_extents='ON'; + +# +# Boolean type. +# +--error ER_WRONG_TYPE_FOR_VAR +SET GLOBAL innodb_reserve_free_extents=1.1; +--error ER_WRONG_TYPE_FOR_VAR +SET GLOBAL innodb_reserve_free_extents=1e1; +--error ER_WRONG_VALUE_FOR_VAR +SET GLOBAL innodb_reserve_free_extents=2; +--echo NOTE: The following should fail with ER_WRONG_VALUE_FOR_VAR (BUG#50643) +SET GLOBAL innodb_reserve_free_extents=-3; +SELECT @@GLOBAL.innodb_reserve_free_extents; +SELECT * FROM information_schema.global_variables WHERE variable_name='innodb_reserve_free_extents'; +SELECT * FROM information_schema.session_variables WHERE variable_name='innodb_reserve_free_extents'; +--error ER_WRONG_VALUE_FOR_VAR +SET GLOBAL innodb_reserve_free_extents='AUTO'; + +# +# Cleanup +# + +SET @@GLOBAL.innodb_reserve_free_extents = @start_global_value; +SELECT @@GLOBAL.innodb_reserve_free_extents; diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt index 9ec9591..487b831 100644 --- a/sql/share/errmsg-utf8.txt +++ b/sql/share/errmsg-utf8.txt @@ -5061,10 +5061,10 @@ ER_DUPLICATED_VALUE_IN_TYPE por "Coluna '%-.100s' tem valor duplicado '%-.64s' em %s" spa "Columna '%-.100s' tiene valor doblado '%-.64s' en %s" ER_TRUNCATED_WRONG_VALUE 22007 - eng "Truncated incorrect %-.32s value: '%-.128s'" - ger "Falscher %-.32s-Wert gekürzt: '%-.128s'" - por "Truncado errado %-.32s valor: '%-.128s'" - spa "Equivocado truncado %-.32s valor: '%-.128s'" + eng "Truncated incorrect %-.64s value: '%-.128s'" + ger "Falscher %-.64s-Wert gekürzt: '%-.128s'" + por "Truncado errado %-.64s valor: '%-.128s'" + spa "Equivocado truncado %-.64s valor: '%-.128s'" ER_TOO_MUCH_AUTO_TIMESTAMP_COLS eng "Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause" ger "Fehlerhafte Tabellendefinition. Es kann nur eine einzige TIMESTAMP-Spalte mit CURRENT_TIMESTAMP als DEFAULT oder in einer ON-UPDATE-Klausel geben" diff --git a/storage/innobase/btr/btr0cur.c b/storage/innobase/btr/btr0cur.c index 799a23d..6772ee9 100644 --- a/storage/innobase/btr/btr0cur.c +++ b/storage/innobase/btr/btr0cur.c @@ -1541,7 +1541,7 @@ btr_cur_pessimistic_insert( if (big_rec_vec == NULL) { - if (n_extents > 0) { + if (n_extents > 0 && n_reserved > 0) { fil_space_release_free_extents(index->space, n_reserved); } @@ -1576,7 +1576,7 @@ btr_cur_pessimistic_insert( lock_update_insert(btr_cur_get_block(cursor), *rec); } - if (n_extents > 0) { + if (n_extents > 0 && n_reserved > 0) { fil_space_release_free_extents(index->space, n_reserved); } @@ -2564,7 +2564,7 @@ return_after_reservations: ut_a(!page_zip || page_zip_validate(page_zip, page)); #endif /* UNIV_ZIP_DEBUG */ - if (n_extents > 0) { + if (n_extents > 0 && n_reserved > 0) { fil_space_release_free_extents(index->space, n_reserved); } @@ -3208,7 +3208,7 @@ return_after_reservations: ret = btr_cur_compress_if_useful(cursor, FALSE, mtr); } - if (n_extents > 0) { + if (n_extents > 0 && n_reserved > 0) { fil_space_release_free_extents(index->space, n_reserved); } diff --git a/storage/innobase/fsp/fsp0fsp.c b/storage/innobase/fsp/fsp0fsp.c index 035a71e..592ebeb 100644 --- a/storage/innobase/fsp/fsp0fsp.c +++ b/storage/innobase/fsp/fsp0fsp.c @@ -2501,7 +2501,7 @@ fseg_create_general( mlog_write_ulint(header + FSEG_HDR_SPACE, space, MLOG_4BYTES, mtr); funct_exit: - if (!has_done_reservation) { + if (!has_done_reservation && n_reserved) { fil_space_release_free_extents(space, n_reserved); } @@ -3061,7 +3061,7 @@ fseg_alloc_free_page_general( block = fseg_alloc_free_page_low(space, zip_size, inode, hint, direction, mtr, init_mtr); - if (!has_done_reservation) { + if (!has_done_reservation && n_reserved) { fil_space_release_free_extents(space, n_reserved); } @@ -3107,6 +3107,36 @@ fsp_reserve_free_pages( space_header, mtr)); } +/********************************************************************//** +Gets the number of free extents to be reserved for certain allocations. */ +static +ulint +fsp_get_n_reserve_free_extents( +/*===========================*/ + ulint size, /*!< in: size of the space */ + ulint alloc_type) /*!< in: FSP_NORMAL, FSP_UNDO, or FSP_CLEANING */ + +{ + double n_reserve = size / FSP_EXTENT_SIZE; + + n_reserve *= srv_free_extents_rsvn_factor / 100.0; + + if (alloc_type == FSP_NORMAL) { + /* The number of extents to reserve is 2 extents plus the free + extents reservation factor (percentage) of the space size. */ + n_reserve = 2.0 + n_reserve; + } else if (alloc_type == FSP_UNDO) { + /* Reserve half the amount of disk space that would have + been reserved for normal allocations. */ + n_reserve = 1.0 + (n_reserve / 2.0); + } else { + ut_a(alloc_type == FSP_CLEANING); + n_reserve = 0.0; + } + + return(n_reserve); +} + /**********************************************************************//** Reserves free pages from a tablespace. All mini-transactions which may use several pages from the tablespace should call this function beforehand @@ -3132,14 +3162,19 @@ function we would liberally reserve several 64 page extents for every page split or merge in a B-tree. But we do not want to waste disk space if the table only occupies < 32 pages. That is why we apply different rules in that special case, just ensuring that there are 3 free pages available. + +If free space reservation is disabled (srv_reserve_free_extents == FALSE), the +function succeeds but no extents are actually reserved. + @return TRUE if we were able to make the reservation */ UNIV_INTERN ibool fsp_reserve_free_extents( /*=====================*/ ulint* n_reserved,/*!< out: number of extents actually reserved; if we - return TRUE and the tablespace size is < 64 pages, - then this can be 0, otherwise it is n_ext */ + return TRUE and the tablespace size is < 64 pages or + space reservation is disabled, then this can be 0, + otherwise it is n_ext */ ulint space, /*!< in: space id */ ulint n_ext, /*!< in: number of extents to reserve */ ulint alloc_type,/*!< in: FSP_NORMAL, FSP_UNDO, or FSP_CLEANING */ @@ -3159,6 +3194,12 @@ fsp_reserve_free_extents( ulint n_pages_added; ut_ad(mtr); + + if (!srv_reserve_free_extents) { + *n_reserved = 0UL; + return(TRUE); + } + *n_reserved = n_ext; latch = fil_space_get_latch(space, &flags); @@ -3203,28 +3244,10 @@ try_again: n_free = n_free_list_ext + n_free_up; - if (alloc_type == FSP_NORMAL) { - /* We reserve 1 extent + 0.5 % of the space size to undo logs - and 1 extent + 0.5 % to cleaning operations; NOTE: this source - code is duplicated in the function below! */ - - reserve = 2 + ((size / FSP_EXTENT_SIZE) * 2) / 200; - - if (n_free <= reserve + n_ext) { + reserve = fsp_get_n_reserve_free_extents(size, alloc_type); - goto try_to_extend; - } - } else if (alloc_type == FSP_UNDO) { - /* We reserve 0.5 % of the space size to cleaning operations */ - - reserve = 1 + ((size / FSP_EXTENT_SIZE) * 1) / 200; - - if (n_free <= reserve + n_ext) { - - goto try_to_extend; - } - } else { - ut_a(alloc_type == FSP_CLEANING); + if (n_free <= reserve + n_ext) { + goto try_to_extend; } success = fil_space_reserve_free_extents(space, n_free, n_ext); @@ -3363,11 +3386,8 @@ fsp_get_available_space_in_free_extents( n_free = n_free_list_ext + n_free_up; - /* We reserve 1 extent + 0.5 % of the space size to undo logs - and 1 extent + 0.5 % to cleaning operations; NOTE: this source - code is duplicated in the function above! */ - - reserve = 2 + ((size / FSP_EXTENT_SIZE) * 2) / 200; + reserve = srv_reserve_free_extents ? + fsp_get_n_reserve_free_extents(size, FSP_NORMAL) : 0; if (reserve > n_free) { return(0); diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index 6716115..5aaa8ab 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -12059,6 +12059,19 @@ static MYSQL_SYSVAR_BOOL(lease_fragment_extents, srv_lease_fragment_extents, "If a free fragment extent is available, allow it to be allocated to " "a segment.", NULL, NULL, FALSE); +static MYSQL_SYSVAR_BOOL(reserve_free_extents, srv_reserve_free_extents, + PLUGIN_VAR_NOCMDARG, + "Whether to reserve a certain percentage of a space size as free space " + "for operations that might allocate several pages.", + NULL, NULL, TRUE); + +static MYSQL_SYSVAR_DOUBLE(free_extents_reservation_factor, + srv_free_extents_rsvn_factor, PLUGIN_VAR_RQCMDARG, + "Percentage of a space size to reserve for operations that may cause more " + "space to be used. If the resulting amount of free space (in extents) is " + "not available, the operation is not allowed to proceed.", + NULL, NULL, 1.0, 0.0, 100.0, 0); + static struct st_mysql_sys_var* innobase_system_variables[]= { MYSQL_SYSVAR(additional_mem_pool_size), MYSQL_SYSVAR(autoextend_increment), @@ -12139,6 +12152,8 @@ static struct st_mysql_sys_var* innobase_system_variables[]= { MYSQL_SYSVAR(index_fill_factor), MYSQL_SYSVAR(index_page_split_mode), MYSQL_SYSVAR(lease_fragment_extents), + MYSQL_SYSVAR(reserve_free_extents), + MYSQL_SYSVAR(free_extents_reservation_factor), NULL }; diff --git a/storage/innobase/include/srv0srv.h b/storage/innobase/include/srv0srv.h index 6c6b1e8..89b2bd0 100644 --- a/storage/innobase/include/srv0srv.h +++ b/storage/innobase/include/srv0srv.h @@ -136,6 +136,9 @@ extern double srv_segment_fill_factor; extern double srv_index_fill_factor; extern my_bool srv_lease_fragment_extents; +extern my_bool srv_reserve_free_extents; +extern double srv_free_extents_rsvn_factor; + extern ibool srv_created_new_raw; extern ulint srv_n_log_groups; diff --git a/storage/innobase/srv/srv0srv.c b/storage/innobase/srv/srv0srv.c index 1f2ceb7..41c0063 100644 --- a/storage/innobase/srv/srv0srv.c +++ b/storage/innobase/srv/srv0srv.c @@ -182,6 +182,16 @@ UNIV_INTERN double srv_index_fill_factor = 93.75; from the segment/space free list. */ UNIV_INTERN my_bool srv_lease_fragment_extents = FALSE; +/* Whether to reserve a percentage (srv_free_extents_rsvn_factor) +of a space size as free space for operations that might allocate +several pages. */ +UNIV_INTERN my_bool srv_reserve_free_extents = TRUE; + +/* Percentage of the space size to reserve for operations that may +allocate several pages. Used to attempt to ensure page allocations +won't fail when expanding an index tree (e.g. page split). */ +UNIV_INTERN double srv_free_extents_rsvn_factor = 1.0; + /* If the following is TRUE we do not allow inserts etc. This protects the user from forgetting the 'newraw' keyword to my.cnf */ diff --git a/storage/innobase/trx/trx0undo.c b/storage/innobase/trx/trx0undo.c index 3d794c6..2530f26 100644 --- a/storage/innobase/trx/trx0undo.c +++ b/storage/innobase/trx/trx0undo.c @@ -469,7 +469,10 @@ trx_undo_seg_create( TRX_UNDO_SEG_HDR + TRX_UNDO_FSEG_HEADER, TRUE, mtr); - fil_space_release_free_extents(space, n_reserved); + if (n_reserved) { + + fil_space_release_free_extents(space, n_reserved); + } if (block == NULL) { /* No space left */ @@ -918,7 +921,10 @@ trx_undo_add_page( + header_page, undo->top_page_no + 1, FSP_UP, TRUE, mtr, mtr); - fil_space_release_free_extents(undo->space, n_reserved); + if (n_reserved) { + + fil_space_release_free_extents(undo->space, n_reserved); + } if (new_block == NULL) { -- 1.8.0