MDEV-6932: Enable Lazy Flushing

Merge Facebook commit 4f3e0343fd2ac3fc7311d0ec9739a8f668274f0d
authored by Steaphan Greene from https://github.com/facebook/mysql-5.6

Adds innodb_idle_flush_pct to enable tuning of the page flushing rate
when the system is relatively idle. We care about this, since doing
extra unnecessary flash writes shortens the lifespan of the flash.

3 files changed, 154 insertions, 0 deletions

diff --git a/mysql-test/suite/sys_vars/r/innodb_idle_flush_pct_basic.result b/mysql-test/suite/sys_vars/r/innodb_idle_flush_pct_basic.result
new file mode 100644
index 00000000000..a2c328f38fd
--- /dev/null
+++ b/mysql-test/suite/sys_vars/r/innodb_idle_flush_pct_basic.result
@@ -0,0 +1,77 @@
+SET @start_global_value = @@global.innodb_idle_flush_pct;
+SELECT @start_global_value;
+@start_global_value
+100
+Valid values are between 0 and 100
+select @@global.innodb_idle_flush_pct between 0 and 100;
+@@global.innodb_idle_flush_pct between 0 and 100
+1
+select @@global.innodb_idle_flush_pct;
+@@global.innodb_idle_flush_pct
+100
+select @@session.innodb_idle_flush_pct;
+ERROR HY000: Variable 'innodb_idle_flush_pct' is a GLOBAL variable
+show global variables like 'innodb_idle_flush_pct';
+Variable_name	Value
+innodb_idle_flush_pct	100
+show session variables like 'innodb_idle_flush_pct';
+Variable_name	Value
+innodb_idle_flush_pct	100
+select * from information_schema.global_variables where variable_name='innodb_idle_flush_pct';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_IDLE_FLUSH_PCT	100
+select * from information_schema.session_variables where variable_name='innodb_idle_flush_pct';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_IDLE_FLUSH_PCT	100
+set global innodb_idle_flush_pct=10;
+select @@global.innodb_idle_flush_pct;
+@@global.innodb_idle_flush_pct
+10
+select * from information_schema.global_variables where variable_name='innodb_idle_flush_pct';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_IDLE_FLUSH_PCT	10
+select * from information_schema.session_variables where variable_name='innodb_idle_flush_pct';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_IDLE_FLUSH_PCT	10
+set session innodb_idle_flush_pct=1;
+ERROR HY000: Variable 'innodb_idle_flush_pct' is a GLOBAL variable and should be set with SET GLOBAL
+set global innodb_idle_flush_pct=1.1;
+ERROR 42000: Incorrect argument type to variable 'innodb_idle_flush_pct'
+set global innodb_idle_flush_pct=1e1;
+ERROR 42000: Incorrect argument type to variable 'innodb_idle_flush_pct'
+set global innodb_idle_flush_pct="bar";
+ERROR 42000: Incorrect argument type to variable 'innodb_idle_flush_pct'
+set global innodb_idle_flush_pct=-7;
+Warnings:
+Warning	1292	Truncated incorrect innodb_idle_flush_pct value: '-7'
+select @@global.innodb_idle_flush_pct;
+@@global.innodb_idle_flush_pct
+0
+select * from information_schema.global_variables where variable_name='innodb_idle_flush_pct';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_IDLE_FLUSH_PCT	0
+set global innodb_idle_flush_pct=106;
+Warnings:
+Warning	1292	Truncated incorrect innodb_idle_flush_pct value: '106'
+select @@global.innodb_idle_flush_pct;
+@@global.innodb_idle_flush_pct
+100
+select * from information_schema.global_variables where variable_name='innodb_idle_flush_pct';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_IDLE_FLUSH_PCT	100
+set global innodb_idle_flush_pct=0;
+select @@global.innodb_idle_flush_pct;
+@@global.innodb_idle_flush_pct
+0
+set global innodb_idle_flush_pct=100;
+select @@global.innodb_idle_flush_pct;
+@@global.innodb_idle_flush_pct
+100
+set global innodb_idle_flush_pct=DEFAULT;
+select @@global.innodb_idle_flush_pct;
+@@global.innodb_idle_flush_pct
+100
+SET @@global.innodb_idle_flush_pct = @start_global_value;
+SELECT @@global.innodb_idle_flush_pct;
+@@global.innodb_idle_flush_pct
+100
diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb.result b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
index f3bc9eead9c..c1823ff7739 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_innodb.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
@@ -1027,6 +1027,20 @@ NUMERIC_BLOCK_SIZE	NULL
 ENUM_VALUE_LIST	NULL
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	OPTIONAL
+VARIABLE_NAME	INNODB_IDLE_FLUSH_PCT
+SESSION_VALUE	NULL
+GLOBAL_VALUE	100
+GLOBAL_VALUE_ORIGIN	COMPILE-TIME
+DEFAULT_VALUE	100
+VARIABLE_SCOPE	GLOBAL
+VARIABLE_TYPE	BIGINT UNSIGNED
+VARIABLE_COMMENT	Up to what percentage of dirty pages should be flushed when innodb finds it has spare resources to do so.
+NUMERIC_MIN_VALUE	0
+NUMERIC_MAX_VALUE	100
+NUMERIC_BLOCK_SIZE	0
+ENUM_VALUE_LIST	NULL
+READ_ONLY	NO
+COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	INNODB_IO_CAPACITY
 SESSION_VALUE	NULL
 GLOBAL_VALUE	200
