Description:
table_open_cache value doesn't auto adjust when the variable is updated at run time. It will be auto adjusted only during start up.
I feel it should be auto adjusted ON_UPDATE so that value is consistent irrespective of whether it is set at runtime or not.
How to repeat:
Before:
------
mysql [localhost:5731] {msandbox} ((none)) > SHOW GLOBAL VARIABLES LIKE '%table_open_cache%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| table_open_cache | 2000 |
| table_open_cache_instances | 16 |
+----------------------------+-------+
2 rows in set (0.00 sec)
Setting the dynamic value:
--------------------------
mysql [localhost:5731] {msandbox} ((none)) > SET GLOBAL table_open_cache=524288;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:5731] {msandbox} ((none)) > SHOW GLOBAL VARIABLES LIKE '%table_open_cache%';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| table_open_cache | 524288 |
| table_open_cache_instances | 16 |
+----------------------------+--------+
2 rows in set (0.00 sec)
---- Restarted ----
Error Log during start up:
-------------------------
2020-10-20T06:05:05.540456Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 1048737)
2020-10-20T06:05:05.540495Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 524288)
After Restart:
-------------
mysql [localhost:5731] {msandbox} ((none)) > SHOW GLOBAL VARIABLES LIKE '%table_open_cache%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| table_open_cache | 431 |
| table_open_cache_instances | 16 |
+----------------------------+-------+
2 rows in set (0.00 sec)
static bool fix_table_cache_size(sys_var *, THD *, enum_var_type) {
/*
table_open_cache parameter is a soft limit for total number of objects
in all table cache instances. Once this value is updated we need to
update value of a per-instance soft limit on table cache size.
*/
table_cache_size_per_instance = table_cache_size / table_cache_instances;
return false;
}
static Sys_var_ulong Sys_table_cache_size(
"table_open_cache",
"The number of cached open tables "
"(total for all table cache instances)",
GLOBAL_VAR(table_cache_size), CMD_LINE(REQUIRED_ARG),
VALID_RANGE(1, 512 * 1024), DEFAULT(TABLE_OPEN_CACHE_DEFAULT),
BLOCK_SIZE(1), NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(nullptr),
ON_UPDATE(fix_table_cache_size), nullptr,
/* table_open_cache is used as a sizing hint by the performance schema. */
sys_var::PARSE_EARLY);
Description: table_open_cache value doesn't auto adjust when the variable is updated at run time. It will be auto adjusted only during start up. I feel it should be auto adjusted ON_UPDATE so that value is consistent irrespective of whether it is set at runtime or not. How to repeat: Before: ------ mysql [localhost:5731] {msandbox} ((none)) > SHOW GLOBAL VARIABLES LIKE '%table_open_cache%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | table_open_cache | 2000 | | table_open_cache_instances | 16 | +----------------------------+-------+ 2 rows in set (0.00 sec) Setting the dynamic value: -------------------------- mysql [localhost:5731] {msandbox} ((none)) > SET GLOBAL table_open_cache=524288; Query OK, 0 rows affected (0.00 sec) mysql [localhost:5731] {msandbox} ((none)) > SHOW GLOBAL VARIABLES LIKE '%table_open_cache%'; +----------------------------+--------+ | Variable_name | Value | +----------------------------+--------+ | table_open_cache | 524288 | | table_open_cache_instances | 16 | +----------------------------+--------+ 2 rows in set (0.00 sec) ---- Restarted ---- Error Log during start up: ------------------------- 2020-10-20T06:05:05.540456Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 1048737) 2020-10-20T06:05:05.540495Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 524288) After Restart: ------------- mysql [localhost:5731] {msandbox} ((none)) > SHOW GLOBAL VARIABLES LIKE '%table_open_cache%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | table_open_cache | 431 | | table_open_cache_instances | 16 | +----------------------------+-------+ 2 rows in set (0.00 sec) static bool fix_table_cache_size(sys_var *, THD *, enum_var_type) { /* table_open_cache parameter is a soft limit for total number of objects in all table cache instances. Once this value is updated we need to update value of a per-instance soft limit on table cache size. */ table_cache_size_per_instance = table_cache_size / table_cache_instances; return false; } static Sys_var_ulong Sys_table_cache_size( "table_open_cache", "The number of cached open tables " "(total for all table cache instances)", GLOBAL_VAR(table_cache_size), CMD_LINE(REQUIRED_ARG), VALID_RANGE(1, 512 * 1024), DEFAULT(TABLE_OPEN_CACHE_DEFAULT), BLOCK_SIZE(1), NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(nullptr), ON_UPDATE(fix_table_cache_size), nullptr, /* table_open_cache is used as a sizing hint by the performance schema. */ sys_var::PARSE_EARLY);