From 7e136b9c42b1cf5ab7ca14b709249dd313894499 Mon Sep 17 00:00:00 2001 From: Mark Callaghan Date: Mon, 26 Sep 2011 16:50:32 -0700 Subject: [PATCH] Fix http://bugs.mysql.com/62534 by making the my.cnf innodb_max_dirty_pages_pct variable a double Summary: This makes innodb_max_dirty_pages_pct a double with min,default,max values 0.001, 75, 99.999. This also adds support for MYSQL_SYSVAR_DOUBLE. " Added more to the BUFFER POOL AND MEMORY section of SHOW INNODB STATUS Percent pages dirty: 8.939 --> this is all n_dirty_pages / used_pages +Percent all pages dirty: 0.195 --> this is n_dirty_pages / all-pages +Max dirty pages percent: 75.000 --> this is innodb_max_dirty_pages_pct " Also made all of the buf use 3 digits of precision instead of 2 (%.2f -> %.3f) Task ID: # Blame Rev: Reviewers: nizamordulu CC: db-eng@lists, dmituzas Test Plan: mtr Revert Plan: Database Impact: Memcache Impact: Other Notes: EImportant: - begin *PUBLIC* platform impact section - Bugzilla: # - end platform impact - Differential Revision: 333501 --- include/my_getopt.h | 2 + include/mysql/plugin.h | 6 ++ .../suite/innodb_plugin/r/innodb_bug62534.result | 47 ++++++++++++++++++ .../suite/innodb_plugin/t/innodb_bug62534.test | 38 ++++++++++++++ mysys/my_getopt.c | 24 +++++++++ sql/set_var.cc | 37 ++++++++++++++ sql/sql_plugin.cc | 52 ++++++++++++++++++++ storage/innodb_plugin/buf/buf0buf.c | 23 +++++--- storage/innodb_plugin/handler/ha_innodb.cc | 4 +- storage/innodb_plugin/include/buf0buf.h | 2 +- storage/innodb_plugin/include/srv0srv.h | 2 +- storage/innodb_plugin/srv/srv0srv.c | 2 +- 12 files changed, 225 insertions(+), 14 deletions(-) create mode 100644 mysql-test/suite/innodb_plugin/r/innodb_bug62534.result create mode 100644 mysql-test/suite/innodb_plugin/t/innodb_bug62534.test diff --git a/include/my_getopt.h b/include/my_getopt.h index d7c9963..144cbf3 100644 --- a/include/my_getopt.h +++ b/include/my_getopt.h @@ -85,6 +85,8 @@ ulonglong getopt_ull_limit_value(ulonglong num, const struct my_option *optp, my_bool *fix); longlong getopt_ll_limit_value(longlong, const struct my_option *, my_bool *fix); +double getopt_double_limit_value(double num, double max_val, double min_val, + double def_val, const char *name, my_bool *fix); my_bool getopt_compare_strings(const char *s, const char *t, uint length); C_MODE_END diff --git a/include/mysql/plugin.h b/include/mysql/plugin.h index 55ef607..43ebf63 100644 --- a/include/mysql/plugin.h +++ b/include/mysql/plugin.h @@ -145,6 +145,7 @@ typedef int (*mysql_show_var_func)(MYSQL_THD, struct st_mysql_show_var*, char *) #define PLUGIN_VAR_STR 0x0005 #define PLUGIN_VAR_ENUM 0x0006 #define PLUGIN_VAR_SET 0x0007 +#define PLUGIN_VAR_DOUBLE 0x0008 #define PLUGIN_VAR_UNSIGNED 0x0080 #define PLUGIN_VAR_THDLOCAL 0x0100 /* Variable is per-connection */ #define PLUGIN_VAR_READONLY 0x0200 /* Server variable is read only */ @@ -317,6 +318,11 @@ DECLARE_MYSQL_SYSVAR_SIMPLE(name, unsigned long long) = { \ PLUGIN_VAR_LONGLONG | PLUGIN_VAR_UNSIGNED | ((opt) & PLUGIN_VAR_MASK), \ #name, comment, check, update, &varname, def, min, max, blk } +#define MYSQL_SYSVAR_DOUBLE(name, varname, opt, comment, check, update, def, min, max, blk) \ +DECLARE_MYSQL_SYSVAR_SIMPLE(name, double) = { \ + PLUGIN_VAR_DOUBLE | ((opt) & PLUGIN_VAR_MASK), \ + #name, comment, check, update, &varname, def, min, max, blk } + #define MYSQL_SYSVAR_ENUM(name, varname, opt, comment, check, update, def, typelib) \ DECLARE_MYSQL_SYSVAR_TYPELIB(name, unsigned long) = { \ PLUGIN_VAR_ENUM | ((opt) & PLUGIN_VAR_MASK), \ diff --git a/mysql-test/suite/innodb_plugin/r/innodb_bug62534.result b/mysql-test/suite/innodb_plugin/r/innodb_bug62534.result new file mode 100644 index 0000000..8a9621f --- /dev/null +++ b/mysql-test/suite/innodb_plugin/r/innodb_bug62534.result @@ -0,0 +1,47 @@ +show global variables like "innodb_max_dirty_pages_pct"; +Variable_name Value +innodb_max_dirty_pages_pct 75.000000 +set global innodb_max_dirty_pages_pct = 0.0; +Warnings: +Warning 1292 Truncated incorrect max_dirty_pages_pct value: '0.000000' +show global variables like "innodb_max_dirty_pages_pct"; +Variable_name Value +innodb_max_dirty_pages_pct 0.001000 +set global innodb_max_dirty_pages_pct = 1; +show global variables like "innodb_max_dirty_pages_pct"; +Variable_name Value +innodb_max_dirty_pages_pct 1.000000 +set global innodb_max_dirty_pages_pct = "1"; +ERROR 42000: Incorrect argument type to variable 'innodb_max_dirty_pages_pct' +set global innodb_max_dirty_pages_pct = 99.9; +show global variables like "innodb_max_dirty_pages_pct"; +Variable_name Value +innodb_max_dirty_pages_pct 99.900000 +set global innodb_max_dirty_pages_pct = 1.0; +show global variables like "innodb_max_dirty_pages_pct"; +Variable_name Value +innodb_max_dirty_pages_pct 1.000000 +set global innodb_max_dirty_pages_pct = 0.1; +show global variables like "innodb_max_dirty_pages_pct"; +Variable_name Value +innodb_max_dirty_pages_pct 0.100000 +set global innodb_max_dirty_pages_pct = 11; +show global variables like "innodb_max_dirty_pages_pct"; +Variable_name Value +innodb_max_dirty_pages_pct 11.000000 +set global innodb_max_dirty_pages_pct = 100; +Warnings: +Warning 1292 Truncated incorrect max_dirty_pages_pct value: '100.000000' +show global variables like "innodb_max_dirty_pages_pct"; +Variable_name Value +innodb_max_dirty_pages_pct 99.999000 +set global innodb_max_dirty_pages_pct = -0.1; +Warnings: +Warning 1292 Truncated incorrect max_dirty_pages_pct value: '-0.100000' +show global variables like "innodb_max_dirty_pages_pct"; +Variable_name Value +innodb_max_dirty_pages_pct 0.001000 +set global innodb_max_dirty_pages_pct = 75; +show global variables like "innodb_max_dirty_pages_pct"; +Variable_name Value +innodb_max_dirty_pages_pct 75.000000 diff --git a/mysql-test/suite/innodb_plugin/t/innodb_bug62534.test b/mysql-test/suite/innodb_plugin/t/innodb_bug62534.test new file mode 100644 index 0000000..13de651 --- /dev/null +++ b/mysql-test/suite/innodb_plugin/t/innodb_bug62534.test @@ -0,0 +1,38 @@ +# +# Confirm innodb_max_dirty_pages_pct parses floating-point values +# + +-- source include/have_innodb_plugin.inc + +show global variables like "innodb_max_dirty_pages_pct"; + +set global innodb_max_dirty_pages_pct = 0.0; +show global variables like "innodb_max_dirty_pages_pct"; + +set global innodb_max_dirty_pages_pct = 1; +show global variables like "innodb_max_dirty_pages_pct"; + +--error ER_WRONG_TYPE_FOR_VAR +set global innodb_max_dirty_pages_pct = "1"; + +set global innodb_max_dirty_pages_pct = 99.9; +show global variables like "innodb_max_dirty_pages_pct"; + +set global innodb_max_dirty_pages_pct = 1.0; +show global variables like "innodb_max_dirty_pages_pct"; + +set global innodb_max_dirty_pages_pct = 0.1; +show global variables like "innodb_max_dirty_pages_pct"; + +set global innodb_max_dirty_pages_pct = 11; +show global variables like "innodb_max_dirty_pages_pct"; + +set global innodb_max_dirty_pages_pct = 100; +show global variables like "innodb_max_dirty_pages_pct"; + +set global innodb_max_dirty_pages_pct = -0.1; +show global variables like "innodb_max_dirty_pages_pct"; + +set global innodb_max_dirty_pages_pct = 75; +show global variables like "innodb_max_dirty_pages_pct"; + diff --git a/mysys/my_getopt.c b/mysys/my_getopt.c index 069626b..07c7401 100644 --- a/mysys/my_getopt.c +++ b/mysys/my_getopt.c @@ -986,6 +986,30 @@ static double getopt_double(char *arg, const struct my_option *optp, int *err) return max(num, (double) optp->min_value); } +double getopt_double_limit_value(double num, double max_val, double min_val, + double def_val, const char *name, my_bool *fix) +{ + my_bool adjusted= FALSE; + double old= num; + if (max_val && num > max_val) + { + num= max_val; + adjusted= TRUE; + } + if (num < min_val) + { + num= min_val; + adjusted= TRUE; + } + if (fix) + *fix= adjusted; + else if (adjusted) + my_getopt_error_reporter(WARNING_LEVEL, + "option '%s': value %g adjusted to %g", + name, old, num); + return num; +} + /* Init one value to it's default values diff --git a/sql/set_var.cc b/sql/set_var.cc index e68a5fc..842f485 100644 --- a/sql/set_var.cc +++ b/sql/set_var.cc @@ -1611,6 +1611,43 @@ bool throw_bounds_warning(THD *thd, bool fixed, bool unsignd, /** + Throw warning (error in STRICT mode) if value for variable needed bounding. + Only call from check(), not update(), because an error in update() would be + bad mojo. Plug-in interface also uses this. + + @param thd thread handle + @param fixed did we have to correct the value? (throw warn/err if so) + @param name variable's name + @param val variable's value + + @retval TRUE on error, FALSE otherwise (warning or OK) + */ +bool throw_bounds_warning_real(THD *thd, bool fixed, const char *name, double val) +{ + if (fixed) + { + char buf[64]; + const char *bufp= buf; + + int r= snprintf(buf, sizeof(buf) - 1, "%f", val); + if (!r) + bufp= "?"; + + if (thd->variables.sql_mode & MODE_STRICT_ALL_TABLES) + { + my_error(ER_WRONG_VALUE_FOR_VAR, MYF(0), name, bufp); + return TRUE; + } + + push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_TRUNCATED_WRONG_VALUE, + ER(ER_TRUNCATED_WRONG_VALUE), name, bufp); + } + return FALSE; +} + + +/** check an unsigned user-supplied value for a systemvariable against bounds. TODO: This is a wrapper function to call clipping from within an update() diff --git a/sql/sql_plugin.cc b/sql/sql_plugin.cc index a2d9fa4..6208bc8 100644 --- a/sql/sql_plugin.cc +++ b/sql/sql_plugin.cc @@ -224,6 +224,8 @@ static void reap_plugins(void); extern sys_var *intern_find_sys_var(const char *str, uint length, bool no_error); extern bool throw_bounds_warning(THD *thd, bool fixed, bool unsignd, const char *name, longlong val); +extern bool throw_bounds_warning_real(THD *thd, bool fixed, const char *name, + double val); #ifdef EMBEDDED_LIBRARY /* declared in sql_base.cc */ @@ -1905,6 +1907,7 @@ typedef DECLARE_MYSQL_SYSVAR_SIMPLE(sysvar_longlong_t, longlong); typedef DECLARE_MYSQL_SYSVAR_SIMPLE(sysvar_uint_t, uint); typedef DECLARE_MYSQL_SYSVAR_SIMPLE(sysvar_ulong_t, ulong); typedef DECLARE_MYSQL_SYSVAR_SIMPLE(sysvar_ulonglong_t, ulonglong); +typedef DECLARE_MYSQL_SYSVAR_SIMPLE(sysvar_double_t, double); typedef DECLARE_MYSQL_THDVAR_SIMPLE(thdvar_int_t, int); typedef DECLARE_MYSQL_THDVAR_SIMPLE(thdvar_long_t, long); @@ -2016,6 +2019,32 @@ static int check_func_longlong(THD *thd, struct st_mysql_sys_var *var, var->name, (longlong) tmp); } + +static int check_func_double(THD *thd, struct st_mysql_sys_var *var, + void *save, st_mysql_value *value) +{ + my_bool fixed; + double tmp, def_value, max_value, min_value; + sysvar_double_t* dvar= (sysvar_double_t*) var; + + value->val_real(value, &tmp); + + DBUG_ASSERT((dvar->flags & + (PLUGIN_VAR_TYPEMASK | + PLUGIN_VAR_UNSIGNED | + PLUGIN_VAR_THDLOCAL)) == PLUGIN_VAR_DOUBLE); + + def_value= dvar->def_val; + min_value= dvar->min_val; + max_value= dvar->max_val; + + *(double *)save= getopt_double_limit_value(tmp, max_value, min_value, + def_value, var->name, &fixed); + + return throw_bounds_warning_real(thd, fixed, var->name, tmp); +} + + static int check_func_str(THD *thd, struct st_mysql_sys_var *var, void *save, st_mysql_value *value) { @@ -2155,6 +2184,13 @@ static void update_func_longlong(THD *thd, struct st_mysql_sys_var *var, } +static void update_func_double(THD *thd, struct st_mysql_sys_var *var, + void *tgt, const void *save) +{ + *(double *)tgt= *(double *) save; +} + + static void update_func_str(THD *thd, struct st_mysql_sys_var *var, void *tgt, const void *save) { @@ -2282,6 +2318,9 @@ static st_bookmark *register_var(const char *plugin, const char *name, case PLUGIN_VAR_SET: size= sizeof(ulonglong); break; + case PLUGIN_VAR_DOUBLE: + size= sizeof(double); + break; case PLUGIN_VAR_STR: size= sizeof(char*); break; @@ -2636,6 +2675,8 @@ bool sys_var_pluginvar::check_update_type(Item_result type) case PLUGIN_VAR_LONG: case PLUGIN_VAR_LONGLONG: return type != INT_RESULT; + case PLUGIN_VAR_DOUBLE: + return type != REAL_RESULT && type != DECIMAL_RESULT && type != INT_RESULT; case PLUGIN_VAR_STR: return type != STRING_RESULT; default: @@ -2655,6 +2696,8 @@ SHOW_TYPE sys_var_pluginvar::show_type() return SHOW_LONG; case PLUGIN_VAR_LONGLONG: return SHOW_LONGLONG; + case PLUGIN_VAR_DOUBLE: + return SHOW_DOUBLE; case PLUGIN_VAR_STR: return SHOW_CHAR_PTR; case PLUGIN_VAR_ENUM: @@ -2879,6 +2922,9 @@ static void plugin_opt_set_limits(struct my_option *options, case PLUGIN_VAR_LONGLONG | PLUGIN_VAR_UNSIGNED: OPTION_SET_LIMITS(GET_ULL, options, (sysvar_ulonglong_t*) opt); break; + case PLUGIN_VAR_DOUBLE: + OPTION_SET_LIMITS(GET_DOUBLE, options, (sysvar_double_t*) opt); + break; case PLUGIN_VAR_ENUM: options->var_type= GET_ENUM; options->typelib= ((sysvar_enum_t*) opt)->typelib; @@ -3109,6 +3155,12 @@ static int construct_options(MEM_ROOT *mem_root, struct st_plugin_int *tmp, if (!opt->update) opt->update= update_func_longlong; break; + case PLUGIN_VAR_DOUBLE: + if (!opt->check) + opt->check= check_func_double; + if (!opt->update) + opt->update= update_func_double; + break; case PLUGIN_VAR_STR: if (!opt->check) opt->check= check_func_str; diff --git a/storage/innodb_plugin/buf/buf0buf.c b/storage/innodb_plugin/buf/buf0buf.c index 9076962..84cb507 100644 --- a/storage/innodb_plugin/buf/buf0buf.c +++ b/storage/innodb_plugin/buf/buf0buf.c @@ -3941,23 +3941,22 @@ Returns the ratio in percents of modified pages in the buffer pool / database pages in the buffer pool. @return modified page percentage ratio */ UNIV_INTERN -ulint +double buf_get_modified_ratio_pct(void) /*============================*/ { - ulint ratio; + double flush_len, lru_len, free_len; buf_pool_mutex_enter(); - ratio = (100 * UT_LIST_GET_LEN(buf_pool->flush_list)) - / (1 + UT_LIST_GET_LEN(buf_pool->LRU) - + UT_LIST_GET_LEN(buf_pool->free)); - - /* 1 + is there to avoid division by zero */ + flush_len = UT_LIST_GET_LEN(buf_pool->flush_list); + lru_len = UT_LIST_GET_LEN(buf_pool->LRU); + free_len = UT_LIST_GET_LEN(buf_pool->free); buf_pool_mutex_exit(); - return(ratio); + /* 1 + is there to avoid division by zero */ + return (100 * flush_len) / (1 + lru_len + free_len); } /*********************************************************************//** @@ -3984,7 +3983,9 @@ buf_print_io( "Modified db pages %lu\n" "Read ahead: %lu\n" "Evicted after read ahead without access: %lu\n" - "Percent pages dirty: %.2f\n" + "Percent pages dirty: %.3f\n" + "Percent all pages dirty: %.3f\n" + "Max dirty pages percent: %.3f\n" "Pending reads %lu\n" "Pending writes: LRU %lu, flush list %lu, single page %lu\n" "Total writes: %lu LRU, %lu flush list, %lu single page\n" @@ -4000,6 +4001,10 @@ buf_print_io( (ulong) buf_pool->stat.n_ra_pages_evicted, (((double) UT_LIST_GET_LEN(buf_pool->flush_list)) / (UT_LIST_GET_LEN(buf_pool->LRU) + 1.0)) * 100.0, + (((double) UT_LIST_GET_LEN(buf_pool->flush_list)) / + (UT_LIST_GET_LEN(buf_pool->LRU) + + UT_LIST_GET_LEN(buf_pool->free) + 1.0)) * 100.0, + srv_max_buf_pool_modified_pct, (ulong) buf_pool->n_pend_reads, (ulong) buf_pool->n_flush[BUF_FLUSH_LRU] + buf_pool->init_flush[BUF_FLUSH_LRU], diff --git a/storage/innodb_plugin/handler/ha_innodb.cc b/storage/innodb_plugin/handler/ha_innodb.cc index 58c203a..7b855e3 100644 --- a/storage/innodb_plugin/handler/ha_innodb.cc +++ b/storage/innodb_plugin/handler/ha_innodb.cc @@ -11922,10 +11922,10 @@ static MYSQL_SYSVAR_STR(log_group_home_dir, innobase_log_group_home_dir, PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY, "Path to InnoDB log files.", NULL, NULL, NULL); -static MYSQL_SYSVAR_ULONG(max_dirty_pages_pct, srv_max_buf_pool_modified_pct, +static MYSQL_SYSVAR_DOUBLE(max_dirty_pages_pct, srv_max_buf_pool_modified_pct, PLUGIN_VAR_RQCMDARG, "Percentage of dirty pages allowed in bufferpool.", - NULL, NULL, 75, 0, 99, 0); + NULL, NULL, 75, 0.001, 99.999, 0); static MYSQL_SYSVAR_BOOL(adaptive_flushing, srv_adaptive_flushing, PLUGIN_VAR_NOCMDARG, diff --git a/storage/innodb_plugin/include/buf0buf.h b/storage/innodb_plugin/include/buf0buf.h index 1096738..c60d3d9 100644 --- a/storage/innodb_plugin/include/buf0buf.h +++ b/storage/innodb_plugin/include/buf0buf.h @@ -639,7 +639,7 @@ Returns the ratio in percents of modified pages in the buffer pool / database pages in the buffer pool. @return modified page percentage ratio */ UNIV_INTERN -ulint +double buf_get_modified_ratio_pct(void); /*============================*/ /**********************************************************************//** diff --git a/storage/innodb_plugin/include/srv0srv.h b/storage/innodb_plugin/include/srv0srv.h index 6139154..19284da 100644 --- a/storage/innodb_plugin/include/srv0srv.h +++ b/storage/innodb_plugin/include/srv0srv.h @@ -218,7 +218,7 @@ extern my_bool srv_use_fast_checksums; extern ibool srv_set_thread_priorities; extern int srv_query_thread_priority; -extern ulong srv_max_buf_pool_modified_pct; +extern double srv_max_buf_pool_modified_pct; extern ulong srv_max_purge_lag; extern ulong srv_replication_delay; diff --git a/storage/innodb_plugin/srv/srv0srv.c b/storage/innodb_plugin/srv/srv0srv.c index f3e6d98..39339cf 100644 --- a/storage/innodb_plugin/srv/srv0srv.c +++ b/storage/innodb_plugin/srv/srv0srv.c @@ -271,7 +271,7 @@ in the buffer pool to all database pages in the buffer pool smaller than the following number. But it is not guaranteed that the value stays below that during a time of heavy update/insert activity. */ -UNIV_INTERN ulong srv_max_buf_pool_modified_pct = 75; +UNIV_INTERN double srv_max_buf_pool_modified_pct = 75.0; /* variable counts amount of data read in total (in bytes) */ UNIV_INTERN ulint srv_data_read = 0; -- 1.7.4