小白学习MySQL - table_open_cache的作用

滕祯

2023-12-01

最近一套MySQL，show processlist的时候，看到很多会话状态是Opening tables，这是什么意思？

MySQL是多线程的，可能在同一时刻有很多的客户端访问某张特定的表。为了能最小化多个客户端在相同表上的不同状态问题，并发会话中访问的每张表都会单独打开。虽然这可能消耗过多的内存，但是通常会提高系统的性能。

table_open_cache系统参数是和max_connection相关的。例如200个并发运行的连接，需要指定表缓存的数量至少是200*N，其中N是执行的查询中每个连接涉及到的表最大数量，换句话说，执行10次查询，很可能因为join了很多张表，实际打开的表数量是10的几倍。而且要确保你的操作系统能处理table_open_cache所指定的文件描述符打开的数量。如果table_open_cache太高，MySQL可能会消耗完文件描述符，现象就是拒绝连接或者查询失败。

MySQL会在如下场景关闭一个未使用的表，并将他从表缓存中删除。

1. 当缓存满了，并且一个线程正在尝试打开一个缓存中的表。

2. 当缓存中包含了超过table_open_cache定义的数量，并且缓存中的表不再被任何线程使用。

3. 当我们执行flush tables，或者执行mysqladmin flush-tables以及mysqladmin refresh指令的时候。

当表缓存满了，服务会执行如下过程来确定可用的内存空间，

1. 当前不使用的表会被释放，从最近最少使用的表开始，即采用LRU。

2. 如果打开一张新表但是缓存满了，并且没有其他表能释放，缓存就会临时按需扩容。当缓存处于一个临时扩容状态，表从使用状态改为未使用状态时，就会关闭表，并从缓存中释放。

当你正在用句柄HANDLER tbl_name OPEN打开一张表，会给线程分配一个专用表对象。这个表对象不会被其他线程共享，更不会被关闭，直到线程调用HANDLER tbl_name CLOSE或者线程中断了。只有当这时候，表才会被放回表缓存中(前提是缓存未满)。

当Open_tables值大于table_open_cache值时，每次新的session打开表，有一些无法命中table cache，而不得不重新打开表。这样反应出来的现象就是有大量的线程处于opening tables状态。

table_open_cache参数可以通过show variable，进行检索，

mysql> show variables like 'table_open_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 400   |
+------------------+-------+
1 row in set (0.13 sec)

我们还可使用mysqladmin status，看下当前打开的表缓存数量，

[root@bisal lib]# /usr/local/mysql7/bin/mysqladmin -uroot -proot -S /mysql/3306/tmp/mysql.sock status
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Uptime: 11901996  Threads: 2  Questions: 16201775  Slow queries: 10  Opens: 307  Flush tables: 1  Open tables: 181  Queries per second avg: 1.361

或者通过show global status，显示当前打开的表数量，

mysql> show global status like 'Open%tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 200   |
| Opened_tables | 309   |
+---------------+-------+
1 row in set (0.01 sec)

其实当出现Opening tables，这可能只是表象，得知道是什么原因导致的，并发量瞬时增加？还是存在连接未释放的应用bug？又或者是SQL执行效率太低，导致释放速度慢？

如果open_tables等于table_open_cache，并且opened_tables不断增加，刨除以上因素，就可能需要增加table_open_cache的值了。

同时，可以考虑设置table_open_cache_instances，5.7默认是16，逻辑是当Open_tables超过(table_open_cache/table_open_cache_instances)时，就会满足条件，加速缓存清理，因此通过增加表缓存分区，应该可以缓解争用，例如2000/16，就会比2000/1更快满足清理的条件。

另外，table_open_cache和open_file_limit存在联动，可以参考，