MySQL设置多大的table_open_cache才合适?

我们有个分库分表的数据库系统,数据库里面大概有6000到7000张表,因为是分库分表,所以同一类的表有很多张,作用也是相同的,会被同时访问到,当时作者将数据库的table_open_cache设置成12000,已经是数据库里面表的数量的2倍了,心想table_open_cache应该已经足够大。

但是,作者在查看数据库各线程的状态的时候,经常发现有不少线程处于open table的状态,就产生了一些怀疑,是否是数据库的设置不对,导致table cache不够用。 同时,通过命令show global status like 'Table_open_cache%' , 查看到Table_open_cache_missesTable_open_cache_overflows增长的速度特别快,同时Table_open_cache_hits 值的增长速度反而没有前面两个指标高。心里一惊,难道是table_open_cache 设置太小了,还有Table_open_cache_overflows 又代表什么意义?  overflows ----超出? 为啥超出,难道table_open_cache设置的太了,但实际情况不是命中率还不够高莫?

抱着试试看的心态,把table_open_cache调成25000,然后看一下效果,果然,table_cache的命中率有提高,但还是没有达到理想状态。继续调高,调成40000,观察后发现还有很多命中不上的情况。此时,有点心虚了,已经设置成40000了,还出现table cache 不够的情况,何解?

一时无头绪,想知道真正的原因,看来只能看代码了。Table_open_cache_overflows 指标的异常增长让作者很是好奇,所以决定从这个入手。 然后找到下面的函数,跟这个指标有直接的关系。

Table_cache::free_unused_tables_if_necessary

MySQL设置多大的table_open_cache

我们看到使 thd->status_var.table_open_cache_overflows++ 执行的条件是:

m_table_count > table_cache_size_per_instance && m_unused_tables

table_cache_size_per_instance  值得意义是什么? 怎么得来的?

继续搜索代码,找到table_cache_size_per_instance 的赋值来源,存在这样的关系。

table_cache_size_per_instance= table_cache_size / table_cache_instances

通过下面命令,我们可以得到table_open_cache_instances的值,默认16.

mysql> show global variables like '%table_open_cache_instances%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| table_open_cache_instances | 16    |
+----------------------------+-------+

于是假如table_open_cache设置成12000, table_open_cache_instances 为16, 则 table_cache_size_per_instance=12000/16=750

然后,我们再来看上面的触发overflows的等式

m_table_count > table_cache_size_per_instance && m_unused_tables

假如m_table_count的值大于750,以及m_unused_tables 非空,就是执行remove_table(table_to_free)的操作,而且上面的条件是递归操作的条件,也就是意味则,m_unused_tables 列表中的table_cache将被清除,直至m_table_count小于或者等于table_cache_size_per_instance,导致table cache 池中的table cache数量进一步减少,产生大量的Table_open_cache_misses 与Table_open_cache_overflows

下面是Table_cache::free_unused_tables_if_necessary的函数栈

MySQL设置多大的table_open_cache才合适?

如果发现overflows的时候,会进行 remove_table(table_to_free)的操作。

下面是remove_table的函数。

MySQL设置多大的table_open_cache才合适?

正是Table_cache::add_used_table跟remove_table 函数的相结合,所以m_table_count 的值就会为维持在 table_cache_size_per_instance  以下,也就导致了实际可以保持的table_cache的数量在table_cache_size_per_instance左右。

亲,已了解该如何设置table_open_cache的值了吗?



留言