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。