MySQL连接太多导致数据无法入库

解决MySQL连接太多,导致数据无法入库的情况

MySQL


问题

出现业务系统无法登录及数据无法入库等现象。查询MySQL连接数类似如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> show processlist;
+--------+--------------+---------------------+----------------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+----------+-------------------+----------------+---------+------+----------+------------------------+
| 96246 | ss | 10.200.8.88:39268 | ss | Sleep | 0 | | NULL |
| 409903 | dba | localhost | bigdata_report | Sleep | 8416 | | NULL |
| 435370 | bizlogic | 10.200.3.38:35339 | greenLife | Sleep | 28 | | NULL |
| 435371 | bizlogic | 10.200.3.38:35340 | greenLife | Sleep | 28 | | NULL |
| 436367 | bizlogic | 10.200.3.38:35606 | greenLife | Sleep | 813 | | NULL |
| 436368 | bizlogic | 10.200.3.38:35607 | greenLife | Sleep | 562 | | NULL |
| 436818 | bizlogic | 10.200.3.38:35746 | greenLife | Sleep | 3236 | | NULL |
| 436819 | bizlogic | 10.200.3.38:35747 | greenLife | Sleep | 3236 | | NULL |
| 436820 | bizlogic | 10.200.3.38:35748 | greenLife | Sleep | 3236 | | NULL |
| 436821 | bizlogic | 10.200.3.38:35749 | greenLife | Sleep | 3236 | | NULL |
| 439468 | bizlogic | 10.200.3.38:40577 | greenLife | Sleep | 1413 | | NULL |
| 439469 | bizlogic | 10.200.3.38:40578 | greenLife | Sleep | 1413 | | NULL |
| 439470 | bizlogic | 10.200.3.38:40579 | greenLife | Sleep | 1413 | | NULL |
| 439471 | bizlogic | 10.200.3.38:40580 | greenLife | Sleep | 1413 | | NULL |
| 442014 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 442026 | bizlogic | 10.200.2.36:34724 | medical | Sleep | 0 | | NULL |
+--------+--------------+---------------------+----------------+---------+------+----------+------------------+
160 rows in set (0.00 sec)


资料

分析与解决

初步推测是mysql连接数达到上限,无法建立新连接导致数据无法读取和存入。怀疑开发代码质量问题,没有使用连接池或者连接建立后没有销毁。
方法是先关闭Sleep状态连接,同时下线相关试运行业务进行抢救。
由于Sleep状态的连接多达140多个,一一杀掉速度太慢。网络上提供的修改方法一般是修改/etc/my.cnf,在这个文件中添加一行wait_timeout=超时时间。这需要重启服务,会影响现有上线业务。
实际上有一种比较简单的方法来修改这个参数:首先作为超级用户登录到MYSQL,注意必须是超级用户,否则后面会提示没有修改权限。然后输入show global variables like 'wait_timeout';,回车执行后显示目前的超时时间:

1
2
3
4
5
6
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

上面显示的是默认的超时时间,即8个小时(单位是秒)。
在MYSQL的默认设置中,如果一个数据库连接超过8小时没有使用(闲置8小时),服务器将断开这条连接,后续在该连接上进行的查询操作都将失败。
现在重新设置该参数,将超时时间设置成10秒。等10秒后sleep连接都销毁后,将超时时间还原为28800(10h)。
问题解决。

反思

长连接 & 短连接
  • 长连接:连接->数据传输->关闭连接;
  • 短连接:连接->数据传输->保持连接->数据传输->保持连接->…->关闭连接

长连接主要用于在少数客户端与服务端的频繁通信,因为这时候如果用短连接频繁通信常会发生Socket出错,并且频繁创建Socket连接也是对资源的浪费。
但是对于服务端来说,长连接也会耗费一定的资源,需要专门的线程(unix下可以用进程管理)来负责维护连接状态。

如果使用了长连接而长期没有对数据库进行任何操作,那么在timeout值后,mysql server就会关闭此连接,而客户端在执行查询的时候就会得到一个类似于“MySQL server has gone away“这样的错误。
在使用mysql_real_connect连接数据库之后,再使用mysql_options( &mysql, MYSQL_OPT_RECONNECT, … ) 来设置为自动重连。这样当mysql连接丢失的时候,使用mysql_ping能够自动重连数据库。如果是在mysql 5.1.6之前,那么则应在每次执行完real_connect 之后执行mysql_options( &mysql, MYSQL_OPT_RECONNECT, … ) ,如果是mysql 5.1.6+,则在connect之前执行一次就够了。

wait_timeout & interactive_timeout
  • interactive_timeout:
    • 参数含义:服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。
    • 参数默认值:28800秒(8小时)
  • wait_timeout:
    • 参数含义:服务器关闭非交互连接之前等待活动的秒数。在线程启动时,根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值,取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义)。
    • 参数默认值:28800秒(8小时)

交互式操作:通俗的说,就是你在你的本机上打开mysql的客户端,就是那个黑窗口,在黑窗口下进行各种sql操作,当然走的肯定是tcp协议。
非交互式操作:就是你在你的项目中进行程序调用。比如一边是tomcat web服务器,一边是数据库服务器,两者怎么通信?在java web里,我们通常会选择hibernate或者是jdbc来连接。那么这时候就是非交互式操作。


参考资料:

  1. mysql 的sleep线程过多处理方法
如果文章对您有帮助,感谢您的赞助支持!