MySQL连接数不足导致服务异常GetConnectionTimeoutException
文章目录
- 场景复现
- 解决方案
- 一、调整连接数
- 二、优化程序
场景复现
已经上线正常运行的项目突然很多功能无法使用，查看程序日志发现MySQL报错，异常信息: Could not open JDBC Connection for transaction; nested exception is com.alibaba.druid.pool.GetConnectionTimeoutException: wait millis 60000, active 0, maxActive 200。根据报错信息来看是由于数据库连接数不足导致的，当新的请求到来，当前有效连接数超过maxActive连接数再获取连接时，就新来的请求就会处于排队状态， 当 排队时间超过maxWait，就会抛出该异常。解决方案有两种，一种是调大MySQL连接数，另一种就是优化程序。
解决方案
一、调整连接数
MySQL常用连接参数介绍 可通过指令 SHOW VARIABLES LIKE '%connect%'; 和 SHOW VARIABLES LIKE '%timeout%';查看以下内容:
| 参数名 | 默认值 | 介绍 |
|---|---|---|
| connect_timeout | 10 | 用于连接超时时间，单位为秒 |
| max_connect_errors | 100 | 允许单用户连接错误最大值，超过后在不刷新状态的情况下，禁止再次连接 |
| max_user_connections | 0 | 用户连接最大限制，默认0表示无限制，遵守总连接数小于等于max_connections |
| max_connections | 100 | MySQL支持的最大连接数 |
| innodb_lock_wait_timeout | 120 | 事务等待获取资源等待的最长时间，超过这个时间还未分配到资源则抛出异常，单位为秒 |
| innodb_rollback_on_timeout | OFF | 在MySQL 5.6&5.7中默认值为OFF，当InnoDB默认情况下仅回滚事务超时的最后一条语句。如果innodb_rollback_on_timeout值为ON，则事务超时后将导致InnoDB中止并回滚整个事务 |
| slave_net_timeout | 60 | slave网络连接超时时间(当数据库使用主从时使用) |
当出现连接数不够时，可以通过指令SHOW VARIABLES LIKE '%max_connections%';查看数据库配置的最大连接数。
如果您的程序并发较高，但是数据连接数较小，可以通过指令调大连接数或者更改数据库的配置文件my.cnf的max_connections参数。通过指令调大连接数:set global max_connections=1000;将连接数调大到1000。如果您的连接数已经设置到了较大的数量，还是出现连接数不够，那么需要排查程序是否有问题，就是下面的优化程序。
二、优化程序
由于我设置的连接数为2000，并且系统的并发也不高。因此可以推测出是程序上的问题。通过指令SHOW FULL PROCESSLIST;查看MySQL服务器上的所有连接，包括连接ID、用户、主机、当前执行的SQL语句等信息。
参数分析：
| 参数名 | 介绍 |
|---|---|
| ID | 用户登录mysql时，系统分配的"connection_id |
| USER | 当前用户 |
| HOST | 执行该SQL是由哪台机器操作的 |
| DB | 进程目前连接的数据库 |
| COMMAND | 当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等 |
| TIME | 状态持续时间，单位为秒 |
| STATE | 当前连接的sql语句的状态 |
| INFO | 当前执行的SQL语句，可以作为程序优化的重要条件 |
找出time时间过长的语句，再查看当前state状态，如果当前state是 Updating或者Sending data，则表示当前正在执行查询或者修改操作，并且执行时间过长。将INFO里的执行SQL信息拿出来，加上Explain进行分析。大概率是因为SQL查询或者修改效率过低，没走索引导致连接一直被占用，最后出现连接数不够的情况。这种情况一般需要我们优化SQL，增加索引即可解决问题。更多关于show processlist文章可以参考show processlist 史上最全参数详解及解决方案