14.9.4 连接池与线程模型调优

连接池与线程模型是ProxySQL性能的核心。本节从连接池参数、线程配置、队列与锁竞争、以及与MySQL后端交互的角度给出调优方法，并提供可执行示例、排错与练习。

1. 连接池核心机制与调优目标#

ProxySQL采用前端客户端连接与后端MySQL连接分离的池化模型。调优目标：
- 提高复用率：减少后端连接频繁建立/关闭的开销。
- 控制并发：避免后端连接数暴涨造成MySQL压力。
- 降低尾延迟：避免等待连接与线程阻塞导致响应变慢。

示例：查看当前连接池与等待情况

-- 进入ProxySQL管理端
mysql -uadmin -padmin -h127.0.0.1 -P6032

-- 查看后端连接池与等待队列
SELECT hostgroup, srv_host, status, ConnUsed, ConnFree, MaxConnUsed, Queries
FROM stats.stats_mysql_connection_pool;

-- 查看排队与执行情况
SELECT * FROM stats.stats_mysql_processlist LIMIT 10;

2. 后端连接池参数调优#

关键变量与设置示例（运行时生效+落盘）：

-- 设置全局后端最大连接数
UPDATE global_variables SET variable_value='2000'
WHERE variable_name='mysql-max_connections';

-- 设置默认连接池大小与空闲回收
UPDATE global_variables SET variable_value='200'
WHERE variable_name='mysql-connection_pool_size';
UPDATE global_variables SET variable_value='300'
WHERE variable_name='mysql-connection_idle_timeout';
UPDATE global_variables SET variable_value='3600'
WHERE variable_name='mysql-connection_lifetime';
UPDATE global_variables SET variable_value='7200000'
WHERE variable_name='mysql-connection_max_age_ms';

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

命令解释
- mysql-max_connections：ProxySQL对后端的连接上限。
- mysql-connection_pool_size：每个hostgroup连接池大小。
- mysql-connection_idle_timeout：空闲连接回收秒数。
- mysql-connection_lifetime：连接生命周期秒数。
- mysql-connection_max_age_ms：最长存活毫秒，防止僵尸连接。

示例：按hostgroup分配连接池

UPDATE mysql_servers SET max_connections=400
WHERE hostgroup_id=10 AND hostname='10.0.0.11';

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

预期效果：后端连接稳定在可控范围，ConnFree增加，ConnUsed峰值下降。

3. 客户端连接与前端线程模型调优#

关键变量示例：

-- 设置线程数与轮询间隔
UPDATE global_variables SET variable_value='16'
WHERE variable_name='mysql-threads';
UPDATE global_variables SET variable_value='100'
WHERE variable_name='mysql-poll_timeout';

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

命令解释
- mysql-threads：处理客户端请求的工作线程数。
- mysql-poll_timeout：事件轮询间隔（毫秒），越小延迟越低、CPU占用越高。

示例：基于CPU核数调优

# 查看CPU核心数
nproc

# 经验值：核数*1~2
# 若8核，先设置为16

4. 队列、锁竞争与并发瓶颈排查#

排查步骤示例

-- 1) 观察连接池等待与队列堆积
SELECT hostgroup, srv_host, ConnUsed, ConnFree, ConnOK, ConnERR
FROM stats.stats_mysql_connection_pool;

-- 2) 查看规则匹配开销（Top查询）
SELECT digest_text, count_star, sum_time, avg_time
FROM stats.stats_mysql_query_digest
ORDER BY sum_time DESC LIMIT 5;

常见问题与处理
- 等待连接多：增大mysql-connection_pool_size或拆分hostgroup。
- CPU高吞吐低：减少mysql-threads或拆分ProxySQL实例。
- 规则过多：合并规则，使用更窄的匹配条件。

排错命令：抓取实时连接与线程

# 查看ProxySQL进程线程数量与CPU消耗
top -H -p $(pidof proxysql)

# 查看网络连接状态
ss -tanp | grep proxysql | head

5. 与MySQL后端连接参数联动#

MySQL侧参数示例

-- 进入MySQL后端
mysql -uroot -p -h10.0.0.11 -P3306

SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'thread_cache_size';

建议设置（my.cnf示例）

[mysqld]
max_connections=2500
wait_timeout=7200
interactive_timeout=7200
thread_cache_size=128
skip_name_resolve=ON

说明：MySQL的max_connections应覆盖ProxySQL连接池上限+其他客户端。

6. 典型调优流程示例#

步骤化命令链

-- 1) 记录基线
SELECT NOW() AS ts, SUM(Queries) AS total_q
FROM stats.stats_mysql_connection_pool;

-- 2) 增大连接池
UPDATE global_variables SET variable_value='300'
WHERE variable_name='mysql-connection_pool_size';
LOAD MYSQL VARIABLES TO RUNTIME;

-- 3) 调整线程数
UPDATE global_variables SET variable_value='24'
WHERE variable_name='mysql-threads';
LOAD MYSQL VARIABLES TO RUNTIME;

-- 4) 观察效果
SELECT hostgroup, ConnUsed, ConnFree, MaxConnUsed, Queries
FROM stats.stats_mysql_connection_pool;

预期效果：ConnFree增加、排队减少、平均延迟下降。

7. 调优注意事项#

连接池过大导致MySQL内存消耗与上下文切换增加。
线程数过多会导致CPU抢占，反而升高延迟。
所有调整需分阶段变更并回归验证。

练习
1. 将mysql-connection_pool_size从200提升到300，记录前后ConnFree变化。
2. 将mysql-threads从16调到24，观察CPU与QPS变化。
3. 通过stats_mysql_query_digest找出Top慢查询，缩减规则匹配复杂度。