一.table_cache 相关参数
1.open_files_limit
2.max_connections
3.table_open_cache
4.table_definition_cache
问题?
经常会看到自己my.cnf设置的那四个值和在MySQL中看到的值不一样.
我修改了其中一个参数,结构发现所有的上面的四个参数都发生了变化。
参数到底应该设置为多少合适?
二.源码分析之初始化
1.常量定义
源码位置:/sql/sql_const.h
#define TABLE_OPEN_CACHE_MIN 400
#define TABLE_OPEN_CACHE_DEFAULT 2000
#define TABLE_DEF_CACHE_DEFAULT 400
#define MAX_CONNECTIONS_DEFAULT 151
#define TABLE_DEF_CACHE_MIN 400
源码位置:/mysql-5.7.33/my_global.h
#ifdef _WIN32
#define MY_FILE_MIN 2048
#else
#define MY_FILE_MIN 0
#endif
#ifdef _WIN32
#define MY_NFILE (16384 + MY_FILE_MIN)
#else
#define MY_NFILE 64
#endif
#define OS_FILE_LIMIT UINT_MAX
2.变量静态文件
源码位置:/mysql-5.7.33/sql/sys_vars.cc
static Sys_var_ulong Sys_open_files_limit(
"open_files_limit",
"If this is not 0, then mysqld will use this value to reserve file "
"descriptors to use with setrlimit(). If this value is 0 then mysqld "
"will reserve max_connections*5 or max_connections + table_open_cache*2 "
"(whichever is larger) number of file descriptors",
READ_ONLY GLOBAL_VAR(open_files_limit), CMD_LINE(REQUIRED_ARG),
VALID_RANGE(0, OS_FILE_LIMIT), DEFAULT(0), BLOCK_SIZE(1),
NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(NULL), ON_UPDATE(NULL),
NULL,
sys_var::PARSE_EARLY);
static Sys_var_ulong Sys_max_connections(
"max_connections", "The number of simultaneous clients allowed",
GLOBAL_VAR(max_connections), CMD_LINE(REQUIRED_ARG),
VALID_RANGE(1, 100000),
DEFAULT(MAX_CONNECTIONS_DEFAULT),
BLOCK_SIZE(1),
NO_MUTEX_GUARD,
NOT_IN_BINLOG,
ON_CHECK(0),
ON_UPDATE(0),
NULL,
sys_var::PARSE_EARLY);
static bool fix_table_cache_size(sys_var *self, THD *thd, enum_var_type type)
{
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(NULL),
ON_UPDATE(fix_table_cache_size),
NULL,
sys_var::PARSE_EARLY);
static Sys_var_ulong Sys_table_cache_instances(
"table_open_cache_instances", "The number of table cache instances",
READ_ONLY GLOBAL_VAR(table_cache_instances), CMD_LINE(REQUIRED_ARG),
VALID_RANGE(1, Table_cache_manager::MAX_TABLE_CACHES),
DEFAULT(Table_cache_manager::DEFAULT_MAX_TABLE_CACHES),
BLOCK_SIZE(1), NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(NULL),
ON_UPDATE(NULL), NULL,
sys_var::PARSE_EARLY);
static Sys_var_ulong Sys_table_def_size(
"table_definition_cache",
"The number of cached table definitions",
GLOBAL_VAR(table_def_size),
CMD_LINE(REQUIRED_ARG, OPT_TABLE_DEFINITION_CACHE),
VALID_RANGE(TABLE_DEF_CACHE_MIN, 512*1024),
DEFAULT(TABLE_DEF_CACHE_DEFAULT),
BLOCK_SIZE(1),
NO_MUTEX_GUARD,
NOT_IN_BINLOG,
ON_CHECK(NULL),
ON_UPDATE(NULL),
NULL,
sys_var::PARSE_EARLY);
三.源码分析之具体流程
1.初始化流程
位置:/mysql-5.7.33/sql/mysqld.cc
#endif
init_sql_statement_names();
sys_var_init(); //系统参数初始化
ulong requested_open_files; //requested_open_files 非常重要的变量
adjust_related_options(&requested_open_files); //
#ifdef WITH_PERFSCHEMA_STORAGE_ENGINE
if (ho_error == 0)
{
if (!opt_help && !opt_bootstrap)
{
/* Add sizing hints from the server sizing parameters. */ //下面这些参数会根据服务器的相关参数进行调整,也就是adjust_related_options这个函数返回的值去设置下面的参数
pfs_param.m_hints.m_table_definition_cache= table_def_size;
pfs_param.m_hints.m_table_open_cache= table_cache_size;
pfs_param.m_hints.m_max_connections= max_connections;
pfs_param.m_hints.m_open_files_limit= requested_open_files;
pfs_param.m_hints.m_max_prepared_stmt_count= max_prepared_stmt_count;
PSI_hook= initialize_performance_schema(&pfs_param);
if (PSI_hook == NULL && pfs_param.m_enabled)
{
pfs_param.m_enabled= false;
sql_print_warning("Performance schema disabled (reason: init failed).");
}
}
}
#else
.......
void adjust_related_options(ulong *requested_open_files)
{
/* In bootstrap, disable grant tables (we are about to create them) */
if (opt_bootstrap)
opt_noacl= 1;
//由于这几个参数会互相影响对方的值,所以可以看出来这几个参数的初始化顺序,以及依赖很关键
/* The order is critical here, because of dependencies. */
adjust_open_files_limit(requested_open_files);
adjust_max_connections(*requested_open_files);
adjust_table_cache_size(*requested_open_files);
adjust_table_def_size();
}
...........
2.open_files_limit相关
位置:/mysql-5.7.33/sql/mysqld.cc
void adjust_open_files_limit(ulong *requested_open_files)
{
ulong limit_1;
ulong limit_2;
ulong limit_3;
ulong request_open_files;
ulong effective_open_files;
/* MyISAM requires two file handles per table. */
limit_1= 10 + max_connections + table_cache_size * 2;
/*
We are trying to allocate no less than max_connections*5 file
handles (i.e. we are trying to set the limit so that they will
be available).
*/
limit_2= max_connections * 5;
/* Try to allocate no less than 5000 by default. */
limit_3= open_files_limit ? open_files_limit : 5000;
request_open_files= max<ulong>(max<ulong>(limit_1, limit_2), limit_3);
/* Notice: my_set_max_open_files() may return more than requested. */
effective_open_files= my_set_max_open_files(request_open_files); //这个函数非常重要,下面的值都要根据这个去判断 my_set_max_open_files
if (effective_open_files < request_open_files)
{
if (open_files_limit == 0)
{
sql_print_warning("Changed limits: max_open_files: %lu (requested %lu)",
effective_open_files, request_open_files);
}
else
{
sql_print_warning("Could not increase number of max_open_files to "
"more than %lu (request: %lu)",
effective_open_files, request_open_files);
}
}
open_files_limit= effective_open_files;
if (requested_open_files)
*requested_open_files= min<ulong>(effective_open_files, request_open_files);
}
my_set_max_open_files
/*
Change number of open files
SYNOPSIS:
my_set_max_open_files()
files Number of requested files
RETURN
number of files available for open
*/
uint my_set_max_open_files(uint files)
{
struct st_my_file_info *tmp;
DBUG_ENTER("my_set_max_open_files");
DBUG_PRINT("enter",("files: %u my_file_limit: %u", files, my_file_limit));
files+= MY_FILE_MIN; //0
files= set_max_open_files(MY_MIN(files, OS_FILE_LIMIT)); //OS_FILE_LIMIT = UINT_MAX 类型为 unsigned int 的变量的最大值。 4294967295,这里应该为0
if (files <= MY_NFILE)
DBUG_RETURN(files);
if (!(tmp= (struct st_my_file_info*) my_malloc(key_memory_my_file_info,
sizeof(*tmp) * files,
MYF(MY_WME))))
DBUG_RETURN(MY_NFILE);
/* Copy any initialized files */
memcpy((char*) tmp, (char*) my_file_info,
sizeof(*tmp) * MY_MIN(my_file_limit, files));
memset((tmp + my_file_limit), 0,
MY_MAX((int) (files - my_file_limit), 0) * sizeof(*tmp));
my_free_open_file_info(); /* Free if already allocated */
my_file_info= tmp;
my_file_limit= files;
DBUG_PRINT("exit",("files: %u", files));
DBUG_RETURN(files);
}
/*
This value is certainly wrong on all 64bit platforms,
and also wrong on many 32bit platforms.
It is better to get a compile error, than to use a wrong value.
#ifndef RLIM_INFINITY
#define RLIM_INFINITY ((uint) 0xffffffff)
#endif
*/
static uint set_max_open_files(uint max_file_limit)
{
struct rlimit rlimit;
uint old_cur;
DBUG_ENTER("set_max_open_files");
DBUG_PRINT("enter",("files: %u", max_file_limit));
if (!getrlimit(RLIMIT_NOFILE,&rlimit)) //判断是否获取成功,获得open file的当前值 ulimit -n /或者是服务中的这个LimitNOFILE
{
old_cur= (uint) rlimit.rlim_cur; //拿到RLIMIT_NOFILE软限制
DBUG_PRINT("info", ("rlim_cur: %u rlim_max: %u",
(uint) rlimit.rlim_cur,
(uint) rlimit.rlim_max));
if (rlimit.rlim_cur == (rlim_t) RLIM_INFINITY) //RLIM_INFINITY表示对资源不做限制,如果得到的值是unlimited
rlimit.rlim_cur = max_file_limit;
if (rlimit.rlim_cur >= max_file_limit)
DBUG_RETURN(rlimit.rlim_cur); /* purecov: inspected */
rlimit.rlim_cur= rlimit.rlim_max= max_file_limit;
if (setrlimit(RLIMIT_NOFILE, &rlimit))
max_file_limit= old_cur; /* Use original value */ //655350
else
{
rlimit.rlim_cur= 0; /* Safety if next call fails */
(void) getrlimit(RLIMIT_NOFILE,&rlimit); //655350
DBUG_PRINT("info", ("rlim_cur: %u", (uint) rlimit.rlim_cur));
if (rlimit.rlim_cur) /* If call didn't fail */
max_file_limit= (uint) rlimit.rlim_cur; //655350
}
}
DBUG_PRINT("exit",("max_file_limit: %u", max_file_limit));
DBUG_RETURN(max_file_limit);
}
#else
static uint set_max_open_files(uint max_file_limit)
{
/* We don't know the limit. Return best guess */
return MY_MIN(max_file_limit, OS_FILE_LIMIT);
}
3.max_connection
void adjust_max_connections(ulong requested_open_files)
{
ulong limit;
limit= requested_open_files - 10 - TABLE_OPEN_CACHE_MIN * 2;
if (limit < max_connections)
{
sql_print_warning("Changed limits: max_connections: %lu (requested %lu)",
limit, max_connections);
// This can be done unprotected since it is only called on startup.
max_connections= limit;
}
}
4.table_open_cache
void adjust_table_cache_size(ulong requested_open_files)
{
ulong limit;
limit= max<ulong>((requested_open_files - 10 - max_connections) / 2,
TABLE_OPEN_CACHE_MIN);
if (limit < table_cache_size)
{
sql_print_warning("Changed limits: table_open_cache: %lu (requested %lu)",
limit, table_cache_size);
table_cache_size= limit;
}
table_cache_size_per_instance= table_cache_size / table_cache_instances;
}
5.table_definition_cache
void adjust_table_def_size()
{
ulong default_value;
sys_var *var;
default_value= min<ulong> (400 + table_cache_size / 2, 2000);
var= intern_find_sys_var(STRING_WITH_LEN("table_definition_cache"));
DBUG_ASSERT(var != NULL);
var->update_default(default_value);
if (! table_definition_cache_specified)
table_def_size= default_value;
}
四.取值
1.open_files_limit
limit_1= 10 + max_connections + table_cache_size * 2;
limit_2= max_connections * 5;
limit_3= open_files_limit ? open_files_limit : 5000;
request_open_files= max(max(limit_1, limit_2), limit_3);
2.max_connections
min(requested_open_files - 10 - TABLE_OPEN_CACHE_MIN * 2,max_connections)
3.table_open_cache
min(max((requested_open_files - 10 - max_connections) / 2, TABLE_OPEN_CACHE_MIN),table_cache_size)
4.table_definition_cache
配置文件有值取值优先
min (400 + table_cache_size / 2, 2000)
所以我们在真正遇到问题的时候,应该先从max_connections去入手。再去调节其他参数。