正文

MySQL慢查询监控神器Percona Monitoring Plugins实战从配置到性能瓶颈诊断

/0 浏览量
0905

写在前面：为什么选Percona？

先说个故事。去年冬天，我带的团队负责一个日均千万级访问的电商系统。某天凌晨三点，DBA老张接到告警，说数据库CPU飙到98%，查询延迟从200ms暴涨到8秒。我们排查了整整六小时，最后发现是一个新上线的营销功能触发了全表扫描，而那个表有2.3亿行数据。

如果当时我们早半小时看到慢查询日志，可能就不会有那个”难忘的冬夜”。

这就是为什么我强烈推荐Percona Monitoring Plugins——它不是另一个监控工具，而是你数据库的”心电图仪”。今天我就手把手教你把它用起来。

一、Percona Monitoring Plugins是什么？

Percona Monitoring Plugins（简称PMP）是Percona公司开源的一套监控组件，专门针对MySQL/Percona Server优化。它包含三个核心部分：

1. Percona Server for MySQL 这是Percona维护的MySQL分支，内置了Performance Schema增强、主动负载管理、数据加密等特性。你可以把它理解为MySQL的”性能增强版”。

2. Percona Monitoring Plugins（监控脚本） 这是一组bash脚本和Python插件，用于采集MySQL的内部指标，包括：

  • 连接数、QPS、TPS
  • InnoDB缓冲池命中率
  • 慢查询统计
  • 复制延迟
  • 表锁等待

3. Percona Monitoring and Management（PMM） 这是可视化管理平台，但今天我们只聊PMP插件部分。

核心优势：不需要修改MySQL配置，零侵入，开箱即用。

二、环境准备：你的服务器需要满足什么？

2.1 支持的MySQL版本

  • MySQL 5.6⁄5.7⁄8.0
  • Percona Server for MySQL 5.7⁄8.0
  • MariaDB 10.1+

注意：MySQL 5.6需要额外安装mysql-plugin包，因为Performance Schema支持较弱。

2.2 系统要求

  • Linux发行版：CentOS 7+/Ubuntu 18.04+/Debian 10+
  • 权限：需要root或sudo权限
  • 依赖：Python 2.7+ 或 Python 3.6+，bash，curl

2.3 示例环境

# 查看MySQL版本
mysql -V
# 输出：mysql  Ver 8.0.32 for Linux on x86_64 (Percona Server (GPL), Release '32', Revision 'r1')

# 查看系统信息
cat /etc/os-release

三、安装配置：三步搞定

3.1 下载PMP插件

# CentOS/RHEL系统
yum install percona-monitoring-plugins -y

# Ubuntu/Debian系统
apt-get install percona-monitoring-plugins -y

# 或者手动下载最新包
wget https://www.percona.com/downloads/percona-monitoring-plugins/LATEST/

3.2 配置监控脚本

PMP插件安装在/usr/share/percona-monitoring-plugins/目录，核心脚本包括：

# 查看安装的脚本
ls -lh /usr/share/percona-monitoring-plugins/

核心脚本清单：

脚本名 功能
mysql_tzinfo_to_sql 时区同步
check_mysql_health 健康检查（连接数、QPS等）
check_replication 主从延迟检测
check_slave_status 从库状态监控
collectd_mysql 采集指标发送到collectd
mysql_exporter Prometheus格式导出

3.3 配置监控用户

PMP需要特定的MySQL用户来采集数据。执行以下SQL：

-- 创建监控用户（MySQL 8.0+）
CREATE USER 'pmp'@'localhost' IDENTIFIED BY 'StrongPassword123!';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'pmp'@'localhost';
FLUSH PRIVILEGES;

-- 旧版本MySQL可能需要额外权限
GRANT SELECT ON performance_schema.* TO 'pmp'@'localhost';

安全提示：

  • 使用强密码（至少16位，包含大小写、数字、特殊字符）
  • 限制只从localhost连接
  • 定期轮换密码

3.4 测试连接

# 测试监控用户权限
mysql -u pmp -h localhost -p -e "SHOW PROCESSLIST;"
mysql -u pmp -h localhost -p -e "SHOW SLAVE STATUS\G"

四、核心监控指标详解

4.1 连接数监控

为什么重要：连接数爆满是MySQL最常见的性能瓶颈之一。每个连接占用内存（约256KB基础+查询缓存），过多连接会导致内存耗尽。

关键指标：

# 查看当前连接数
mysql -u pmp -h localhost -p -e "SHOW STATUS LIKE 'Threads%';"

输出解读：

变量名 含义 健康阈值
Threads_connected 当前连接数 < max_connections * 0.8
Threads_running 正在执行的线程 < 10（单核）
Threads_created 创建的线程总数 持续增长说明连接泄漏
Threads_cached 线程缓存命中数 > 0.9