diff --git a/mysql-test/suite/sys_vars/t/innodb_idle_flush_pct_basic.test b/mysql-test/suite/sys_vars/t/innodb_idle_flush_pct_basic.test
new file mode 100644
index 00000000000..7175a59e238
--- /dev/null
+++ b/mysql-test/suite/sys_vars/t/innodb_idle_flush_pct_basic.test
@@ -0,0 +1,63 @@
+
+#
+# 2013-04-01 - Added
+#
+
+--source include/have_innodb.inc
+
+SET @start_global_value = @@global.innodb_idle_flush_pct;
+SELECT @start_global_value;
+
+#
+# exists as global only
+#
+--echo Valid values are between 0 and 100
+select @@global.innodb_idle_flush_pct between 0 and 100;
+select @@global.innodb_idle_flush_pct;
+--error ER_INCORRECT_GLOBAL_LOCAL_VAR
+select @@session.innodb_idle_flush_pct;
+show global variables like 'innodb_idle_flush_pct';
+show session variables like 'innodb_idle_flush_pct';
+select * from information_schema.global_variables where variable_name='innodb_idle_flush_pct';
+select * from information_schema.session_variables where variable_name='innodb_idle_flush_pct';
+
+#
+# show that it's writable
+#
+set global innodb_idle_flush_pct=10;
+select @@global.innodb_idle_flush_pct;
+select * from information_schema.global_variables where variable_name='innodb_idle_flush_pct';
+select * from information_schema.session_variables where variable_name='innodb_idle_flush_pct';
+--error ER_GLOBAL_VARIABLE
+set session innodb_idle_flush_pct=1;
+
+#
+# incorrect types
+#
+--error ER_WRONG_TYPE_FOR_VAR
+set global innodb_idle_flush_pct=1.1;
+--error ER_WRONG_TYPE_FOR_VAR
+set global innodb_idle_flush_pct=1e1;
+--error ER_WRONG_TYPE_FOR_VAR
+set global innodb_idle_flush_pct="bar";
+
+set global innodb_idle_flush_pct=-7;
+select @@global.innodb_idle_flush_pct;
+select * from information_schema.global_variables where variable_name='innodb_idle_flush_pct';
+set global innodb_idle_flush_pct=106;
+select @@global.innodb_idle_flush_pct;
+select * from information_schema.global_variables where variable_name='innodb_idle_flush_pct';
+
+#
+# min/max/DEFAULT values
+#
+set global innodb_idle_flush_pct=0;
+select @@global.innodb_idle_flush_pct;
+set global innodb_idle_flush_pct=100;
+select @@global.innodb_idle_flush_pct;
+set global innodb_idle_flush_pct=DEFAULT;
+select @@global.innodb_idle_flush_pct;
+
+
+SET @@global.innodb_idle_flush_pct = @start_global_value;
+SELECT @@global.innodb_idle_flush_pct;