MySQL的max_connections参数用来设置最大连接(用户)数。超过这个值,系统就会拒绝接下来的连接请求,报错提示:“too many connections”。对于被拒绝连接的请求来说,从业务角度就是数据库不可用。
每个连接MySQL的用户均算作一个连接,MySQL8.0.13的max_connections的默认值为151.
MySQL建立连接的过程,成本是很高的,除了正常的网络连接三次握手外,还需要做登录权限判断和获得这个连接的数据读写权限。
该参数在服务器资源够用的情况下应该尽量设置大,以满足多个客户端同时连接的需求。否则将会出现类似”Too many connections”的错误。
碰到这种情况,一个比较自然的想法,就是调高max_conncetions的值。
set GLOBAL max_connections=1000;
但是这样做是有风险的。因为设计max_connections这个参数的目的是想保护MySQL,如果我们把它改的太大,让更多的连接都可以进来,那么系统的负载可能会进一步加大,大量的资源消耗在权限验证等逻辑上,结果可能适得其反,已经连接的线程拿不到CPU资源去执行业务的SQL请求。
处理这种情况,有另外两个有损的方法。
第一种方法:先处理掉那些占着连接但是不工作的线程。
对于那些不需要保持的连接,我们可以通过show processlist命令,查看状态为sleep的线程,通过kill connection 主动踢掉。
另外需要注意,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会跳过所有的权限验证阶段,包括连接过程和语句执行过程在内。