这篇文章主要讲解了“怎么解决MySQL too many connections问题”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“怎么解决MySQL too many connections问题”吧！迪B哥在某个惬意的周末接到连续数据库的告警，告警信息如下：看到too many connection的报错信息，基本上可以把问题定位在：
（1）机器负载飙升，导致SQL执行效率下降，导致连接推积；（2）业务访问量突增（或者有SQL注入现象），导致连接数打满；（3）出现“死锁”或者锁竞争严重，导致大量SQL堆积。（1）机器的各项性能指标都显示正常， 没有出现高负载现象，暂时先排除了这种原因；（2）查看监控信息，发现在连接数打满的时间点前并没有访问量突增的趋势，同时通过检查告警信息并没有发现有注入工单；（3）最后上到服务器上查看下SQL的执行情况：3.1）查看show full processlist；大量的请求都是在“Waiting for table metadata lock”，可以分成三类请求：A. Select请求B. Rename请求C. Sleep请求3.2）分析Waiting for table metadata lock一般来说常见的“Waiting for table metadata lock”会出现在DDL操作或者是有未提交的事务上，从information_schema.processlist表中，没有发现有DDL操作，而能够产生MDL锁的操作也只剩下rename，但是根据SQL执行的状态，rename操作也是在等待MDL锁，所以rename操作应该是被阻塞的操作，而不是产生MDL锁的操作。接着我们来查看下死锁和事务的相关指标：A. show engine innodb status;中没有任何死锁的信息B. information_schema.innodb_trx 、 information_schema.innodb_locks 、information_schema.innodb_lock_waits 的也没有任何形式的锁信息。现在基本又排除了显示的死锁问题，那是从show full processlist中也抓不出任何请求，这里就比较疑惑了，当看了下表的结构式，发现这个表是myisam引擎的，所以上面的两种统计信息里面没有任何值就可以解释了。那么其实问题就集中在有未结束的事务上了，这里其实有一个误区，当时跟开发沟通存在未关闭的事务时，开发一直认为不可能，因为myisam表是不支持事务的，只有innodb支持事务。但是对于MDL锁来说，5.5之后引入MDL事务级别的锁不论对myisam还是innodb都是生效的。3.3）查看未提交的事务之后查看了下系统的事务自动提交的变量，autocommit的值是ON，那说明如果是事务未提交的话只可能是业务主动的开启一个事务，而没有commit。为了验证这个猜想，打开了general log，在log中果然发现，业务在开启事务后，把autocommit的值设为0了，导致必须要显示的commit才能提交事务。这时候我们反过头来看一下host为10.49.84.70的连接请求，由于select的执行速度很快，而且访问并不频繁，所以在抽样的show processlist中，状态值大部分时间是“Sleep”,给问题的定位带来了一些迷惑性的干扰。接着我们kill掉了这个进程，果然推积的请求瞬间就执行完成了，也之间印证了刚刚上述推论。在与开发同学沟通过程中，开发同学说库中是myisam表所以不会主动开启事务，在代码里也没有设置autocommit=0的代码，那么根本原因在哪？当我们定位到这台服务器上的请求都是来自python的定时脚本，使用python 操作mysql的时候，使用了其pymysql模块，但是在进行插入操作的时候，必须使用受到提交事务。Python的pymysql模块默认是会设置autocommit=0的。让我们来对比一下其他开发云主机域名同样使用python访问的正常连接请求，再断开前都会手动的commit。找到原因后有思考了下，是不是可以在建连后就设置autocommit=1呢？这样对于之后新变更的SQL就不要再考虑到手动commit的事情了，可以通过在初始化连接池的时候，对每一个连接进行设置，即（1）MDL简述为了在并发环境下维护表元数据的数据一致性，在表上有活动事务（显式或隐式）的时候，不可以对元数据进行写入操作。因此从MySQL5.5版本开始引入了MDL锁（metadata lock），来保护表的元数据信息，用于解决或者保证DDL操作与DML操作之间的一致性。对于引入MDL，其主要解决了2个问题，一个是事务隔离问题，比如在可重复隔离级别下，会话A在2次查询期间，会话B对表结构做了修改，两次查询结果就会不一致，无法满足可重复读的要求；另外一个是数据复制的问题，比如会话A执行了多条更新语句期间，另外一个会话B做了表结构变更并且先提交，就会导致slave在重做时，先重做alter，再重做update时就会出现复制错误的现象。所以在对表进行上述操作时，如果表上有活动事务（未提交或回滚），请求写入的会话会等待在Metadata lock wait 。支持事务的InnoDB引擎表和不支持事务的MyISAM引擎表，都会出现Metadata Lock Wait等待现象。一旦出现Metadata Lock Wait等待现象，后续所有对该表的访问都会阻塞在该等待上，导致连接堆积，业务受影响。（2）常见MDL锁场景2.1）当前有执行DML操作时执行DDL操作2.2）当前有对表的长时间查询或使用mysqldump/mysqlpump时，使用alter会被堵住2.3）显示或者隐式开启事务后未提交或回滚，比如查询完成后未提交或者回滚，DDL会被堵住2.4）表上有失败的查询事务，比如查询不存在的列，语句失败返回，但是事务没有提交，此时DDL仍然会被堵住Myisam是不支持事务的，innodb是支持事务的，这个概念其实没有任何问题，但是这里只的都是对于数据的事务性操作的支持，通过如下简单的实验可以很清楚的理解（关于事务的相关概念和解释就不再赘述了，只是想区别一下mysiam不支持事务，但是主动开始事务中对Myisam的操作仍然会产生MDL锁）：在隔离级别为RC的情况下：（1）myisam表1.1）CREATE TABLE `tb2` (`a` int(11) DEFAULT NULL ) ENGINE=MyISAM;1.2）Session 1：mysql> begin ;mysql> insert into tb2(a) value(1);（在session2的update之后）mysql> select * from tb2;+——–+| a |+——–+| 3 |+——–+Session 2：mysql> select * from tb2;+———+| a |+———+| 1 |+———+mysql> update tb2 set a=3 where a=1;mysql> select * from tb2;+——–+| a |+——–+| 3 |+——–+mysql> alter table tb2 add b int(11);… hangs …（2）innodb表2.1）CREATE TABLE `tb3` (`a` int(11) DEFAULT NULL ) ENGINE=INNODB;2.2）Session 1：mysql> begin ;mysql> insert into tb3(a) value(1);Session 2：mysql> select * from tb3;Empty set (0.00 sec)（1）场景1.1）CREATE TABLE `tb2` (`a` int(11) DEFAULT NULL ) ENGINE=MyISAM;1.2）Session 1：mysql> begin ;mysql> select * from tb2;Session 2：mysql> create table if not exists tb2(a int);… hangs …1.3）查看show processlistSession 1：SleepSession 2：Waiting for table metadata lock（2）解决方式①session 1上commit或者rollback②另外再开一个session3 ，kill掉可疑连接感谢各位的阅读，以上就是“怎么解决MySQL too many connections问题”的内容了，经过本文的学习后，相信大家对怎么解决MySQL too many connections问题这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是开发云，小编将为大家推送更多相关知识点的文章，欢迎关注！
MySQL读写分离架构(KHPM)Keepalived+HAProxy 应用程序入口无单点故障ProxySQL 开发云主机域名Cluster ProxySQL无单点故障MHA MySQL无单点故障(MHA Manager后续用ORCH RAFT代替，实现无单点…
免责声明：本站发布的图片视频文字，以转载和分享为主，文章观点不代表本站立场，本站不承担相关法律责任；如果涉及侵权请联系邮箱：360163164@qq.com举报，并提供相关证据，经查实将立刻删除涉嫌侵权内容。
微信扫一扫 
