如何解决MySQL线程处于打开表的问题

这篇文章主要介绍了如何解决MySQL线程处于打开表的问题,具有一定借鉴价值,需要的朋友可以参考下。希望大家阅读完这篇文章后大有收获。下面让小编带着大家一起了解一下。

本篇文章给大家带来的内容是关于MySQL线程处于打开表的问题解决(附示例),有一定的参考价值,有需要的朋友可以参考一下,希望对你有所帮助。

<强>问题描述

最近有一台MySQL5.6.21的服务器,在应用发布后,并发线程Threads_running迅速升高,达到2000左右,大量线程处于等待打开表,关闭表状态,应用端相关逻辑访问超时。

【分析过程】

1, 16:10应用发布结束后,Opened_tables不断增加,如下图所示:

查看当时故障期间抓取的pt-stalk日志文件,时间点2019-01-18 16:29:37,Open_tables的值为3430,而table_open_cache的配置值为2000。
当Open_tables值大于table_open_cache值时,每次新会话的打开表,有一些无法命表缓存中,而不得不重新打开表。这样反应出来的现象就是有大量的线程处于打开表状态。

2,这个实例下的表,加上系统数据库下总计851张,远小于table_open_cache的2000年,为什么会导致Open_tables达到3430呢
从官方文档中可以得到解释,
https://dev.mysql.com/doc/refman/5.6/en/table-cache.html

 table_open_cache max_connections有关。例如,连续200个并发连接,指定一个表缓存大小至少200 * N,其中N是表的最大数量每加入任何你执行的查询。

当时并发线程数达到1980,假设这些并发连接中有30%是访问2张表,其他都是单表,那么缓存大小就会达到(1980 * 30% * 2 + 1980 * 70% * 1)=2574

3,每秒在发布前后都比较平稳,从外部请求来看并没有突增的连接请求,但在发布后threads_running上升到接近2000的高位,一直持续。猜测是由于某个发布的SQL语句触发了问题。

4,查看当时抓取的processlist信息,有一句SQL并发访问很高,查询了8张物理表,SQL样本如下:

 <代码>选择id、名称、电子邮件从table1左加入表union all 
 
选择id、名称、电子邮件从table3左加入table4 union all 
 
选择id、名称、电子邮件从table5左加入table6 union all 
 
选择id、名称、电子邮件从table7左加入table8 
在id (& # 39; aaa # 39;);  

5,在测试环境中创建相同的8张表,清空表缓存,单个会话执行SQL前后对比,Open_tables的值会增加8,如果高并发的情况下,Open_tables的值就会大幅增加。

<强>问题重现

在测试环境上模拟高并发访问的场景,并发1000个线程同时执行上面的SQL语句,复现了生产环境类似的现象,Open_tables迅速达到3800年大量进程处于打开表,关闭表状态。

<>强优化方案

1,定位到问题原因后,我们与开发同事沟通,建议优化该SQL,降低单句SQL查询表的数量或大幅降低该SQL的并发访问频率。
不过开发同事还没来的及优化,生产环境上故障又出现了。当时DBA排障时将table_open_cache从2000增加4000 CPU使用率上升,效果并不明显,等待打开表的问题依然存在。

2,分析故障期间抓取的pstack信息,用pt-pmp聚合后,看到大量线程在open_table时等待互斥资源: