Write by lyc at 2018-08-01

一、to many connections

show proceslist 时发现大量的sleep，有什么风险吗，该如何处理？

可能的风险有：

  • 大量sleep线程会占用连接数，当超过max_connections后，新连接无法再建立，业务不可用
  • 这些sleep线程中，有些可能有未提交事务，可能还伴随着行锁未释放，有可能会造成严重锁等待
  • 这些sleep线程中，可能仍有一些内存未释放，数量太多的话，是会消耗大量无谓的内存的，影响性能

建议应对措施：

  • 升级到5.7及以上版本，连接性能有所提升；
  • 采用MariaDB/Percona版本，根据情况决定是否启用thread pool功能；
  • 适当调低wait_timeout，interactive_timeout值，例如只比java连接池的timeout时间略高些即可；
  • 利用 pt-kill 或辅助脚本/工具巡查并杀掉无用sleep进程；
  • 利用5.7的新特性，适当设置max_execution_time阈值，消除长时间执行的SQL；
  • 定期检查show processlist的结果，找到长时间sleep的线程，根据host&port反推找到相关应用负责人，协商优化方案。

【ZST】to many connections解决措施

  • super 权限不要分配给程序，mysqld会为super权限保持最后一个连接
  • max_connections=1000,max_user_connections=800，防止某一个用户连接过多，导致占用所有连接，管理用户也无法登陆数据库。

二、connections连接数参数

1
2
3
4
5
6
7
### connections VARIABLES
skip_name_resolve = 1
max_connections = 512
max_connect_errors = 1000000
back_log = 1024
interactive_timeout = 600
wait_timeout = 600

skip_name_resolve

  • 如果这个参数设为OFF，则MySQL服务在检查客户端连接的时候会解析主机名；
  • 如果这个参数设为ON，则MySQL服务只会使用IP，在这种情况下，授权表中的Host字段必须是IP地址或localhost。 （关闭域名解析，使用ip port加快解析速度）
  • 这个参数默认是关闭的。

max_connections

允许客户端并发连接的最大数量，默认值是151，一般将该参数设置为500-2000

max_connect_errors

  • 如果客户端尝试连接的错误数量超过这个参数设置的值，则服务器不再接受新的客户端连接。
  • 可以通过清空主机的缓存来解除服务器的这种阻止新连接的状态，通过FLUSH HOSTS或mysqladmin flush-hosts命令来清空缓存。
  • 这个参数的默认值是100，一般将该参数设置为100000。

back_log

MySQL服务器连接请求队列所能处理的最大连接请求数，如果队列放满了，后续的连接才会拒绝。
当主要的MySQL线程在很短时间内获取大量连接请求时，这个参数会生效。接下来，MySQL主线程会花费很短的时间去检查连接，然后开启新的线程。这个参数指定了MySQL的TCP/IP监听队列的大小。 如果MySQL服务器在短时间内有大量的连接，可以增加这个参数。

interactive_timeout

Mysql关闭交互连接前的等待时间，单位是秒，默认是8小时，建议不要将该参数设置超过24小时，即86400。

wait_timeout

Mysql关闭非交互连接前的等待时间，单位是秒，默认是8小时，建议不要将该参数设置超过24小时，即86400

三、如何配置MySQL最大并发连接数？

MySQL 优化系列（1）– InnoDB重要参数优化

经常会遇见"MySQL: ERROR 1040: Too many connections"的情况：

  • 一种是访问量确实很高，MySQL服务器抗不住，这个时候就要考虑增加从服务器分散读压力。
  • 另外一种情况是MySQL配置文件中max_connections值过小：

1.查看当前数据的max_connections

1
2
3
4
5
6
7
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 2048  |
+-----------------+-------+
1 row in set (0.00 sec)

2.查看当前数据历史的最大并发连接数

1
2
3
4
5
6
7
mysql> show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 514   |
+----------------------+-------+
1 row in set (0.00 sec)

3.如何配置max_connections？

  1. 首先，历史的最大并发连接数一定要小于配置的最大并发连接数，并且留有一定的缓冲空间。
  2. 比较理想的设置是：最大连接数占上限连接数的85%左右，如果发现比例在10%以下，MySQL服务器连接数上限设置的过高了。
    1
    Max_used_connections / max_connections * 100% ≈ 85%