MySQL max_connections变量讲解

最新推荐文章于 2024-06-10 08:11:32 发布

weixin_33690367

最新推荐文章于 2024-06-10 08:11:32 发布

阅读量1.2k

点赞数

文章标签：数据库 java python

原文链接：https://my.oschina.net/u/3672057/blog/2996195

版权

2019独角兽企业重金招聘Python工程师标准>>>

MySQL的max_connections参数用来设置最大连接（用户）数。超过这个值，系统就会拒绝接下来的连接请求，报错提示：“too many connections”。对于被拒绝连接的请求来说，从业务角度就是数据库不可用。

每个连接MySQL的用户均算作一个连接，MySQL8.0.13的max_connections的默认值为151.

MySQL无论如何都会保留一个用于管理员（SUPER）登陆的连接，用于管理员连接数据库进行维护操作，即使当前连接数已经达到了max_connections。因此MySQL的实际最大可连接数为max_connections+1；

MySQL建立连接的过程，成本是很高的，除了正常的网络连接三次握手外，还需要做登录权限判断和获得这个连接的数据读写权限。

该参数在服务器资源够用的情况下应该尽量设置大，以满足多个客户端同时连接的需求。否则将会出现类似”Too many connections”的错误。

碰到这种情况，一个比较自然的想法，就是调高max_conncetions的值。

set GLOBAL max_connections=1000;

但是这样做是有风险的。因为设计max_connections这个参数的目的是想保护MySQL，如果我们把它改的太大，让更多的连接都可以进来，那么系统的负载可能会进一步加大，大量的资源消耗在权限验证等逻辑上，结果可能适得其反，已经连接的线程拿不到CPU资源去执行业务的SQL请求。

处理这种情况，有另外两个有损的方法。

第一种方法：先处理掉那些占着连接但是不工作的线程。

对于那些不需要保持的连接，我们可以通过show processlist命令，查看状态为sleep的线程，通过kill connection 主动踢掉。

这个行为跟事先设置wait_timeout的效果是一样的。设置wait_timeout参数表示的是，一个线程空闲wait_timeout这么多秒之后，就会被MySQL直接断开连接。顺便提一下，wait_timeout默认值是28800秒，即8个小时。

另外需要注意，show processlist出现的结果有多个状态为sleep的线程，应该优先断开事务外空闲的连接.

如何判断哪些是事务外空闲的呢？

要看具体的事务状态，可以查看information_schema库的innodb_trx表。

select * from information_schema.innodb_trx \G

查询结果里，trx_mysql_thread_id=32，表示id=32的线程还处在事务中。

因此，如果是连接数过多，你可以优先断开事务外空闲太久的连接；如果这样还不够，再考虑断开事务内空闲太久的连接。

从服务端断开连接使用的命令是kill connection + id的命令，一个客户端处于sleep状态时，它的连接被服务端主动断开后，这个客户端并不会马上知道。直到客户端再发起下一个请求的时候，才会收到这样的报错“ERROR 2013（HY000）：Lost connection to MySQL server during query"。

从数据库端主动断开连接可能是有损的，尤其是有的应用端收到这个错误后，不重新连接，而是直接用这个已经不能用的句柄重试连接。这会导致从应用端看上去，”MySQL一直没恢复“。

第二种方法：让数据库跳过权限验证阶段。

如果现在数据库确认是被连接行为打挂了，那么一种可能的做法是：让数据库跳过权限验证阶段，减少连接过程的消耗。

跳过权限验证的方法：

重启数据库，并使用--skip-grant-tables参数启动。

这样，整个MySQL会跳过所有的权限验证阶段，包括连接过程和语句执行过程在内。