Mysql连接数过大导致连接超时的问题

￡神魔★判官ぃ 2022-05-13 06:24 271阅读 0赞

Mysql连接数过大导致连接超时的问题，春节访问量激增，负载压力很大，程序处理较慢，然后就调整项目中的线程池和[数据库][Link 1]连接数。可是还是没有太好的提高，追根溯源，发现数据库连接受到限制。虽然是做了读写分离，但是还是没抗住高峰。所以会有报错：“MySQL: ERROR 1040: Too many connections”。

查看最大连接数上限，默认的是151

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 </td> 
 <td> <code>mysql&gt; show variables </code><code>like</code> <code>'max_connections'</code><code>;</code> <code>+</code><code>-----------------+-------+</code> <code>| Variable_name&nbsp;&nbsp; | Value |</code> <code>+</code><code>-----------------+-------+</code> <code>| max_connections | 151&nbsp;&nbsp; |</code> <code>+</code><code>-----------------+-------+</code> </td> 
 </tr> 
 </tbody> 
</table>

查看mysql最大使用连接数

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 </td> 
 <td> <code>mysql&gt; show </code><code>global</code> <code>status </code><code>like</code> <code>'Max_used_connections'</code><code>;</code> <code>+</code><code>----------------------+-------+</code> <code>| Variable_name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Value |</code> <code>+</code><code>----------------------+-------+</code> <code>| Max_used_connections | 152&nbsp;&nbsp; |</code> <code>+</code><code>----------------------+-------+</code> </td> 
 </tr> 
 </tbody> 
</table>

可以看出，已经超过了连接数上限。而且还多了一个，这是为什么呢？原来MySQL无论如何都会保留一个用于管理员（SUPER）登陆的连接，用于管理员连接数据库进行维护操作，即使当前连接数已经达到了max\_connections。因此MySQL的实际最大可连接数为max\_connections+1；  
这个参数实际起作用的最大值（实际最大可连接数）为16384，即该参数最大值不能超过16384，即使超过也以16384为准；  
增加max\_connections参数的值，不会占用太多[系统][Link 3]资源。系统资源（CPU、内存）的占用主要取决于查询的密度、效率等；

解决方法：  
提高数据库最大连接数：  
1、直接设置，不需要重启：

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 </td> 
 <td> <code>mysql&gt; </code><code>set</code> <code>global</code> <code>max_connections=1500;</code> <code>Query OK, 0 </code><code>rows</code> <code>affected (0.00 sec)</code> &nbsp; <code>mysql&gt; show variables </code><code>like</code> <code>'max_connections'</code><code>;</code> <code>+</code><code>-----------------+-------+</code> <code>| Variable_name&nbsp;&nbsp; | Value |</code> <code>+</code><code>-----------------+-------+</code> <code>| max_connections | 1500|</code> <code>+</code><code>-----------------+-------+</code> <code>1 row </code><code>in</code> <code>set</code> <code>(0.00 sec)</code> </td> 
 </tr> 
 </tbody> 
</table>

2、配置文件设置，需要重启：  
修改my.cnf文件，在\[mysqld\]下面添加语句：

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 </td> 
 <td> <code>max_connections=1500</code> </td> 
 </tr> 
 </tbody> 
</table>

数值大小，根据并发情况调整，Max\_used\_connections/max\_connections<90%就可以。 
光靠上面的设置，还不够。执行show processlist查看执行任务，你会发现很多连接长时间在sleep。如果不是高峰期，这些连接也会消耗CPU和内存。这个需要通过程序设置最大超时时间wait\_timeout和interactive\_timeout。

(1)interactive\_timeout:  
参数含义：服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql\_real\_connect()中使用CLIENT\_INTERACTIVE选项的客户端。  
参数默认值：28800秒（8小时）

(2)wait\_timeout:  
参数含义：服务器关闭非交互连接之前等待活动的秒数。  
在线程启动时，根据全局wait\_timeout值或全局interactive\_timeout值初始化会话wait\_timeout值，取决于客户端类型(由mysql\_real\_connect()的连接选项CLIENT\_INTERACTIVE定义)。  
参数默认值：28800秒（8小时）

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 </td> 
 <td> <code>mysql&gt; </code><code>set</code> <code>global</code> <code>wait_timeout=30;</code> <code>mysql&gt; </code><code>set</code> <code>wait_timeout=30;</code> </td> 
 </tr> 
 </tbody> 
</table>

wait\_timeout过大有弊端，其体现就是MySQL里大量的SLEEP进程无法及时释放，拖累系统性能，不过也不能把这个指设置的过小，否则你可 能会遭遇到“MySQL has gone away”之类的问题。

> ## 欢迎关注公众号： ##
> 
> # ![70][] #

[Link 1]: https://www.2cto.com/database/
[Link 2]: https://www.2cto.com/database/201702/593739.html#
[Link 3]: https://www.2cto.com/os/
[70]: /images/20220513/d08546598a1646fdbdbac0acc19abfbf.png