示例场景：

假设你的服务器配置：

  • max_connections = 500
  • innodb_buffer_pool_size = 8GB
-- 计算健康连接数上限
-- 公式：max_connections * 0.8 = 400

-- 查看当前状态
SHOW STATUS LIKE 'Threads_connected';
-- 输出：Threads_connected | 450

-- 警告！已超过80%阈值

告警策略：

  • 连接数 > 400：发送警告
  • 连接数 > 450：发送紧急告警
  • Threads_running > 20：检查是否有长事务或锁等待

4.2 InnoDB缓冲池命中率

为什么重要：InnoDB缓冲池是MySQL性能的核心。命中率低意味着频繁磁盘IO，查询延迟飙升。

关键指标：

# 查看缓冲池命中率
mysql -u pmp -h localhost -p -e "
SELECT 
  (1 - (Pages_free * 1.0 / (SELECT variable_value FROM information_schema.global_status WHERE status_variable_name = 'Innodb_buffer_pool_pages_total'))) * 100 AS buffer_pool_usage_pct,
  (1 - ( Innodb_buffer_pool_pages_free * 1.0 / Innodb_buffer_pool_pages_total)) * 100 AS buffer_hit_rate
FROM information_schema.global_status
WHERE status_variable_name IN ('Innodb_buffer_pool_pages_total', 'Innodb_buffer_pool_pages_free');
"

健康阈值：

  • 缓冲池命中率 > 99%：优秀
  • 95% - 99%：正常
  • 90% - 95%：需要优化
  • < 90%：紧急！检查查询或扩容

示例场景：

-- 假设你的系统出现慢查询，检查缓冲池
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

输出解读：

Innodb_buffer_pool_read_requests    | 100000000   -- 缓冲池读取请求总数
Innodb_buffer_pool_reads            | 500000      -- 物理读取次数

命中率计算：

命中率 = 1 - (物理读取 / 缓冲池读取请求)
       = 1 - (500000 / 100000000)
       = 99.5%  ✅ 健康

如果命中率下降到85%，意味着每100次查询有15次需要磁盘IO。

4.3 慢查询监控

为什么重要：慢查询是性能瓶颈的直接体现。一条慢查询可能阻塞整个数据库。

PMP慢查询采集：

# 使用PMP脚本采集慢查询统计
/usr/share/percona-monitoring-plugins/check_mysql_health \
  --hostname localhost \
  --username pmp \
  --password 'StrongPassword123!' \
  --mode slow_queries

关键指标：

指标 含义 健康值
Slow_queries 慢查询总数 持续增长需关注
Long_running_queries 运行超过1秒的查询 应接近0
Query_response_time_max 最慢查询耗时 < 1秒

慢查询日志分析：

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

优化建议：

  • long_query_time 设置为1秒（默认10秒太松）
  • 开启log_queries_not_using_indexes
  • 使用pt-query-digest分析慢查询日志

4.4 复制延迟监控

为什么重要：对于读写分离架构，主从延迟会导致数据不一致。

监控命令：

# 检查主从延迟
/usr/share/percona-monitoring-plugins/check_replication \
  --hostname localhost \
  --username pmp \
  --password 'StrongPassword123!'

关键指标：

指标 含义 健康值
Seconds_Behind_Master 从库延迟秒数 < 5秒
Slave_SQL_Running SQL线程状态 Yes
Slave_IO_Running IO线程状态 Yes
Last_IO_Error IO线程错误 空

示例场景：

假设从库延迟告警：

SHOW SLAVE STATUS\G

输出：

Seconds_Behind_Master: 120    -- 延迟2分钟！
Slave_SQL_Running: Yes
Last_SQL_Error: Error executing row event: ...

排查步骤：

  1. 检查从库是否有锁等待
  2. 查看是否有大事务阻塞
  3. 检查网络延迟
  4. 使用pt-heartbeat验证真实延迟

五、实战：性能瓶颈诊断全流程

5.1 场景重现：电商大促期间的数据库危机

背景：

  • 系统：日均订单10万，大促期间峰值50万
  • MySQL版本：Percona Server 8.0.32
  • 问题：大促当天，订单查询延迟从200ms暴涨到8秒

5.2 第一步：确认问题范围

# 使用PMP快速检查
/usr/share/percona-monitoring-plugins/check_mysql_health \
  --hostname localhost \
  --username pmp \
  --password 'StrongPassword123!' \
  --mode connection \
  --mode throughput \
  --mode innodb_buffer \
  --mode slow_queries

输出解读：

connection OK - 450 connections (80% of 500) | Threads_running=25
throughput OK - QPS=15000, TPS=3000
innodb_buffer OK - Hit rate=94.5%
slow_queries WARNING - Slow queries growing: 1250/hour

