最近遇到一起大量opening tables导致数据库hang起的生产事故，借此机会总结一下。
故障发生后查看show processlist，发现大量线程处于opening tables状态，大多数语句无法正常执行，甚至kill语句也会卡住。此时，如果短时间无法找到原因，最有效的解决方法是立刻重启实例，如果条件容许，建议打几个pstack，然后事后分析。
一、table_open_cache设置不合理
当Open_tables值大于table_open_cache值，且新的连接无法命中table cache时，就需要再次打开并缓存表定义，此时就会有大量的线程处于opening tables状态。
show global status like 'Open_tables%';
show variables like 'table_open_cache';
这里我们观察两个指标，Table_open_cache_misses和Table_open_cache_overflows，如果hit值很小，但是miss和overflows很大，就考虑增大table_open_cache。
连续执行下面命令，观察下面3个参数的变化：
mysql> show global status like '%table_open_cache%';
+----------------------------+------------+
| Variable_name | Value |
+----------------------------+------------+
| Table_open_cache_hits | 2457557898 |
| Table_open_cache_misses | 71875008 |
| Table_open_cache_overflows | 64860924 |
+----------------------------+------------+
3 rows in set (0.00 sec)
初始化参考值
table_open_cache_instances=16； #多实例可以降低mutex冲突
table_open_cache=10240； #受文件打开数影响（open_file_limit）
根本解决方法
出现Opening tables等待问题时，建议找出打开表频繁的SQL语句，优化该SQL，降低单条SQL查询表的数量或大幅降低该SQL的并发访问频率。
二、执行truncate后，触发AHI维护
开启AHI（自适应哈希）后，如果truncate一张大表，mysql会同时删除buffer pool中对应表上的AHI，这个过程会加数据字典锁（sys_dict）。而在很多地方比如如下的函数中都会持有这个锁进行互斥保护。
- >ha_innobase::get_foreign_key_list:获取外键列表
- >ha_innobase::open:每当table cache不在的时候都会调用它取建立。
此外，用户sql用到上面两个函数后引发sys_dict mutex，此时kill命令也将无法唤醒。所以，当数据库负载较大时在大表执行了truncate操作，AHI维护耗时就会很长，导致其他用户线程无法获取字典锁而处于Opening tables状态。
解决思路
1、使用rename + create代替 truncate。
即在一个事务里修改字典数据，成功后再删除rename的临时表。 不过这仅仅使用于可控的计划任务操作的表。如果发生在应用频繁访问的表，这个操作有一定风险性，因为sql由一条改为两条，中间可能引起业务的报错。
2、降低truncate操作的频率，避免在高峰期执行。查看数据库中AHI的命中率，如果命中率较低，可以考虑关闭AHI功能。
上图中，AHI命中率才14.1%，可以关闭。
