MySQL 5.6下table_open_cache参数优化合理配置详解

数据库  /  管理员 发布于 6年前   128

1、简介

table_cache是一个非常重要的MySQL性能参数，它在5.1.3之后的版本中叫做table_open_cache。table_cache主要用于设置table高速缓存的数量。由于每个客户端连接都会至少访问一个表，因此此参数的值与max_connections有关。
例如，对于 1000 个并行运行的连接，应该让表的缓存至少有 1000 × N ，这里 N 是应用可以执行的查询的一个联接中表的最大数量。此外，还需要为临时表和文件保留一些额外的文件描述符。

2、缓存机制

当某一连接访问一个表时，MySQL会检查当前已缓存表的数量。如果该表已经在缓存中打开，则会直接访问缓存中的表已加快查询速度；如果该表未被缓存，则会将当前的表添加进缓存并进行查询。

在执行缓存操作之前，table_open_cache用于限制缓存表的最大数目：如果当前已经缓存的表未达到table_open_cache，则会将新表添加进来；若已经达到此值，MySQL将根据缓存表的最后查询时间、查询率等规则释放之前的缓存。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。

3、如何判断

通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_open_cache的值。

如果你发现open_tables等于table_open_cache，并且opened_tables在不断增长，那么你就需要增加 table_open_cache的值了（上述状态值可以使用SHOW GLOBAL STATUS LIKE ‘Open%tables'获得）。

注意，不能盲目地把table_open_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。

Open_tables / Opened_tables >= 0.85

Open_tables / table_cache <= 0.95

4、建议

如果开始没有把握的话，把MySQL数据库放在生产环境中试运行一段时间，然后把参数的值调整得比Opened_tables的数值大一些，并且保证在比较高负载的极端条件下依然比Opened_tables略大。
在mysql默认安装情况下，table_cache的值在2G内存以下的机器中的值默认时256到 512，如果机器有4G内存,则默认这个值是2048，但这决意味着机器内存越大，这个值应该越大，因为table_cache加大后，使得mysql对 SQL响应的速度更快了，不可避免的会产生更多的死锁（dead lock），这样反而使得数据库整个一套操作慢了下来，严重影响性能。所以平时维护中还是要根据库的实际情况去作出判断，找到最适合你维护的库的 table_cache值。

mysql >flush tables; 可以将open_tables 清零

# service mysqld restart 可以讲opened_tables 清零

以下是针对mysql 5.6的说明

table_open_cache指定表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。
通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_open_cache的值。
如果你发现open_tables等于table_open_cache，并且opened_tables在不断增长，那么你就需要增加table_open_cache的值了(上述状态值可通过SHOW GLOBAL STATUS LIKE ‘Open%tables'获得)。
注意，不能盲目地把table_open_cache设置成很大的值，设置太大超过了shell的文件描述符(通过ulimit -n查看)，造成文件描述符不足，从而造成性能不稳定或者连接失败。

测试环境：腾讯云CDB，内存4000M，在控制台查看到table_open_cache＝512，监测table_open_cache设置是否合理，是否需要优化。

show variables like '%table_open_cache%';

show global status like 'Open%tables';

发现open_tables等于table_open_cache，都是512，说明mysql正在将缓存的表释放以容纳新的表，此时可能需要加大table_open_cache的值，4G内存的机器，建议设置为2048

比较适合的值：

Open_tables / Opened_tables >= 0.85
Open_tables / table_open_cache <= 0.95

如果对此参数的把握不是很准，有个很保守的设置建议：把MySQL数据库放在生产环境中试运行一段时间，然后把参数的值调整得比Opened_tables的数值大一些，并且保证在比较高负载的极端条件下依然比Opened_tables略大。