14.10.4 性能问题与慢查询定位

性能问题与慢查询定位建议沿“路径→资源→SQL”三条线并行推进，并在 ProxySQL 侧与 MySQL 侧交叉验证。以下内容包含原理草图、命令示例、排错步骤与练习。

原理与定位路径#

定位顺序建议：
1) 路由是否命中正确规则与 Hostgroup
2) 连接池是否排队或饱和
3) SQL 是否高频或高耗时

---

1. 路由与规则命中检查（路径）#

目标：确认规则是否生效，是否误路由到低配/高延迟后端。

检查规则与命中#

-- 连接 ProxySQL 管理端
mysql -u admin -padmin -h 127.0.0.1 -P 6032

-- 查看路由规则
SELECT rule_id,active,match_digest,match_pattern,destination_hostgroup,apply
FROM mysql_query_rules
ORDER BY rule_id;

-- 查看规则命中统计
SELECT rule_id,hits,apply,match_digest,match_pattern
FROM stats_mysql_query_rules
ORDER BY hits DESC
LIMIT 20;

解释
- hits 高但 destination_hostgroup 指向不合理时，可能导致跨区或低配后端。
- 若 stats_mysql_query_rules 无命中，可能未加载或规则冲突。

排错要点#

• mysql_query_rules 修改后需 LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;
• 避免重复/冲突规则：更具体规则放前面（rule_id 小）

---

2. 连接池与资源瓶颈检查（资源）#

目标：判断 ProxySQL 还是后端 MySQL 造成慢。

连接池队列与后端忙碌#

-- 连接池状态
SELECT hostgroup, srv_host, srv_port, status, ConnUsed, ConnFree, ConnOK, ConnERR,
       MaxConnUsed, Queries, Bytes_data_recv, Bytes_data_sent
FROM stats_mysql_connection_pool
ORDER BY ConnUsed DESC;

-- 后端连接重置情况（连接抖动）
SELECT hostgroup, srv_host, srv_port, reset_events, reset_transactions
FROM stats_mysql_connection_pool_reset
ORDER BY reset_events DESC;

解释
- ConnUsed 持续接近上限，说明后端繁忙或连接数不足。
- reset_events 激增，可能连接不稳定或超时设置不合理。

系统层确认（ProxySQL 主机）#

# CPU/负载
top -c

# IO 等待与磁盘
iostat -x 1 5

# 网络延迟（ProxySQL -> MySQL）
ping -c 5 10.0.0.10

预期
- ProxySQL CPU 高但 MySQL 负载低：路由或 ProxySQL 处理瓶颈
- MySQL 负载高且 ProxySQL 正常：SQL 与索引问题优先

---

3. 慢查询定位（SQL）#

目标：基于 digest 统计识别高频慢语句与突发慢语句。

开启统计并查询 digest#

-- 确认 query digest 统计开启
SELECT * FROM global_variables WHERE variable_name='mysql-query_digest';

-- 查询慢语句统计
SELECT digest, count_star, sum_time, avg_time, first_seen, last_seen, digest_text
FROM stats_mysql_query_digest
ORDER BY sum_time DESC
LIMIT 10;

解释
- sum_time：总耗时高的“重负载”语句
- avg_time：单次慢的语句
- count_star：高频语句

对比 MySQL 慢日志#

-- MySQL 侧开启慢日志（示例）
SET GLOBAL slow_query_log=ON;
SET GLOBAL long_query_time=0.5;

排错判定
- ProxySQL 显示慢，但 MySQL 无慢日志：可能是网络/TLS、连接池饱和或复用受限
- 双侧均慢：优化 SQL、索引、执行计划

---

4. 典型问题与调整示例（排错）#

4.1 慢查询单独路由#

-- 将高耗时语句路由到独立 Hostgroup 20
INSERT INTO mysql_query_rules
(rule_id,active,match_digest,destination_hostgroup,apply)
VALUES (9001,1,'^SELECT .* FROM big_table',20,1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

4.2 连接池参数调整#

-- 示例：提升连接上限与超时
UPDATE mysql_servers
SET max_connections=200, max_replication_lag=10
WHERE hostgroup_id=10;

UPDATE global_variables
SET variable_value='2000'
WHERE variable_name='mysql-max_connections';

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

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

解释
- mysql-max_connections 控制 ProxySQL 侧连接池上限
- max_replication_lag 控制读库延迟阈值

4.3 复用禁用导致性能下降#

检查是否出现影响 multiplexing 的语句（事务、临时表、用户变量等），必要时调整应用或规则。

---

5. 诊断脚本（完整示例）#

#!/usr/bin/env bash
# 文件: /opt/proxysql/diag_slow.sh
# 说明: 快速输出 Top 慢语句与连接池状态

mysql -u admin -padmin -h 127.0.0.1 -P 6032 <<'SQL'
SELECT '=== TOP DIGEST ===' AS section;
SELECT digest, count_star, sum_time, avg_time, digest_text
FROM stats_mysql_query_digest
ORDER BY sum_time DESC
LIMIT 5;

SELECT '=== POOL STATUS ===' AS section;
SELECT hostgroup, srv_host, ConnUsed, ConnFree, MaxConnUsed, Queries
FROM stats_mysql_connection_pool
ORDER BY ConnUsed DESC;
SQL

预期效果
执行脚本后快速获取 Top 慢语句与最繁忙连接池。

---

6. 定位后验证与复盘#

-- 清空统计，观察优化后是否改善
SELECT * FROM stats_mysql_query_digest_reset;

-- 观察优化后指标变化
SELECT digest, count_star, sum_time, avg_time
FROM stats_mysql_query_digest
ORDER BY sum_time DESC
LIMIT 5;

验证指标：QPS、avg_time、ConnUsed、pool latency、MySQL 响应时间

---

练习与排错题#

1) 使用 stats_mysql_query_rules 找出命中最高的规则，并确认其目标 Hostgroup 合理。
2) 当 ConnUsed 持续接近上限时，分别用 ProxySQL 和 MySQL 侧指标判断瓶颈在何处。
3) 将一条高频慢语句路由到独立 Hostgroup，观察 sum_time 是否下降。

以上流程确保“路径、资源、SQL”三线并行，能快速定位 ProxySQL 性能问题并验证优化效果。