【案例】MySQL table_open_cache配置过小导致thread running过高告警

前言

mysql突然接到thread running过高的告警。首先分析监控指标并未发现明显异常。进入mysql执行show processlist发现大量SQL处于opening tables和closing tables状态。初步怀疑是table_open_cache 参数设置过小。紧接着调大table_open_cahe参数后，告警恢复正常。

现象

1、mysql版本8.0.22
2、thread running迅速增长至500
3、执行show processlist发现大量SQL处于opening tables和closing tables状态

问题

本文主要以问题为切入点。

• 为什么thread running会突增
• 为什么执行processlist是会有大量的opening tables和closing tables。
• 为什么调整完table_open_cahe后就恢复正常了。

分析

1、thread running很高通常意味着数据库正在处理大量的并发连接或查询。先查看指标监控，分析数据库是否存在性能瓶颈。检查cpu，内存，io等指标，并未发现明显异常，用户也未反馈异常。证明当前实例还能正常提供服务。无需紧急介入恢复，可以先分析原因。初步确定排查方向通常有以下几种可能：慢查询，事务锁，连接泄露等。

2、进一步验证猜想：登录mysql实例，查看当前有哪些线程正在运行。

show processlist;

发现有大量的线程长时间处于opening tables状态。表明数据库在处理查询时，频繁的打开与关闭表。

3、正常情况下mysql默认有了表缓存，不应该出现频繁的打开与关闭表。难道是配置太低了？

# 查看配置
SHOW VARIABLES LIKE 'table_open_cache%';

结果显示为：512。而table_open_cache_instances 为16

4、查看 Open_tables 和 Opened_tables 的值

SHOW GLOBAL STATUS LIKE 'Open_tables';
SHOW GLOBAL STATUS LIKE 'Opened_tables';

发现 Open_tables 的值为 512，与 table_open_cache 相等，而 Opened_tables 的值非常高，表明表缓存频繁地被填满并清理。

5、这个实例业务有1000张表，明显大于open_tables。怀疑是open_tables被打满了

6、调大 table_open_cache 参数值，减少业务表频繁打开和关闭。

SET GLOBAL table_open_cache=2048;
SET GLOBAL table_open_cache_instances=128;

注意：这个数值需要根据mysql的实例规格来确定，设置过大可能导致内存不够用。通常 max_connections 和table_open_cache_instances 的默认比例为（1:16）。

7、结果验证：发现 “opening table” 状态的查询进程数量显著减少，thread
running 告警恢复。

原理

在没有表缓存实例的情况下，所有的客户端会话共享一个全局的表缓存。这意味着，当多个会话同时访问数据库时，它们可能会争用同一个表的缓存条目，从而导致锁竞争和性能下降。

表缓存实例的引入，通过将全局表缓存分割成多个较小的缓存区域，为每个会话提供了更独立的资源，从而减少了会话间的锁竞争，提高了数据库的可伸缩性。

通过设置 table_open_cache_instances，可以将打开的表缓存划分为多个较小的缓存实例。这样，每个会话只需要锁定它需要访问的一个实例，而不是整个表缓存。这有助于减少会话间的锁竞争，特别是在并发访问量高的情况下，可以显著提高数据库的并发处理能力。

总结

最终通过调整table_open_cache与table_open_cache_instances参数，恢复异常告警。核心原因为参数配置过小，无法满足高并发场景下的查询访问。