MySQL Connections

问题描述

CPU瞬间饱满，MySQL数据库爆出too many connection错误， MySQL ERROR日志报出[Warning] Too many connections，操作日志爆出Kernal: TCP: time wait bucket table overflow。

MySQL: ERROR 1040: Too many connections

MySQL连接允许长连接和短连接，其自身建立连接的过程存在较大开销，所以一般会采用长连接。但使用长连接后可能会占用内存增多，因为MySQL在执行查询过程中临时使用内存管理连接对象，这些连接对象资源只有在连接断开时才会释放。如果长连接累计很多将导致内存占用加大而被系统强制KILL而发生MySQL服务异常重启的现象。

针对长连接的这种情况需要定期断开，可以通过判断连接所占用内存大小来推测是否为持久性的长连接。另外可以在每次执行较大的操作后执行mysql_reset_connection来重新初始化后连接资源。

MySQL连接通常是一个用户请求一个连接，如果请求操作长时间没有执行完毕，会造成连接堆积，并迅速消耗数据库的连接数。也就是说如果数据库中有长时间没有执行完毕的SQL，它会一直占用着连接并不释放。而在此时应用的请求会一直不断的涌入数据库，造成数据库连接数被快速用完。

排查原因

• 访问量过高
• 数据库最大连接数过小
• 数据库建立了连接后忘记关闭
• 数据库死锁
  ...

解决方案

• 增加最大连接数
• 主从备份读写分离
• 业务拆分引入多个数据库实例
• 增减缓存减少查询
  ...

连接已满

连接数量满会导致客户端无法连接到数据库，因为数据库最大连接数有限，究其原因只有两种可能，一种是空闲连接过多，另一种是活动连接过多。

• 空闲连接过多

当应用使用长连接模式，此时应用侧应配置连接池，连接池的初始连接数量如果设置过高，应用启动后会建立多个到数据库实例的空闲连接。

当应用使用短连接模式，若出现大量空闲连接则说明应用没有在查询执行完毕后显式的关闭连接。

• 活动连接过多

1. 慢查询SQL增加将导致活动连接数堆积
2. 锁等待将导致活动连接数堆积，包括InnoDB锁等待，Metadata表元数据锁等待。
3. CPU使用率过高将导致活动连接堆积
4. IOPS（每秒IO吞吐量）使用率过高将导致活动连接堆积

检查流程

• 使用SHOW PROCESSLIST查看MySQL正在执行的线程，发现是否具有大量sleep休眠和timeout超时的线程。
• 使用kill杀掉所有sleep或timeout的线程，观察连接数是否又会暴增。
• 观察超时连接所执行的SQL语句发现跟具体表相关的查询超时的情况
• 使用CHECK TABLE table_name查询表是否有损坏，则使用REPAIR TABLE table_name修复表。

查看当前数据库建立的连接数量的方式有很多种

• 通过查询MySQL内部表information_schema.processlist记录数获取

mysql> SELECT COUNT(1) AS cnt FROM information_schema.processlist;
+-----+
| cnt |
+-----+
|   5 |
+-----+
1 row in set

• 执行show processlist统计线程数量

mysql> SHOW FULL PROCESSLIST

使用SHOW PROCESSLIST将会展示最近的100条线程，其中不包含background thread后台线程，因此可认为线程数约等于连接数。

查看当前连接数即数据库线程数量Threads_connected

mysql> SHOW STATUS LIKE "Threads_connected";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 5     |
+-------------------+-------+
1 row in set

查看服务器默认最大连接数max_connections

mysql> SHOW GLOBAL VARIABLES LIKE "max_connections";
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connections    | 16520 |
+--------------------+-------+
2 rows in set

查看当前服务器响应的最大连接数量max_used_connections

mysql> SHOW GLOBAL STATUS LIKE "max_used_connections";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 167   |
+----------------------+-------+
1 row in set

MySQL服务器最大连接数max_connections设置范围理想状态是服务器响应的最大连接数max_used_connections占服务器上限连接数值比例的10%，如果在10%以下则说明max_connections设置过高。

max_used_connection / max_connections = 10%

查看MySQL服务器最大用户并发连接数max_user_connections

mysql> SHOW VARIABLES LIKE "%
connections%";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| max_connections      | 512   |
| max_user_connections | 0     |
+----------------------+-------+
2 rows in set

查看当前服务器上MySQL连接请求数量

$ netstat -ant|grep "ESTABLISHED"|grep ":3306" -c

锁

查询是否锁表

mysql> SHOW OPEN TABLES WHERE In_use>0;

SHOW OPEN TABLES WHERE in_use>0命令用于查看被打开的表，这里的表不一定是被锁住的。因为每次数据库CURD操作都会打开表（OPEN TABLES），一个线程对应一个连接，为了维护不同带的状态，将会分别打开表，也就是OPEN TABLES。因此OPEN TABLES`说明是在操作表，但如果表锁了，也就无法打开了。

查看已经打开的表的数量

mysql> SHOW STATUS LIKE "Opened_tables";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 2     |
+---------------+-------+
1 row in set

查询当前正在锁的事务

mysql> SELECT * FROM information_schema.innodb_locks;

查询等待锁的事务

mysql> SELECT * FROM information_schema.innodb_lock_waits;