mysql里table_cach关系

凌晨5点在做美梦当中的时候，某某却在翻MySQL运维内参，看到第一页表对象缓存的时候，

抽出1个小时特意，花费时间研究了一下 table_definition_cache，table_open_cache和table_open_cache_instances关系。

以前老是有疑惑，这个是做什么的，不就是打开表数量控制的吗。

确实理解的一样。

但越往里看 就发现，这个参数不简单，一旦控制不好，能给MySQL系统带来再灾难性的问题。

先看看官网怎么说

1.table_open_cache_instances
The number of table definitions (from .frm files) that can be stored in the definition cache. If you use a large number of tables, you can create a large table definition cache to speed up opening of tables. The table definition cache takes less space and does not use file descriptors, unlike the normal table cache. 

理解下来，就是控制总frm文件的数量，还是个hash表，内部维护。如果打开的表实例的数量超过了table_definition_cache设置，
LRU机制将开始标记表实例以进行清除，并最终将它们从数据字典缓存中删除。

简单通俗点frm文件有多少，就设置多少了。

2.table_open_cache
The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. You can check whether you need to increase the table cache by checking the Opened_tables status variable

所有线程打开的表的数量。增加这个值会增加mysqld需要的文件描述符的数量。可以通过检查Opened_tables状态变量来检查是否需要增加表缓存。
是不是可以理解为ibd/MYI/MYD 文件，打开数量了。但mysql内部需要对表进行操作的时候，第一需要找到最上层文件的句柄信息，table_open_cache_instances是能提供的，之后对应的寻找ibd,MYI,MYD文件

3.那问题就来了，mysql是多线程，对于并发同一个文件，会打开多个文件，会存在哪些限制呢？下面是5.7.25代码里提到的限制

limit =max<ulong>((requested_open_files-10- max_connection)/2, TABLE_OPEN_CACHE_MIN) 
这里可以看出来需要取最大值，

requested_open_files：请求的文件数量
max_connection：最大用户连接数
10：预留一点空间
max_connection：最大连接数
TABLE_OPEN_CACHE_MIN=table_definition_cache（默认值400）
备注：有些环境如max_connection=0，这样会导致最高打开400个frm文件，所以建议把max_connection一定要设置

4.故障触发怎样处理

5.7版本已经支持在线动态改配置信息
set global table_definition_cache=2000;
set global table_open_cache=3000;
set global max_connection= 2000

 

5.内存满的时候，怎样处理
官方给的方案建议：
MySQL closes an unused table and removes it from the table cache under the following circumstances:
When the cache is full and a thread tries to open a table that is not in the cache.
When the cache contains more than table_open_cache entries and a table in the cache is no longer being used by any threads.
When a table-flushing operation occurs. This happens when someone issues a FLUSH TABLES statement or executes a mysqladmin flush-tables or mysqladmin refresh command.

这里好奇FLUSH TABLE操作，有如下隐患
    关闭所有打开的表，强制关闭所有正在使用的表，并刷新查询缓存和准备好的语句缓存。
    FLUSH TABLES还会从查询缓存中删除所有查询结果，比如RESET查询缓存语句。

 

 

 

 

另外，table_definition_cache为每个表的表空间中可以同时打开的InnoDB文件的数量定义了一个软限制，这也是由innodb_open_files控制的。
如果设置了table_definition_cache和innodb_open_files，则使用最高设置。如果两个变量都没有设置，则使用默认值更高的table_definition_cache。

6。怎样看open table 情况，
通过 show global status like '%Open%_table%'; 确认是否调优这个参数

7.table_open_cache_instances
The number of open tables cache instances. To improve scalability by reducing contention among sessions, the open tables cache can be partitioned into several smaller cache instances of size table_open_cache / table_open_cache_instances . A session needs to lock only one instance to access it for DML statements. This segments cache access among instances, permitting higher performance for operations that use the cache when there are many sessions accessing tables.

开的表缓存实例的数量。为了通过减少会话间的争用来提高可伸缩性，可以将打开的表缓存划分为几个大小为table_open_cache / table_open_cache_instances的较小缓存实例。一个会话只需要锁定一个实例就可以访问DML语句。

写到这里就已经大致了解到 如下关系 table_definition_cache > table_open_cache_instances >  table_open_cache

 

8.常见故障应对：

在运行数据库通过 show processlist 可看到大量的 Opening tables、closing tables状态，导致应用端访问操作。
需要确认 table_open_cache=最大并发数*表数量（join里可能用到2张表），时候满足当前配置

当时并发线程数达到1000，假设这些并发连接中有30%是访问2张表，其他都是单表，那么cache size就会达到（1000*30%*2+1000*70%*1）=2574

 

比较适合的值：
Open_tables / Opened_tables >= 0.85 表的重复使用率
Open_tables / table_open_cache <= 0.95 缓存里存在已打开的表