怎么解决MySQL too many connections问题

75次阅读
没有评论

共计 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 小编将为大家推送更多相关知识点的文章,欢迎关注!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-27发表,共计3725字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)