怎么解决MySQL too many connections问题

共计 3725 个字符，预计需要花费 10 分钟才能阅读完成。

这篇文章主要讲解了“怎么解决 MySQL too many connections 问题”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“怎么解决 MySQL too many connections 问题”吧！

一、故障情况

迪 B 哥在某个惬意的周末接到连续数据库的告警，告警信息如下：

二、艰难的探索过程 1、总体思路

看到 too many connection 的报错信息，基本上可以把问题定位在：

（1）机器负载飙升，导致 SQL 执行效率下降，导致连接推积；

（2）业务访问量突增（或者有 SQL 注入现象），导致连接数打满；

（3）出现“死锁”或者锁竞争严重，导致大量 SQL 堆积。

2、排查过程

（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 掉了这个进程，果然推积的请求瞬间就执行完成了，也之间印证了刚刚上述推论。

3、问题解决

在与开发同学沟通过程中，开发同学说库中是 myisam 表所以不会主动开启事务，在代码里也没有设置 autocommit= 0 的代码，那么根本原因在哪？

当我们定位到这台服务器上的请求都是来自 python 的定时脚本，使用 python 操作 mysql 的时候，使用了其 pymysql 模块，但是在进行插入操作的时候，必须使用受到提交事务。Python 的 pymysql 模块默认是会设置 autocommit= 0 的。

让我们来对比一下其他同样使用 python 访问的正常连接请求，再断开前都会手动的 commit。

找到原因后有思考了下，是不是可以在建连后就设置 autocommit= 1 呢？这样对于之后新变更的 SQL 就不要再考虑到手动 commit 的事情了，可以通过在初始化连接池的时候，对每一个连接进行设置，即

三、延伸思考 1、metadata lock

（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 仍然会被堵住

2、myisam、innodb 对事务的支持

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)

3、myisam 表的另一个 BUG

（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 processlist

Session 1：Sleep

Session 2：Waiting for table metadata lock

（2）解决方式

①session 1 上 commit 或者 rollback

②另外再开一个 session3，kill 掉可疑连接

感谢各位的阅读，以上就是“怎么解决 MySQL too many connections 问题”的内容了，经过本文的学习后，相信大家对怎么解决 MySQL too many connections 问题这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！