1. MySQL连接数爆满的紧急处理方案当MySQL突然抛出Too many connections错误时整个系统可能瞬间瘫痪。作为DBA我经历过无数次这样的惊险时刻。下面分享一套经过实战检验的应急处理流程1.1 30秒止血操作立即通过MySQL超级用户执行以下命令无需重启服务SET GLOBAL max_connections 500; SET GLOBAL wait_timeout 30; FLUSH PRIVILEGES;这三个命令的组合拳能立即缓解连接池压力临时扩大连接池容量根据服务器配置调整一般4核8G机器设为500较安全将空闲连接超时从默认8小时缩短到30秒刷新权限使设置立即生效重要提示此操作只是临时解决方案就像给漏水的管道打补丁。必须继续排查根本原因否则问题会反复出现。1.2 连接状态实时诊断执行这个增强版监控命令SHOW PROCESSLIST WHERE Command ! Sleep AND Time 2 ORDER BY Time DESC;关键列解读Id连接会话ID可用于后续强制终止User发起连接的用户帮助定位问题应用Time持续时间(秒)长时间运行的要重点关注State当前状态出现Sending data、Copying to tmp table等要警惕Info正在执行的SQL片段1.3 连接泄漏快速定位使用这个查询找出可能的连接泄漏源SELECT user, host, count(*) as connection_count FROM information_schema.processlist GROUP BY user, host ORDER BY connection_count DESC LIMIT 5;典型问题模式某个应用服务器IP的连接数异常偏高测试账号存在大量长期连接监控系统过度频繁采集2. 深度根因分析技术2.1 连接池配置检查检查应用层连接池配置以Druid为例// 错误配置示例容易导致泄漏 dataSource.setMaxActive(50); // 最大连接数 dataSource.setMinIdle(10); // 最小空闲连接 dataSource.setMaxWait(30000); // 获取连接超时时间(毫秒) // 推荐配置 dataSource.setMaxActive(20); dataSource.setMinIdle(2); dataSource.setMaxWait(1000); dataSource.setRemoveAbandoned(true); // 自动回收泄漏连接 dataSource.setRemoveAbandonedTimeout(60); // 60秒后回收常见配置陷阱最大连接数设置过高超过MySQL承受能力获取连接超时时间过长导致线程堆积未启用连接泄漏检测机制2.2 慢查询分析使用performance_schema快速定位问题SQL-- 开启历史SQL记录 UPDATE performance_schema.setup_consumers SET ENABLED YES WHERE NAME LIKE events_statements%; -- 查询最耗时的SQL SELECT digest_text, count_star, avg_timer_wait/1000000000 as avg_ms FROM performance_schema.events_statements_summary_by_digest ORDER BY avg_timer_wait DESC LIMIT 10;2.3 连接生命周期追踪在my.cnf中添加连接追踪配置[mysqld] log_warnings2 log_error_verbosity3通过错误日志可以观察到2023-08-20T14:23:01.123456Z 10 [Note] Aborted connection 10 to db: test user: app_user host: 192.168.1.100 (Got timeout reading communication packets)这种日志能清晰显示哪些连接异常终止。3. 长效解决方案设计3.1 连接池优化方案推荐配置模板基于8核16G服务器# MySQL服务端配置 max_connections 800 wait_timeout 600 interactive_timeout 600 # 应用连接池配置(Druid) initialSize5 maxActive50 minIdle5 maxWait500 testWhileIdletrue timeBetweenEvictionRunsMillis30000 minEvictableIdleTimeMillis1800003.2 连接泄漏防护体系构建四层防护网应用层确保所有JDBC操作使用try-with-resourcestry (Connection conn dataSource.getConnection(); PreparedStatement stmt conn.prepareStatement(sql)) { // 业务代码 }中间件层配置连接池的泄漏检测参数spring: datasource: druid: remove-abandoned: true remove-abandoned-timeout: 120 log-abandoned: true数据库层设置用户级连接限制CREATE USER app_user% WITH MAX_USER_CONNECTIONS 50;监控层部署Prometheus监控- job_name: mysql static_configs: - targets: [mysql-host:9104] metrics_path: /metrics3.3 连接风暴应对策略当突发流量导致连接激增时启用读写分离分流查询配置HikariCP的快速失败机制dataSource.setConnectionTimeout(200); // 毫秒 dataSource.setInitializationFailTimeout(1);使用ProxySQL实现连接排队INSERT INTO mysql_servers (hostgroup_id,hostname,port) VALUES (10,mysql-master,3306); UPDATE global_variables SET variable_valuetrue WHERE variable_namemysql-set_query_lock_on_hostgroup; LOAD MYSQL VARIABLES TO RUNTIME;4. 高级诊断技巧4.1 线程状态深度解读通过SHOW PROCESSLIST观察到的状态码解析状态码含义危险等级处理建议Creating sort index创建排序索引高检查排序字段是否有索引Sending data发送数据到客户端中检查网络带宽和结果集大小Waiting for table lock等待表锁紧急立即检查锁竞争情况Copying to tmp table使用临时表高优化GROUP BY/ORDER BY4.2 性能模式(Performance Schema)实战启用全量监控MySQL 8.0UPDATE performance_schema.setup_consumers SET ENABLED YES; UPDATE performance_schema.setup_instruments SET ENABLED YES, TIMED YES;关键分析查询-- 查找连接最多的客户端IP SELECT PROCESSLIST_HOST, COUNT(*) FROM performance_schema.threads WHERE TYPEFOREGROUND GROUP BY PROCESSLIST_HOST ORDER BY COUNT(*) DESC; -- 统计SQL执行阶段耗时 SELECT event_name, count_star, sum_timer_wait/1000000000 as total_sec, avg_timer_wait/1000000000 as avg_sec FROM performance_schema.events_stages_summary_global_by_event_name ORDER BY sum_timer_wait DESC;4.3 连接池压测方法使用sysbench模拟连接风暴sysbench oltp_read_only \ --db-drivermysql \ --mysql-host127.0.0.1 \ --mysql-port3306 \ --mysql-usertest \ --mysql-passwordtest \ --mysql-dbsbtest \ --threads200 \ --time300 \ --report-interval10 \ run监控关键指标-- 每秒查询数 SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME Questions; -- 当前连接数 SELECT COUNT(*) FROM information_schema.processlist;5. 生产环境经验总结在金融级系统中我们总结出这些黄金法则连接数计算公式推荐max_connections (可用内存MB - 系统预留) / 每个连接预估内存MB MySQL 8.0每个连接约占用4-10MB内存连接池大小经验值Web应用maxActive (CPU核心数 * 2) 有效磁盘数 批处理应用maxActive CPU核心数 / 0.1必须监控的指标Threads_connectedThreads_runningAborted_connectsConnection_errors_max_connections连接泄漏检测脚本#!/bin/bash ALERT_THRESHOLD300 CURRENT_CONN$(mysql -e SHOW STATUS LIKE Threads_connected | awk NR2{print $2}) [ $CURRENT_CONN -ge $ALERT_THRESHOLD ] \ echo 警告当前连接数 $CURRENT_CONN | mail -s MySQL连接警报 dbaexample.com最后分享一个真实案例某电商大促期间因未设置MAX_USER_CONNECTIONS导致一个异常服务创建了800连接拖垮整个数据库集群。事后我们通过ProxySQL实现了连接数限制和自动kill机制类似问题再未发生。
