文章目录
- 场景复现
- 解决方案
- 一、调整连接数
- 二、优化程序
场景复现
已经上线正常运行的项目突然很多功能无法使用,查看程序日志发现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 史上最全参数详解及解决方案