MySQL Config--参数TABLE_OPEN_CACHE设置过小导致Thread Running较高
2022/3/5 2:15:20
本文主要是介绍MySQL Config--参数TABLE_OPEN_CACHE设置过小导致Thread Running较高,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
问题描述
某服务器实例Thread Running屏藩报警,高峰期Thread Running数超过200:
通过查看活跃进程发现大量进程处于" Opening tables "或" closing tables " 状态,当前实例上存在945个数据库(schema)和11万多数据表(table)。
当前数据库实例配置为:
## 查询参数配置 SELECT * FROM information_schema.GLOBAL_VARIABLES AS T1 WHERE T1.VARIABLE_NAME IN( 'INNODB_OPEN_FILES', 'OPEN_FILES_LIMIT', 'TABLE_OPEN_CACHE_INSTANCES', 'TABLE_DEFINITION_CACHE', 'TABLE_OPEN_CACHE' ) ## 查询结果 +----------------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +----------------------------+----------------+ | INNODB_OPEN_FILES | 10000 | | OPEN_FILES_LIMIT | 65535 | | TABLE_OPEN_CACHE_INSTANCES | 1 | | TABLE_DEFINITION_CACHE | 4096 | | TABLE_OPEN_CACHE | 4000 | +----------------------------+----------------+
当前数据库实例状态值为:
## 查询STATUS SELECT * FROM information_schema.GLOBAL_STATUS AS T1 WHERE T1.VARIABLE_NAME LIKE '%OPEN%'; ## 输出结果 +----------------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +----------------------------+----------------+ | COM_HA_OPEN | 0 | | COM_SHOW_OPEN_TABLES | 0 | | INNODB_NUM_OPEN_FILES | 10000 | | OPEN_FILES | 20 | | OPEN_STREAMS | 0 | | OPEN_TABLE_DEFINITIONS | 4096 | | OPEN_TABLES | 4000 | | OPENED_FILES | 5117677500 | | OPENED_TABLE_DEFINITIONS | 4874024138 | | OPENED_TABLES | 6077542840 | | SLAVE_OPEN_TEMP_TABLES | 0 | | TABLE_OPEN_CACHE_HITS | 179625030889 | | TABLE_OPEN_CACHE_MISSES | 6077541902 | | TABLE_OPEN_CACHE_OVERFLOWS | 6077426207 | +----------------------------+----------------+
相关参数解释:
OPEN_TABLES: The number of tables that are open. OPENED_TABLES: The number of tables that have been opened. If Opened_tables is big, your table_open_cache value is probably too small. Open_files: The number of files that are open. This count includes regular files opened by the server. It does not include other types of files such as sockets or pipes. Also, the count does not include files that storage engines open using their own internal functions rather than asking the server level to do so. Opened_files: The number of files that have been opened with my_open() (a mysys library function). Parts of the server that open files without using this function do not increment the count. 参考资料:https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html#statvar_Opened_files
由于OPENED_FILES和OPENED_TABLES的值均远大于OPEN_FILES和OPEN_TABLES,因此怀疑TABLE_OPEN_CACHE参数值设置过低导致,将TABLE_OPEN_CACHE从4000调整为30000后,发现Thread Running监控趋于平稳:
在Percona Server 5.7.26版本中,使用SHOW STATUS显示的结果中部分STATUS的值为0,但使用information_schema.GLOBAL_STATUS 表能获取到值,怀疑存在BUG。
这篇关于MySQL Config--参数TABLE_OPEN_CACHE设置过小导致Thread Running较高的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-06-25MySQL报错Duplicate entry '0' for key 'PRIMARY'
- 2024-05-29阿里 Canal 实时同步 MySQL 增量数据至 ClickHouse 数据库
- 2024-05-24在Linux下管理MySQL的大小写敏感性
- 2024-04-26MySQL查出时间比实际晚8小时的解决方案
- 2024-04-01JPA不识别MySQL的枚举类型
- 2024-03-30mysql数据库表卡死解决方法
- 2024-03-15MySQL多数据源笔记5-ShardingJDBC实战
- 2024-03-11natural join mysql
- 2024-03-11关于VS2017,VS2015 中利用 EF使用Mysql 不显示数据源问题解决方案
- 2024-02-26mysql 阿里云xb后缀备份文件恢复-icode9专业技术文章分享