参数优化-table_open_cache_instances

1.参数影响RDS实例性能

分库分表实例，共5台rds 相同的配置，每个rds20个表， 在执行相同的脚本（串行执行alter table 来回收表空间），

发现其中一台数据库（rds1）实例执行的速度比其他4台足足慢了1天， 分库分表，数据量是差不多的， 表大小也差不多，为什么会有这么大的差异呢？

排查结果：发现rds1的其中一个参数和其他的rds设置的不一样，其他4台设置的是16 只有rds设置的是1

参数：table_open_cache_instances

rds2~rds5

 rds1

 原因：（目前还未验证完成修改参数后是否有效）-----有效已验证

 

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

table_definition_cache > table_open_cache_instances > table_open_cache

参考表缓存文章：MySQL 表缓存_月饮沙的技术博客_51CTO博客
扩展：

1.table相关的限制有哪些？
mysql是多线程，对于并发同一个文件，不同数据的情况下，会打开多个文件，会存在哪些限制呢？下面是源代码里逻辑是怎样

1)table_definition_cache
.frm文件其实最大值只能到2000,跟官网给得最大值没关系

2）open_files_limit
imit_1= 10 + max_connections + table_cache_size * 2;
limit_2= max_connections * 5;
limit_3= open_files_limit ? open_files_limit : 5000;
可以看出max_connections有关，需要借助于table open file 的信息

3）max_connections超出打开文件数量的伐值的时候，也跟table_open_cache有关

4）table_cache_size 计算方式
备注：TABLE_OPEN_CACHE_MIN=table_open_cache

5.定期查看open table 情况
通过 show global status like ‘%Open%_table%’; 确认是否调优这个参数

2.常见故障应对：
如：在运行数据库通过 show processlist 可看到大量的 Opening tables、closing tables状态，导致应用端访问操作。
需要确认 table_open_cache=最大并发数表数量（join里可能用到2张表），时候满足当前配置
如：但并发线程数达到1000，假设这些并发连接中有40%是访问2张表，其他都是单表，那么cache size就会达到（100040%2+100060%*1）=1400
建议定期监控值：
Open_tables / Opened_tables >= 0.85 表的重复使用率
Open_tables / table_open_cache <= 0.95 缓存里存在已打开的表

1）5.7版本已经支持在线动态改配置信息

set global table_definition_cache=2000;
set global table_open_cache=3000;
set global max_connection= 2000;
table_open_cache_instances参数修改需要重新启动服务

2）无法更改的时候，可通过flush操作，但存在问题

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。

总结：
Table缓存关于的参数table_definition_cache，table_definition_cache，table_open_cache_instances 按照实际环境和需求进行设置外，还有跟max_connections也要设置合理。有些环境里发现max_connections过大，过小设置的问题，设置过大可能会存在等待的情况
这些参数控制不好，会给MySQL数据库系统带来性能上的瓶颈。如果把握不是很准，有个很保守的设置建议：把MySQL数据库放在生产环境中试运行一段时间，然后把参数的值调整得比Opened_tables的数值大一些，并且保证在比较高负载的极端条件下依然比Opened_tables略大

1. MySQL表的打开和关闭

MySQL是多线程的，因此可能有多个客户端同时对同一个表进行查询，为了减少多个客户端会话在同一个表上具有不同状态的问题，并发的每个会话都会独立的打开表。这样会使用额外的内存，但是通常可以提升性能。
为了减少会话间对表缓存的争用，提高扩展性，表缓存还可以被分区为多个小的缓存实例。对于DML操作，会话只需要锁定它访问的一个实例就可以执行。DDL操作还是需要锁定整个表缓存。

2.MySQL表的关闭

在以下情况下，MySQL关闭未使用的表并将他们从缓存中删除

当表缓存已满并且线程尝试打开一个不在缓存中的表的时候
当表缓存中的内容超过table_open_cache的数量并且一个在缓存中的表不再被任何线程使用的时候
当发出表刷新语句的时候。表刷新语句包括FLUSH TABLES,mysqladmin flush-tables,mysqladmin reflush

3.MySQL表缓存的清理机制

当表缓存已满的时候，使用以下机制释放缓存

从最近最少使用的表开始，释放当前未使用的表
如果必须打开一个不在缓存中的表，但是缓存已满并且没有表可以释放，则可以根据需要临时扩展缓存。
当缓存处于临时扩展状态并且其中的表从使用中的状态转换为未使用的状态时，这个表会关闭并从缓存从释放

4.表缓存相关的参数

table_open_cache：
全局变量，可以动态修改，默认值2000。取值范围400-524288
所有线程打开的表的数量。

table_open_cache_instances：
全局变量，不可以动态修改，默认值16，取值范围1-64
在16核或更高内核数量的操作系统中，建议将值设置为8或16

table_definition_cache：
全局变量，可以动态修改，默认值-1，表示自动调节。取值范围400-524288
默认值 400 + ( table_open_cache / 2 )，最高默认值为2000
可以存储在表定义缓存中的表定义（.frm文件）数量。如果表的数量很多，可以增加该值来提升打开表的速度。表定义缓存使用较小的空间，并且不像表缓存一样需要使用文件描述符。

5.MySQL为什么要打开表？

为了提升性能。
5.MySQL打开表对操作系统有什么影响，哪种存储引擎有额外的影响？
打开表需要占用系统的文件描述符。对于MyISAM表，首次打开时需要两个文件描述符，一个用于数据文件，一个用户索引文件。当MyISAM表第二次打开时，只需要一个文件描述符，因为索引文件在所有线程之间共享。对于MyISAM分区表，在打开表时会打开所有表分区，每个分区都需要两个文件描述符。
7.MySQL在什么情况下打开表，在什么情况下关闭表？
每个并发的查询都会打开表。当表缓存已满但是需要缓存新表的时候，会关闭缓存中未使用的表并将它从缓存中删除。也可以使用FLUSH TABLES主动关闭表。
8.MySQL表缓存相关的参数有哪些？都有什么作用？
table_open_cache 定义了打开表的数量
table_open_cache_instances 定义了表缓存的实例数量。在进行DML操作时，只需要锁定一个缓存实例
table_definition_cache 定义了.frm文件的缓存数量
9.MySQL表缓存的释放机制是怎样的？
在表缓存已满但是需要缓存新的表的时候，首先检查缓存中是否存在未在使用的表，如果存在，按照最近最少使用的原则关闭并释放表，如果没有未使用的表，临时扩展缓存并缓存新表，当缓存中的表不再使用时，对未使用的表进行关闭和释放，直到缓存的表数量小于等于open_table_cache
10.如何查看MySQL缓存的表的数量？
SHOW GLOBAL STATUS LIKE '%open%'
11.在什么情况下应该增加表缓存？
在没使用FLUSH TABLES的情况下，发现Opened_tables值很大。
12.如何查看当前缓存了哪些表？
SHOW OPEN TABLES
还可以通过SHOW OPEN TABLES WHERE in_use>0查看哪些表持有表锁。