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 0000000..ae1bea4 --- /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 performance_schema.global_variables where variable_name='innodb_idle_flush_pct'; +VARIABLE_NAME VARIABLE_VALUE +innodb_idle_flush_pct 100 +select * from performance_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 performance_schema.global_variables where variable_name='innodb_idle_flush_pct'; +VARIABLE_NAME VARIABLE_VALUE +innodb_idle_flush_pct 10 +select * from performance_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 performance_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 performance_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/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 0000000..b810cbd --- /dev/null +++ b/mysql-test/suite/sys_vars/t/innodb_idle_flush_pct_basic.test @@ -0,0 +1,61 @@ + +# +# 2013-04-01 - Added +# + +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 performance_schema.global_variables where variable_name='innodb_idle_flush_pct'; +select * from performance_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 performance_schema.global_variables where variable_name='innodb_idle_flush_pct'; +select * from performance_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 performance_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 performance_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; diff --git a/storage/innobase/buf/buf0flu.cc b/storage/innobase/buf/buf0flu.cc index cc5dbf3..c6a5156 100644 --- a/storage/innobase/buf/buf0flu.cc +++ b/storage/innobase/buf/buf0flu.cc @@ -3419,9 +3419,11 @@ buf_flush_page_coordinator_thread(size_t n_page_cleaners) n_flushed_list); } - } else if (ret_sleep == OS_SYNC_TIME_EXCEEDED) { + } else if (ret_sleep == OS_SYNC_TIME_EXCEEDED && + srv_idle_flush_pct) { /* no activity, slept enough */ - buf_flush_lists(PCT_IO(100), LSN_MAX, &n_flushed); + buf_flush_lists(PCT_IO(srv_idle_flush_pct), + LSN_MAX, &n_flushed); n_flushed_last += n_flushed; diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index b09e8e5..f7efe99 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -21045,6 +21045,13 @@ static MYSQL_SYSVAR_ULONG(io_capacity_max, srv_max_io_capacity, SRV_MAX_IO_CAPACITY_DUMMY_DEFAULT, 100, SRV_MAX_IO_CAPACITY_LIMIT, 0); +static MYSQL_SYSVAR_ULONG(idle_flush_pct, + srv_idle_flush_pct, + PLUGIN_VAR_RQCMDARG, + "Up to what percentage of dirty pages should be flushed when innodb " + "finds it has spare resources to do so.", + NULL, NULL, 100, 0, 100, 0); + #ifdef UNIV_DEBUG static MYSQL_SYSVAR_BOOL(background_drop_list_empty, innodb_background_drop_list_empty, @@ -22033,6 +22040,7 @@ static struct st_mysql_sys_var* innobase_system_variables[]= { MYSQL_SYSVAR(read_only), MYSQL_SYSVAR(io_capacity), MYSQL_SYSVAR(io_capacity_max), + MYSQL_SYSVAR(idle_flush_pct), MYSQL_SYSVAR(page_cleaners), MYSQL_SYSVAR(monitor_enable), MYSQL_SYSVAR(monitor_disable), diff --git a/storage/innobase/include/srv0srv.h b/storage/innobase/include/srv0srv.h index c3be747..c3ab82b 100644 --- a/storage/innobase/include/srv0srv.h +++ b/storage/innobase/include/srv0srv.h @@ -323,6 +323,8 @@ extern ulong srv_n_write_io_threads; extern uint srv_change_buffer_max_size; +extern ulint srv_idle_flush_pct; + /* Number of IO operations per second the server can do */ extern ulong srv_io_capacity; diff --git a/storage/innobase/srv/srv0srv.cc b/storage/innobase/srv/srv0srv.cc index 3d91cc7..bfa9e3c 100644 --- a/storage/innobase/srv/srv0srv.cc +++ b/storage/innobase/srv/srv0srv.cc @@ -262,6 +262,8 @@ ulong srv_buf_pool_dump_pct; /** Lock table size in bytes */ ulint srv_lock_table_size = ULINT_MAX; +ulint srv_idle_flush_pct = 100; + /* This parameter is deprecated. Use srv_n_io_[read|write]_threads instead. */ ulong srv_n_read_io_threads;