6.7.4 连接与线程参数调优

连接与线程参数调优#

连接与线程参数直接影响并发能力、资源占用与稳定性。调优目标是在保证业务并发的前提下，控制线程创建开销与上下文切换，避免连接耗尽与长连接堆积。

原理草图：连接与线程生命周期#

文章图片

1. 连接管理核心参数与示例#

  • max_connections：最大连接数上限。需结合业务并发峰值与服务器内存估算。
  • max_user_connections：限制单用户连接数，防止异常应用占满连接池。
  • wait_timeout / interactive_timeout：空闲连接超时，避免僵尸连接长期占用资源。
  • max_connect_errors：连接错误次数阈值，防止异常主机持续重试。

查看当前参数与连接状态：

SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'wait_timeout';
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';
SHOW STATUS LIKE 'Threads_created';

修改配置（推荐持久化配置，路径示例：/etc/my.cnf）：

[mysqld]
max_connections = 800
max_user_connections = 100
wait_timeout = 600
interactive_timeout = 600
max_connect_errors = 1000

重启并验证生效：

systemctl restart mysqld
mysql -uroot -p -e "SHOW VARIABLES LIKE 'max_connections';"

2. 线程与调度相关参数与示例#

  • thread_cache_size：缓存线程数量，减少频繁创建销毁线程开销。
  • thread_stack：线程栈大小，默认一般足够。
  • thread_handling：线程模型选择，通常保持默认。
  • innodb_thread_concurrency：限制InnoDB并发线程数。

观察线程创建频率并调整：

SHOW STATUS LIKE 'Threads_created';
SHOW VARIABLES LIKE 'thread_cache_size';

动态调整（在线生效）：

SET GLOBAL thread_cache_size = 200;

持久化配置：

[mysqld]
thread_cache_size = 200
innodb_thread_concurrency = 0

3. 连接池与应用侧配合建议（含示例）#

应用连接池最大值不应超过 MySQL max_connections 的70%~80%，预留给管理连接与备份任务。

示例：Java HikariCP 配置（application.yml）

spring:
  datasource:
    hikari:
      minimum-idle: 20
      maximum-pool-size: 200
      idle-timeout: 600000
      max-lifetime: 1800000

4. 典型调优步骤（含命令链路）#

  1. 基于业务峰值统计并发连接数与TPS/QPS。
  2. 检查 Threads_connected、Threads_running、Threads_created 变化趋势。
  3. 调整 thread_cache_size 以降低 Threads_created 增长。
  4. 结合内存评估与慢查询治理，设置合理 max_connections。
  5. 设置空闲超时，清理长时间不活跃连接。
  6. 压测验证并观察连接错误率与响应时延变化。

采样脚本（每5秒输出连接状态）：

#!/bin/bash
# 文件路径：/usr/local/bin/mysql_conn_watch.sh
while true; do
  mysql -uroot -p'YourPass' -e "
  SHOW STATUS LIKE 'Threads_connected';
  SHOW STATUS LIKE 'Threads_running';
  SHOW STATUS LIKE 'Threads_created';"
  sleep 5
done

5. 监控与告警指标（Prometheus示例）#

若使用 mysqld_exporter，可关注以下指标：

mysql_global_status_threads_connected
mysql_global_status_threads_running
mysql_global_status_threads_created
mysql_global_status_connections
mysql_global_status_aborted_connects

6. 常见问题与排错#

问题1：连接数耗尽（Too many connections）
排错与处理：

SHOW PROCESSLIST;
SELECT user, COUNT(*) c FROM information_schema.PROCESSLIST GROUP BY user ORDER BY c DESC;

临时扩容：

SET GLOBAL max_connections = 1000;

进一步检查连接池泄漏与长事务。

问题2：线程创建频繁
排错与处理：

SHOW STATUS LIKE 'Threads_created';
SHOW VARIABLES LIKE 'thread_cache_size';

提升线程缓存：

SET GLOBAL thread_cache_size = 300;

问题3：空闲连接堆积
排错与处理：

SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'wait_timeout';

缩短空闲超时：

SET GLOBAL wait_timeout = 300;

7. 练习#

  1. 将 max_connections 设置为 500，验证连接限制是否生效。
  2. 通过 Threads_created 观察线程创建速率，逐步调整 thread_cache_size，找到创建速率明显下降的阈值。
  3. 模拟空闲连接，调整 wait_timeout，观察断开时间差异。
  4. 使用连接池配置，使应用连接数不超过 max_connections 的 70%。