MySQL dump 导致连接池被占满问题分析与解决方案

Java异常信息

分析

先查看一下当前数据库的连接数量


mysql> show status where variable_name = 'threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 50    |
+-------------------+-------+
1 row in set (0.04 sec)

mysql>

然后查看最大的连接数量

mysql> show variables like "%connection%";
+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| character_set_connection | utf8            |
| collation_connection     | utf8_general_ci |
| max_connections          | 800             |
| max_user_connections     | 0               |
+--------------------------+-----------------+
4 rows in set (0.01 sec)

可以看到连接数量是完全够用的。那么问题可能处在报错的时间点，数据库服务器应该有大量的请求。

查看数据库服务器慢查询监控

可以看到相应时间点有725个慢查询，基本和我们的数据库服务器的连接池大小一致。

慢查询的主要问题是在获取锁，那么说明在 00:01:10.000 的时候数据库全库被锁。

问题分析

全库被锁说明有一个数据库服务器级别的操作触发了全库锁，而且还是在凌晨，基本猜到是备份程序了。

[root@db ~]# crontab -l
00 00 * * * bash /opt/backup/backup_haoyong.sh
[root@db ~]#

这个备份程序的逻辑很简单

mysqldump -uroot -pxxxx dbname

查看一下mysqldumo的默认配置

[root@db ~]# mysqldump --help | grep lock
  --add-locks         Add locks around INSERT statements.
                      (Defaults to on; use --skip-add-locks to disable.)
                      options --skip-add-drop-table --skip-add-locks
                      turn --lock-all-tables on, unless --single-transaction is
                      specified too (in which case a global read lock is only
                      of the dump.Option automatically turns --lock-tables off.
                      using --lock-all-tables or --master-data: in this case
                      moment all tables are locked. So if you want your dump
                      should use --lock-all-tables or --master-data with
  -x, --lock-all-tables 
                      by taking a global read lock for the duration of the
                      --lock-tables off.
  -l, --lock-tables   Lock all tables for read.
                      (Defaults to on; use --skip-lock-tables to disable.)
                      --lock-all-tables on, unless --single-transaction is
                      specified too (in which case a global read lock is only
                      of the dump. Option automatically turns --lock-tables
  --opt               Same as --add-drop-table, --add-locks, --create-options,
                      --quick, --extended-insert, --lock-tables, --set-charset,
                      from them. Option automatically turns off --lock-tables.
  --skip-opt          Disable --opt. Disables --add-drop-table, --add-locks,
                      --lock-tables, --set-charset, and --disable-keys.
add-locks                         TRUE
lock-all-tables                   FALSE
lock-tables                       TRUE

默认是开启 lock-tables的，这个作用主要是为了保障数据备份过程中的一致性，你一边备份程序一边写入那么备份出来的数据一定是不一致的。

但是我们使用的innodb存储引擎，完全可以开启一个事务使用mvcc来保证我们看到一个一致的快照，也就没有必要加锁了。

For each dumped database, lock all tables to be dumped before dumping them. The tables are locked with READ LOCAL to permit concurrent inserts in the case of MyISAM tables. For transactional tables such as InnoDB, --single-transaction is a much better option than --lock-tables because it does not need to lock the tables at all.

Because --lock-tables locks tables for each database separately, this option does not guarantee that the tables in the dump file are logically consistent between databases. Tables in different databases may be dumped in completely different states.

Some options, such as --opt, automatically enable --lock-tables. If you want to override this, use --skip-lock-tables at the end of the option list.

解决方案

备份程序添加 --single-transaction 选项。

总结

监控日志是解决线上问题的基础，任何项目都需要做到相应级别的监控。小项目可能不需要上ELK全套，那么你最起码把慢查询日志打开，后续使用其他线下的工具也是可以分析的。没有日志和监控，没有数据支撑，识别问题解决问题的效率必然低下。

lihongjie0209 / myblog