mysql8.0打开一张表的所有调用链如下源码以8.0.44版本为例open_tables() -sql/sql_base.cc:5759行SQL 层入口遍历语句中所有 Table_ref 链表申请 MDL 锁流程。open_and_process_table() -sql/sql_base.cc:4913行对单个 Table_ref 做分类处理区分临时表与普通表最终对普通表发起打开。open_table() -sql/sql_base.cc:2806行先查 Table Cache命中则直接复用未命中则获取 TABLE_SHARE分配新 TABLE 对象调用下层打开实体文件。open_table_from_share() -sql/table.cc:2877行根据 TABLE_SHARE 中的元数据初始化 TABLE 对象字段、索引、分区等然后通过存储引擎接口向下打开物理文件。handler::ha_open() -sql/handler.cc:2773行handler 基类的通用打开逻辑处理只读降级、统计信息初始化等公共事务再通过虚函数 open() 把打开表的动作到具体的存储引擎上。ha_innobase::open() -storage/innobase/handler/ha_innodb.cc:7191行InnoDB 引擎层入口解析表名、设置事务隔离相关参数调用 InnoDB 字典层加载表的内部元数据。dd_open_table() -storage/innobase/dict/dict0dd.cc:5404行从 MySQL Data Dictionary 读取表定义构建 InnoDB 内部的 dict_table_t 结构并触发表空间文件的关联与打开。fil_ibd_open() -storage/innobase/fil/fil0fil.cc:5777行构造 Datafile 对象调用 open_read_only 打开表空间文件。Datafile::open_read_only() -storage/innobase/fsp/fsp0file.cc:112行InnoDB 表空间文件对象的只读打开封装调用 OS 抽象层的简化打开接口。os_file_create_simple_no_error_handling- os_file_create_simple_no_error_handling_func() -storage/innobase/os/os0file.cc:3270行os_file_create_simple_no_error_handling是os_file_create_simple_no_error_handling_func函数的包装宏。在os_file_create_simple_no_error_handling_func函数中InnoDB 跨平台的 OS 抽象层直接发起open()系统调用。::open() -POSIX 系统调用对于打开一张表的操作操作系统在这个过程中做的事情主要有找到文件路径解析inode定位、确认权限、通过open(2)分配访问句柄fd。真正的磁盘 I/O 发生在后续查询执行阶段而不是打开阶段。操作系统成功打开后fd 沿调用链一路返回给 InnoDB 的 fil system后续所有对该表的读写 I/O 都通过这个 fd 进行。【3.4 table_open_cache与innodb_open_files】【3.4.1 innodb_open_files介绍】引入一个场景某数据库有压测的需求需要大量的并发连接数当把max_connection调整到30000table_open_cache也调整到30000发现偶尔还是有进程卡在open table状态然后检查了一下当Open_tables是30000的时候 mysql实例实际使用的文件句柄仅仅只有25000左右同时该实例长期有1w左右的连接数说明mysql实例实际使用的文件句柄数不尽尽和打开表的数量有关还应该有别的参数在控制这一过程。mysql SHOW GLOBAL STATUS LIKE ‘Open_tables%’;±--------------±------| Variable_name | Value |±--------------±------| Open_tables | 30000 |±--------------±------1 row in set (0.01 sec)[***** ~]$ sudo ls /proc/4146482/fd | wc -l25706从3.2.2的源码流程可以看出当mysql试图打开一张表时首先会在内存的TableCache中创建一个Table对象这个对象仅仅和内存使用有关系也就是table_open_cache的数量只对server层有意义实际在打开文件的是innodb层innodb实际上也维护了一个自己的缓存池用于缓存打开的InnoDB .ibd 文件使用innodb_open_files参数控制可以同时打开的.ibd文件数量的大小。【3.4.2 源码分析】//默认值0 PLUGIN_VAR_READONLY代表启动mysql实例后不可更改static MYSQL_SYSVAR_LONG(open_files, innobase_open_files, PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY,“How many files at the maximum InnoDB keeps open at the same time.”,nullptr, nullptr, 0L, 0L, INT32_MAX, 0);下面的代码片段展示了innobase_open_files的初始化过程1.当innobase_open_files没有显式设置时(初始值为0),会有一个300的兜底值如果srv_file_per_table打开了且table_open_cache大于300则把innobase_open_files调整到和table_open_cache相等。因为当srv_file_per_table打开时,每一张表对应一个.ibd文件,文件句柄要和表缓存匹配否则缓存会没有意义。2.当innobase_open_files的值超过open_files_limit的时候会触发一个warn并且强制截断到table_open_cache。//sql/sys_vars.cc:5111//这里放上来说明用户视角的table_open_cache变量绑定的cpp变量是table_cache_size文章在后面不会区分这两个名词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);//storage/innobase/handler/ha_innodb.cc:4949行if (innobase_open_files 10) {innobase_open_files 300;if (srv_file_per_table table_cache_size 300) {innobase_open_files table_cache_size;}}if (innobase_open_files (long)open_files_limit) {ib::warn(ER_IB_MSG_539) “innodb_open_files should not be greater” than the open_files_limit.\n;if (innobase_open_files (long)table_cache_size) {innobase_open_files table_cache_size;}}//storage/innobase/srv/srvOstart.cc:1661行fil_init(innobase_get_open_files_limit());//storage/innobase/handler/ha_innodb.cc:5112行long innobase_get_open_files_limit() { return innobase_open_files; }//storage/innobase/fil/fil0fil.cc:3618行void fil_init(ulint max_n_open) {static_assert((1 UNIV_PAGE_SIZE_SHIFT_MAX) UNIV_PAGE_SIZE_MAX,“(1 UNIV_PAGE_SIZE_SHIFT_MAX) ! UNIV_PAGE_SIZE_MAX”);static_assert((1 UNIV_PAGE_SIZE_SHIFT_MIN) UNIV_PAGE_SIZE_MIN,“(1 UNIV_PAGE_SIZE_SHIFT_MIN) ! UNIV_PAGE_SIZE_MIN”);ut_a(fil_system nullptr);ut_a(max_n_open 0);fil_system ut::new_withkey(UT_NEW_THIS_FILE_PSI_KEY, MAX_SHARDS,max_n_open);}对于3.4.1的场景来说该实例的innodb_open_files只有12000所以该实例使用的文件句柄数大概是12000(ibd)11000(连接数)系统文件占用的文件句柄(binlog,redolog,系统表等) 约等于25000多。当我们需要提高某个mysql实例的文件缓存数目时正确的做法应该是同时提高table_open_cache和innodb_open_files两个参数而不是单纯提高table_open_cache。【3.5 常用运维操作】– 查看当前配置值SHOW VARIABLES LIKE ‘table_open_cache%’;–监控表打开数量SHOW GLOBAL STATUS LIKE ‘Open_tables%’; /当前打开表的数量/SHOW GLOBAL STATUS LIKE ‘Opened_tables’;/历史总打开表的数量/–监控缓存使用情况SHOW GLOBAL STATUS LIKE ‘Table_open_cache%’;/*Table_open_cache_hits 代表打开表时缓存命中次数Table_open_cache_misses 代表打开表时缓存未命中次数Table_open_cache_overflows 代表缓存溢出次数使用完一个TABLE 对象后尝试将其返回缓存但缓存已满会从缓存中驱逐一个对象*/– 计算缓存命中率SELECThits,misses,ROUND(hits / (hits misses) * 100, 2) AS hit_ratio_pctFROM (SELECT(SELECT VARIABLE_VALUE FROM performance_schema.global_statusWHERE VARIABLE_NAME ‘Table_open_cache_hits’) 0 AS hits,(SELECT VARIABLE_VALUE FROM performance_schema.global_statusWHERE VARIABLE_NAME ‘Table_open_cache_misses’) 0 AS misses) t;– 关闭所有空闲表清空缓存FLUSH TABLES;– 只刷新指定表FLUSH TABLES db_name.table_name;– 清零所有 status 计数重新观察命中率变化趋势FLUSH STATUS;【3.6 总结】第一层OS 层 — open_files_limit管的是整个进程的文件描述符总数。mysqld 启动时会向操作系统申请实际拿到多少取决于配置值和 ulimit -n 谁更小结果可以通过 SHOW VARIABLES LIKE ‘open_files_limit’ 确认。这个池子是共享的——socket、binlog、redo log、.ibd 文件全都从这里使用。第二层Server 层 — table_open_cache管的是 Server 层缓存的 TABLE 对象数量每张打开的表对应一个里面存储表的元数据、统计信息等。超出上限后按 LRU 淘汰最久没用的但淘汰 TABLE 对象不等于释放 fd具体需要看存储层的处理。table_open_cache_instances默认 16把缓存分成多个分片主要是为了降低锁竞争。第三层InnoDB 引擎层 — innodb_open_files管的是 InnoDB 内部实际持有的 .ibd 文件句柄数。fil_system 维护了一个独立的 fd LRU 链表超出上限就关掉最久没访问的句柄下次用到再重新 open。mysql实例具体真正使用了多少文件句柄由它决定。启动时 MySQL 会自动检查确保 innodb_open_files 不超过 open_files_limit超了也没意义。默认值一般取 min(table_open_cache, 300)。【4. 线程与连接】【4.1 mysql是如何管理连接的】mysql官方文档max_connections直接控制mysql可以允许的最大连接数如果服务器因达到 max_connections 限制而拒绝连接则会递增 Connection_errors_max_connections 状态变量。mysqld 实际上允许 max_connections 1 个客户端连接。额外的一个连接保留给拥有 CONNECTION_ADMIN 权限的账户使用。通过将该权限授予管理员而非普通用户,管理员即使在最大数量的非特权客户端已连接的情况下仍可连接服务器并使用 SHOW PROCESSLIST 诊断问题。同时mysql也支持配置特殊的管理接口(8.0.14新增),在my.cnf增加两行[mysqld]admin_address127.0.0.1 # 管理接口监听的 IPadmin_port33062 # 管理接口的端口默认就是 33062通过管理员接口进入的连接也能够无视max_connections的限制。相关源码如下//sql/conn_handler/connection_handler_manager.cc:104行bool Connection_handler_manager::check_and_incr_conn_count(bool is_admin_connection) {bool connection_accepted true;mysql_mutex_lock(LOCK_connection_count);//先检查再1 实际上允许max_connections 1个连接if (connection_count max_connections !is_admin_connection) {connection_accepted false;m_connection_errors_max_connection;} else {connection_count;if (connection_count max_used_connections) { max_used_connections connection_count; max_used_connections_time time(nullptr); }}mysql_mutex_unlock(LOCK_connection_count);return connection_accepted;}//sql/auth/sql_authentication.cc:3740行static inline bool check_restrictions_for_com_connect_command(THD *thd) {//管理员接口 必须是有SERVICE_CONNECTION_ADMIN权限的用户才能连接if (thd-is_admin_connection() !thd-m_main_security_ctx.has_global_grant(STRING_WITH_LEN(“SERVICE_CONNECTION_ADMIN”)).first) {my_error(ER_SPECIFIC_ACCESS_DENIED_ERROR, MYF(0),“SERVICE_CONNECTION_ADMIN”);return true;}//对于没有高级权限的普通用户如果并发连接数已达上限 max_connections则拒绝新连接。//拥有上述任一特权的用户不受此限制可以突破 max_connections 上限登录if (!(thd-m_main_security_ctx.check_access(SUPER_ACL) ||thd-m_main_security_ctx.has_global_grant(STRING_WITH_LEN(“CONNECTION_ADMIN”)).first ||thd-m_main_security_ctx.has_global_grant(STRING_WITH_LEN(“SERVICE_CONNECTION_ADMIN”)).first)) {if (!Connection_handler_manager::get_instance()-valid_connection_count()) { // too many connectionsmy_error(ER_CON_COUNT_ERROR, MYF(0));return true;}}return false;}【4.2 thread_stack和thread_cache_size】【4.2.1 thread_stack线程栈大小】thread_stack 控制 MySQL 为每个连接线程分配的栈空间大小,MySQL 8.0 将默认值统一调整为 1MB。//include/my_thread.h:55#define DEFAULT_THREAD_STACK (1024UL * 1024UL)//sql/sys_vars.cc:3989static Sys_var_ulong Sys_thread_stack(“thread_stack”, “The stack size for each thread”,READ_ONLY GLOBAL_VAR(my_thread_stack_size), CMD_LINE(REQUIRED_ARG),#if defined(clang) defined(HAVE_UBSAN)// Clang with DEBUG needs more stack, esp. with UBSAN.VALID_RANGE(DEFAULT_THREAD_STACK, ULONG_MAX),#elseVALID_RANGE(128 * 1024, ULONG_MAX),#endifDEFAULT(DEFAULT_THREAD_STACK), BLOCK_SIZE(1024));thread_stack 不仅在创建线程时起作用在查询执行过程中也是实时检测的依据。//sql/check_stack.cc:110行bool check_stack_overrun(const THD *thd, long margin, unsigned char *buf) {assert(thd current_thd);assert(stack_direction -1 || stack_direction 1);long stack_used used_stack(thd-thread_stack, reinterpret_cast(stack_used));if (stack_used static_cast(my_thread_stack_size - margin) ||DBUG_EVALUATE_IF(“simulate_stack_overrun”, true, false)) {if (buf ! nullptr) buf[0] ‘\0’;char *ebuff new (std::nothrow) char[MYSQL_ERRMSG_SIZE];if (ebuff) {snprintf(ebuff, MYSQL_ERRMSG_SIZE,ER_THD(thd, ER_STACK_OVERRUN_NEED_MORE), stack_used,my_thread_stack_size, margin);my_message(ER_STACK_OVERRUN_NEED_MORE, ebuff, MYF(ME_FATALERROR));delete[] ebuff;}return true;}#ifndef NDEBUGmax_stack_used std::max(max_stack_used.load(), stack_used);#endifreturn false;}mysql在运行过程中不会真的等到了栈溢出了报错而是选择距离栈底margin直接时就主动报错。margin值的来源如下//sql/sql_const.h:139行#if defined HAVE_UBSAN SIZEOF_CHARP 4constexpr const long STACK_MIN_SIZE{30000}; // Abort if less stack during eval.#elseconstexpr const long STACK_MIN_SIZE{20000}; // Abort if less stack during eval.#endif//sql/sp_head.cc:2013行/*Just reporting a stack overrun error(sa check_stack_overrun()) requires stack memory for errormessage buffer. Thus, we have to put the below checkrelatively close to the beginning of the execution stack,where available stack margin is still big. As long as the checkhas to be fairly high up the call stack, the amount of memorywe “book” for has to stay fairly high as well, and hencenot very accurate. The number below has been calculatedby trial and error, and reflects the amount of memory necessaryto execute a single stored procedure instruction, be it eitheran SQL statement, or, heaviest of all, a CALL, which involvesparsing and loading of another stored procedure into the cache(sa db_load_routine() and Bug#10100).TODO: that should be replaced by proper handling of stack overrun error. Stack size depends on the platform: - for most platforms (8 * STACK_MIN_SIZE) is enough; - for Solaris SPARC 64 (10 * STACK_MIN_SIZE) is required. - for clang and ASAN/UBSAN we need even more stack space.*/{#if defined(clang) defined(HAVE_ASAN)const int sp_stack_size 12 * STACK_MIN_SIZE;#elif defined(clang) defined(HAVE_UBSAN)const int sp_stack_size 16 * STACK_MIN_SIZE;#elseconst int sp_stack_size 8 * STACK_MIN_SIZE;#endifif (check_stack_overrun(thd, sp_stack_size, (uchar *)old_packet)) return true;}可以看到存储过程设置的margin是普通查询的816倍这就是为什么深度嵌套的存储过程特别容易触发 Thread stack overrun。mysql源码在注释中也解释了这一设计的目的仅仅报告栈溢出错误参见 check_stack_overrun()就需要占用栈内存来存放错误消息缓冲区。因此我们必须将以下检查放在执行栈相对靠近开头的位置此时可用的栈余量还比较充裕。由于该检查必须位于调用栈的较高位置我们为其预留的内存量也必须保持较高水平因而不会非常精确。下面的数值是通过反复试验得出的反映了执行单条存储过程指令所需的内存量——无论是 SQL 语句还是其中最重的 CALL 语句涉及将另一个存储过程解析并加载到缓存中参见 db_load_routine() 和 Bug#10100。【4.2.2 thread_cache_size线程缓存数量】thread_cache_size的变量定义如下//sql/sys_vars.cc:5150static Sys_var_ulong Sys_thread_cache_size(“thread_cache_size”, “How many threads we should keep in a cache for reuse”,GLOBAL_VAR(Per_thread_connection_handler::max_blocked_pthreads),CMD_LINE(REQUIRED_ARG, OPT_THREAD_CACHE_SIZE), VALID_RANGE(0, 16384),DEFAULT(0), BLOCK_SIZE(1), NO_MUTEX_GUARD, NOT_IN_BINLOG, nullptr,ON_UPDATE(modify_thread_cache_size));缓存中的线程连接断开后该工作线程不立即销毁而是挂起block在条件变量上等待复用当新连接到来时直接唤醒该线程免去创建/销毁线程的开销。线程一旦超出缓存上限才会真正退出。下面是线程缓存的工作代码//sql/conn_handler/connection_handler_per_thread.cc:144行Channel_info *Per_thread_connection_handler::block_until_new_connection() {Channel_infonew_conn nullptr;mysql_mutex_lock(LOCK_thread_cache);if (blocked_pthread_count max_blocked_pthreads !shrink_cache) {/Don’t kill the pthread, just block it for reuse */DBUG_PRINT(“info”, (“Blocking pthread for reuse”));/* mysys_var is bound to the physical thread, so make sure mysys_var-dbug is reset to a clean state before picking another session in the thread cache. */ DBUG_POP(); assert(!_db_is_pushed_()); // Block pthread blocked_pthread_count; while (!connection_events_loop_aborted() !wake_pthread !shrink_cache) mysql_cond_wait(COND_thread_cache, LOCK_thread_cache); blocked_pthread_count--; if (shrink_cache blocked_pthread_count max_blocked_pthreads) { mysql_cond_signal(COND_flush_thread_cache); } if (wake_pthread) { wake_pthread--; if (!waiting_channel_info_list-empty()) { new_conn waiting_channel_info_list-front(); waiting_channel_info_list-pop_front(); DBUG_PRINT(info, (waiting_channel_info_list-pop %p, new_conn)); } else { assert(0); // We should not get here. } }}mysql_mutex_unlock(LOCK_thread_cache);return new_conn;}只有当当前缓存的空闲线程数 允许的最大值thread_cache_size且没有在缩减缓存shrink_cache时才去缓存这个线程 否则直接返回nullptr。线程进入缓存 blocked_pthread_count随后线程在条件变量COND_thread_cache上睡眠三个退出条件connection_events_loop_aborted() mysql正在关闭wake_pthread有新连接到来需要唤醒线程shrink_cache有主动收缩线程缓存。当线程唤醒时从等待队列的头部取出新连接的Channel_info并返回。【4.3 线程与系统资源的关系】mysql官方文档mysql启动时会为以下这些项目申请内存InnoDB Buffer Pool打开表缓存每个连接线程的内存内部临时表Performance Schema 内存blob列缓冲其他本节重点介绍每个连接线程的内存的使用情况是什么样子的。【4.3.1 线程栈】线程栈是 OS 在 pthread_create 时分配的内核资源不经过 MySQL 的 malloc由 thread_stack 系统变量控制其大小默认1M在4.2.1节有介绍【4.3.2 NET I/O 缓冲区】The connection buffer and result buffer each begin with a size equal to net_buffer_length bytes, but are dynamically enlarged up to max_allowed_packet bytes as needed. The result buffer shrinks to net_buffer_length bytes after each SQL statement. While a statement is running, a copy of the current statement string is also allocated.I/O 缓冲区承担着所有网络数据的收发在连接对象创建之后紧接着mysql就会为连接申请I/O 缓冲区。它的初始大小由变量net_buffer_length控制// sql/sql_client.cc:40// 设置初始包大小和最大包大小上限 默认值16kbnet-max_packet (uint)global_system_variables.net_buffer_length;net-max_packet_size max(net_buffer_length, max_allowed_packet);当接收到超过当前缓冲区大小数据的包时会动态扩容// sql-common/net_serv.cc:217bool net_realloc(NET *net, size_t length) {if (length net-max_packet_size) {// 超过 max_allowed_packet直接报错不扩容net-last_errno ER_NET_PACKET_TOO_LARGE;return true;}// 按 IO_SIZE对齐向上取整然后 my_reallocpkt_length (length IO_SIZE - 1) ~(IO_SIZE - 1);buff (uchar *)my_realloc(key_memory_NET_buff, net-buff,pkt_length NET_HEADER_SIZE COMP_HEADER_SIZE,MYF(MY_WME));net-buff_end buff (net-max_packet (ulong)pkt_length);}释放这一内存发生在连接彻底关闭时// sql-common/net_serv.cc:207void net_end(NET *net) {my_free(net-buff);net-buff nullptr;}当连接断开但是线程进入线程缓存等待复用时net_end()不会被调用,net io缓冲区不会被释放这意味着 thread_cache_size 越大驻留在缓存中的空闲线程越多常驻内存就越高。mysql的官方文档也解释了这一点。When a thread is no longer needed, the memory allocated to it is released and returned to the system unless the thread goes back into the thread cache. In that case, the memory remains allocated.【4.3.3 Sort 缓冲区】sort缓冲区是一个查询级别的缓冲区当线程执行的sql含有排序操作时分配查询结束后释放。sort缓冲区大小由sort_buffer_size控制sort_buffer_size是每一个排序操作所能申请的内存块大小的上限。//sql/sys_vars.cc:4857行static Sys_var_ulong Sys_sort_buffer(“sort_buffer_size”,“Each thread that needs to do a sort allocates a buffer of this size”,HINT_UPDATEABLE SESSION_VAR(sortbuff_size), CMD_LINE(REQUIRED_ARG),VALID_RANGE(MIN_SORT_MEMORY, ULONG_MAX), DEFAULT(DEFAULT_SORT_MEMORY),BLOCK_SIZE(1));