初步判断：连接数接近上限，慢查询激增，缓冲池命中率下降。

5.3 第二步：定位瓶颈

-- 查看详细连接状态
SELECT 
  SUBSTRING_INDEX(host, ':', 1) AS client_ip,
  COUNT(*) AS connection_count
FROM information_schema.processlist
GROUP BY client_ip
ORDER BY connection_count DESC;

输出：

client_ip    | connection_count
------------|----------------
10.0.1.50    | 180     -- 应用服务器A
10.0.1.51    | 175     -- 应用服务器B
10.0.1.52    | 95      -- 应用服务器C
127.0.0.1    | 0       -- 本地连接

发现：应用服务器A和B的 conexiones 数异常高，可能存在连接池泄漏。

5.4 第三步：检查锁等待

-- 查看当前锁等待
SELECT 
  p.id AS process_id,
  p.user,
  p.host,
  p.time AS wait_time,
  t.TABLE_SCHEMA,
  t.TABLE_NAME,
  lock_type,
  lock_mode
FROM information_schema.processlist p
JOIN information_schema.INNODB_LOCKS l ON p.id = l.requesting_trx_id
JOIN information_schema.INNODB_LOCK_WAITS w ON l.lock_id = w.blocked_lock_id
JOIN information_schema.TABLES t ON l.lock_table = t.TABLE_NAME;

输出：

process_id | user      | wait_time | TABLE_SCHEMA | TABLE_NAME | lock_type | lock_mode
-----------|-----------|-----------|--------------|------------|-----------|----------
12345      | app_user  | 45        | ecommerce    | orders     | RECORD    | X
12346      | app_user  | 30        | ecommerce    | orders     | RECORD    | X
12347      | app_user  | 15        | ecommerce    | orders     | RECORD    | X

发现：多条查询在等待orders表的行锁，说明有长事务未提交。

5.5 第四步：找到罪魁祸首

-- 查看长事务
SELECT 
  t.trx_id,
  t.trx_state,
  t.trx_started,
  t.trx_query,
  TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) AS duration_seconds
FROM information_schema.INNODB_TRX t
WHERE t.trx_started < DATE_SUB(NOW(), INTERVAL 30 SECOND);

输出：

trx_id  | trx_state | trx_started         | trx_query                              | duration_seconds
--------|-----------|---------------------|----------------------------------------|-----------------
9876543 | RUNNING   | 2023-12-15 14:23:15 | UPDATE orders SET status='shipped' WHERE order_id IN (...) | 180

发现：一个批量更新订单状态的查询已经运行了3分钟，锁住了大量行。

5.6 第五步：分析查询计划

-- 查看慢查询
SHOW FULL PROCESSLIST;

输出：

Id    | User     | Host         | db         | Command | Time | State                | Info
------|----------|--------------|------------|---------|------|----------------------|------------------------------------
12345 | app_user | 10.0.1.50:54321 | ecommerce | Query   | 180  | Updating             | UPDATE orders SET status='shipped' WHERE order_id IN (1001,1002,...)
12346 | app_user | 10.0.1.51:54322 | ecommerce | Sleep   | 0    |                      | NULL
12347 | app_user | 10.0.1.52:54323 | ecommerce | Query   | 45   | Waiting for lock     | SELECT * FROM orders WHERE order_id = 1001

发现：批量更新查询正在执行，其他查询在等待锁。

-- 分析慢查询的索引使用情况
EXPLAIN UPDATE orders SET status='shipped' WHERE order_id IN (1001,1002,...);

输出：

id | select_type | table  | type | possible_keys | key   | key_len | ref  | rows   | Extra
---|-------------|--------|------|---------------|-------|---------|------|--------|------------------
1  | UPDATE      | orders | ALL  | PRIMARY       | NULL  | NULL    | NULL | 230000 | Using where

问题：全表扫描！没有使用索引。

5.7 第六步：验证索引问题

-- 检查orders表结构
SHOW CREATE TABLE orders\G

输出：

Table: orders
Create Table: CREATE TABLE `orders` (
  `order_id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL,
  `status` varchar(20) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`order_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

问题确认：order_id是主键，但批量更新使用了IN子句，应该使用主键索引。

检查应用代码，发现SQL拼接错误：

-- 错误写法（触发全表扫描）
UPDATE orders SET status='shipped' WHERE order_id IN (1001,1002,...,100000)

-- 正确写法（使用主键）
UPDATE orders SET status='shipped' WHERE order_id = 1001;
UPDATE orders SET status='shipped' WHERE order_id = 1002;
-- 或者分批执行

5.8 第七步：紧急处理

”`sql – 方案1：终止长事务 KILL 1234

-- 展开阅读全文 --