MySQL|数据库|MySQL数据库的Sleep进程过多的处理方式

前言：

MySQL数据库是和其它的关系型数据库基本一样，开箱了就能用，但性能方面还需要仔细的配置，最后才能完全的发挥它的性能，也需要相关服务的开发遵循数据库的开发规范，例如，减少大事务，尽量使用短连接，常用查询使用缓存技术，连接池扩展，读写分离，读写负载均衡等等技术，而这些技术通常是根据业务量来选择的，并不是用上就会提升性能，毕竟鞋子合适不合适，只有穿过了才知道。

那么，没有优化或者优化较少的情况下，MySQL容易出现Sleep进程过多的问题，这些进程会挤占MySQL数据库所在的操作系统的内存，导致系统的运行缓慢，当然了。数据库的查询，插入等等使用效率也会有所降低，严重的甚至会导致连接数据库的业务中断。

MySQL中的Sleep进程过多是一个常见的数据库维护问题，它通常指的是很多数据库连接处于空闲状态（Sleep状态），这些连接虽然没有执行任何实际操作，但仍占用了服务器的资源（如内存、线程等）。以下是处理MySQL中Sleep进程过多的一些步骤和建议：

1. 理解原因：

   • 持久连接：如果应用程序使用了持久连接（如PHP中的mysql_pconnect或mysqli的持久连接模式），即使脚本执行完毕，连接也不会立即关闭，而是在一段时间内保持闲置状态，形成Sleep进程。
   • 超时设置：wait_timeout 和 interactive_timeout 参数配置不合理，导致空闲连接在设定的时间内未主动关闭，依然保持在Sleep状态。
   • 程序设计问题：程序代码中未能正确关闭数据库连接，例如忘记调用 mysql_close 或使用PDO、mysqli等现代接口时没有确保连接释放。

2. 解决方案：

   • 调整连接策略：如果因持久连接引起，考虑改用非持久连接，确保每个请求结束后连接能被正常关闭。
   • 优化超时参数：根据应用的实际需求，适当减小wait_timeout和interactive_timeout的值，以便空闲连接能更快地被回收。但是要注意不要设置得过小，以免频繁创建新连接带来额外开销。
   • 程序层面改进：确保在所有数据库操作完成后，程序都正确且及时地关闭数据库连接。
   • 监控与清理：定期检查并手动清理长时间处于Sleep状态的连接，可以通过执行KILL命令终止这些连接（注意谨慎操作，避免误杀正在工作的连接）；也可以编写脚本或利用数据库监控工具自动监测和清理。
   • 连接池优化：对于高并发场景，可考虑使用连接池技术，它可以更高效地管理和复用数据库连接，从而减少Sleep进程数量。

3. 数据库配置：

   • 如果MySQL服务器版本支持，可以启用thread_pool功能以更智能地管理线程资源，特别注意MySQL5.7并不支持此配置，Percona Server for MySQL或MariaDB才有此功能
   • 调整max_connections参数，合理限制最大并发连接数，防止过多无用连接耗尽系统资源。

4. 应用层缓存：

   • 在应用层增加缓存机制，减少不必要的数据库访问，间接降低Sleep连接的产生。

总之，针对MySQL Sleep进程过多的问题，核心思路是找出产生这些空闲连接的原因，从应用程序设计、数据库配置、连接管理等多个角度进行优化。同时，定期审计和维护也是非常重要的。

         当然了，以上的建议对于云上MySQL也是有效的，而且据观察，云上数据库RDS可能更容易出现此类问题

         稍作总结

如果希望快速的立刻减少Sleep进程过多现象，无疑监控并清理选项是可以立竿见影的（上面的解决方案的第四条），当然，这个方法是等于自己实现的优化超时参数，也就是上面的解决方案的第二条，其它的方法需要更多的时间和耐心进行调整（主要看开发的优化意愿了）

解决方案的第四条，利用数据库监控工具

许多数据库监控工具，如MySQL Enterprise Monitor、Percona Monitoring and Management (PMM)、Zabbix等，都支持自定义告警规则和自动操作。你可以设置规则来监控长时间处于Sleep状态的连接，并在满足一定条件时自动清理。

例如，在PMM中，可以设置自定义警报，当发现长时间Sleep的连接时发送通知或直接执行清理操作。这样就能实时且安全地管理MySQL的Sleep连接，避免资源浪费。

本文将就手动清理做一个详细的解释

一、

脚本手动治理MySQL的Sleep进程过多问题

假设现在我有一个RDS数据库，那么，在一台可以连接到RDS的服务器上，随便安装一个maridb数据库，该数据库不需要启动，编写如下脚本即可：

vim kill_mysql_pid.sh

#!/bin/bash
#!author zsk
set -eu
ip=192.168.123.19
port=3306
password=123456
id=`mysqladmin -h $ip -uroot -P$port -p$password processlist |grep Sleep |awk -F '| '  '{print $2}'`
echo $id
for i in $id
do
mysqladmin -h $ip -uroot -P$port -p$password kill $i
done

三个变量，IP是RDS的IP地址（假设），port是RDS的对外端口（假设），密码是RDS的root密码，变量按实际填写就可以使用了，关键是root密码和连接地址，对于RDS服务器同样适用的

这样的清理比较霸道，需要谨慎适用

或者手动实现MySQL参数timeout的脚本：

#!/bin/bash
MYSQL_USER="your_username"
MYSQL_PASS="your_password"
MYSQL_HOST="localhost"
MYSQL_CONN="-u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST"
 
# 空闲连接阈值，单位秒
IDLE_THRESHOLD=3600
 
# 获取所有Sleep状态且空闲时间超过阈值的连接ID
CONNECTION_IDS=$(mysql $MYSQL_CONN -e "SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > ${IDLE_THRESHOLD} AND STATE = 'Sleep';")
 
# 关闭这些连接
for CONN_ID in $CONNECTION_IDS; do
    mysql $MYSQL_CONN -e "KILL ${CONN_ID};"
done

二、

线程池功能

线程池功能可以有效的自动控制MySQL的Sleep进程

MySQL 5.7并不自带内建的线程池功能。如果希望在MySQL 5.7中使用线程池技术来改善服务器性能，可以考虑使用第三方MySQL分支或者插件。

Percona Server for MySQL，作为MySQL的一个增强分支，提供了线程池插件。要在Percona Server 5.7中启用线程池功能，您可以按照以下步骤操作：

1. 安装Percona Server 5.7： 确保您已安装了包含线程池插件的Percona Server版本。如果不是，请从Percona官网下载并安装正确的版本。

2. 安装线程池插件： 登录到MySQL服务器（使用root权限或具有相应权限的用户），并安装线程池插件：

   INSTALL PLUGIN thread_pool SONAME 'percona-threadpool';
   

3. 配置线程池参数： 设置线程池的相关参数，如线程池大小、队列大小等。例如：

   Sql

   SET GLOBAL thread_pool_size = 16;
   SET GLOBAL thread_pool_max_threads = 32;
   SET GLOBAL thread_pool_min_threads = 4;
   

   根据实际业务负载需求调整这些参数。