小白学习MySQL - table_open_cache的作用

爱被打了一巴掌 2022-09-04 06:51 116阅读 0赞

最近一套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，进行检索，

我们还可使用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，显示当前打开的表数量，

其实当出现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存在联动，可以参考，

open_files_limit= Table_open_cache*2

通过以上信息，我们知道如果需要调整table\_open\_cache，还得结合数据库和操作系统的参数，确认不会因此导致系统资源耗尽。因此，技术算是一项系统工程，了解方方面面，才可知道怎么做，是正确的。

参考，

https://dev.mysql.com/doc/refman/5.7/en/table-cache.html

https://www.yisu.com/zixun/116452.html

https://blog.csdn.net/liuhuayang/article/details/107194612

https://www.cnblogs.com/fjping0606/p/6531292.html

**小白学习MySQL，**

《[小白学习MySQL - 表空间碎片整理方法][MySQL -]》

《[小白学习MySQL - 大小写敏感问题解惑][MySQL - 1]》

《[小白学习MySQL - only\_full\_group\_by的校验规则][MySQL - only_full_group_by]》

《[小白学习MySQL - max\_allowed\_packet][MySQL - max_allowed_packet]》

《[小白学习MySQL - mysqldump保证数据一致性的参数差异][MySQL - mysqldump]》

《[小白学习MySQL - 查询会锁表？][MySQL - 2]》

《[小白学习MySQL - 索引键长度限制的问题][MySQL - 3]》

《[小白学习MySQL - MySQL会不会受到“高水位”的影响？][MySQL - MySQL]》

《[小白学习MySQL - 数据库软件和初始化安装][MySQL - 4]》

《[小白学习MySQL - 闲聊聊][MySQL - 5]》

**近期更新的文章：**

《[《SQL Cookbook》 - 第一章 检索数据][SQL Cookbook_ - _]》

《[梅西 ≠ 一人一城？][Link 1]》

《[小白学习MySQL - 表空间碎片整理方法][MySQL -]》

《[Oracle字符串类型扩容隐患][Oracle]》

《[PG逻辑复制的REPLICA IDENTITY设置][PG_REPLICA IDENTITY]》

**文章分类和索引：**

[《公众号800篇文章分类和索引][800]》

[MySQL -]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a030ddf52926dd6c3a22a7da1a3c7055d8e24e1ee958d885b0c25d29202239587a2a1a66&idx=1&mid=2247493059&scene=21&sn=16eaffcc25f1e7d50c792e58b3a230ea#wechat_redirect
[MySQL - 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a06fddf529791d829e1e2c976d5b9d9a9e9825bb35c7a9e8b85ccd9cafe962115ba90e4c&idx=1&mid=2247493020&scene=21&sn=ced36198e3ad9d003445ea6d37679358#wechat_redirect
[MySQL - only_full_group_by]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a070ddf52966a270c13763d5068d58ca8c16a3655f088ddb1c856b3f2394178452b751b2&idx=1&mid=2247492995&scene=21&sn=bfeca96384e6310326f2309da5ae0853#wechat_redirect
[MySQL - max_allowed_packet]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a0a2ddf529b48f7a84a3c6e29f3db6034e41951fb003a83d4ba39de501c1b74ea5a4d488&idx=1&mid=2247492945&scene=21&sn=86c99c128149dc975541eaf98127f880#wechat_redirect
[MySQL - mysqldump]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a169ddf5287f13b6f3ce6092e7645d9c4a50ff14a9e1965afda26514fee38691d5dbea9f&idx=1&mid=2247492762&scene=21&sn=6f412753ba32dc5f4560d5d32ed28314#wechat_redirect
[MySQL - 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a751ddf52e47df120a4c3f4cd841cd06383a3cc406398d6feb9185e17d1167c53e1c0a56&idx=1&mid=2247492258&scene=21&sn=d1859e3c1e3f2bee0e2c7801c8dc0b5b#wechat_redirect
[MySQL - 3]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a466ddf52d701aa8f23065ac5d0ef4e0ff1bf1b6deb53a923e3d3ab007d6d41d92a73088&idx=1&mid=2247491989&scene=21&sn=3979107e0b99ebddc6b6ccfc8f9b901c#wechat_redirect
[MySQL - MySQL]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a485ddf52d936279d52b0e62463f699412cf886e9b51eaf48114e7561dec55ccc21189be&idx=1&mid=2247491958&scene=21&sn=6917dcc719cab7b5537194eed27b4ed2#wechat_redirect
[MySQL - 4]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a4c8ddf52dde849a70b34f2b62ff16593f81898cca7bc1fd7f0321588823016f0becaa18&idx=1&mid=2247491899&scene=21&sn=7affd185a81761eeaa5c3e143fc6f894#wechat_redirect
[MySQL - 5]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a4dcddf52dcad1708150cc6ceb101423eb5f4326ceff0f9dd53c05103bea76c1c5b7ccbc&idx=1&mid=2247491887&scene=21&sn=91485c182868ef19ad822c3ac9bf2ea9#wechat_redirect
[SQL Cookbook_ - _]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a011ddf529079fa6fbcfbee70fd98db135076344d0ae9ea4863b3fe9f5e17149aac2609b&idx=1&mid=2247493090&scene=21&sn=8bc441cc9e023eb49e897759355f6357#wechat_redirect
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a029ddf5293fbd6d72536d566769b6ef0dff0d6f438363ece58c99c57a68d5a93e9796e1&idx=1&mid=2247493082&scene=21&sn=1adcc88366ab2baa5b2e6767941994d3#wechat_redirect
[Oracle]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a055ddf52943c9699cd6e4ccd7ab18c6f181921a97cf9c35cb962b9898c6eed1a8fb553f&idx=1&mid=2247493030&scene=21&sn=5bc4c2487b1ac96d507ce7468f02d535#wechat_redirect
[PG_REPLICA IDENTITY]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a0b7ddf529a1c70e180e944651c2330c2be943b1ef07c95b78b9936a2d32c43b166b9637&idx=1&mid=2247492932&scene=21&sn=b3dc4ee09e8517446d356e31c4b17a87#wechat_redirect
[800]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a084ddf529920f2572e843967c4cb4248b6adc854a001315dd54264080349d3a5cc2ea14&idx=1&mid=2247492983&scene=21&sn=5e0514b86258eebed20740dce83a6d45#wechat_redirect