正文

MySQL数据库变慢了这6款性能监控工具帮你快速定位慢查询和连接瓶颈

/0 浏览量
0903

MySQL数据库变慢了这6款性能监控工具帮你快速定位慢查询和连接瓶颈

数据库突然变慢，确实是件让人头疼的事。想象一下，你正在写代码，接口请求突然卡住，用户开始抱怨，这时候最需要的就是能迅速找到问题根源的工具。今天聊聊几款我在实际工作中经常使用的MySQL性能监控工具，它们能帮你快速定位慢查询和连接瓶颈。

一、Percona Monitoring and Management (PMM)

这是Percona公司开源的一套完整的监控解决方案，界面友好，功能强大，是很多人首选的工具之一。

1.1 它能监控什么

PMM可以监控MySQL的QPS、TPS、连接数、InnoDB缓冲池命中率、慢查询日志、磁盘IO、CPU使用率等多个维度的数据。

1.2 如何部署

以Ubuntu为例，安装过程相当简单：

# 安装Docker
sudo apt-get update
sudo apt-get install -y docker.io

# 启动PMM服务器
docker run -d \
  --name pmm-server \
  --restart always \
  -v /opt/pmm-data:/srv \
  -p 443:443 \
  percona/pmm-server:2

# 安装PMM客户端
sudo apt-get install -y pmm-client

# 添加MySQL实例到PMM
pmm-admin config --server-insecure-tls --server-url=https://admin:admin@localhost:443
pmm-admin add mysql --user=root --password=your_password

1.3 如何使用PMM定位慢查询

部署完成后，访问PMM的Web界面（通常是https://your-server-ip），可以看到实时的仪表板。

-- 查看当前慢查询日志的配置
SHOW VARIABLES LIKE '%slow%';

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过1秒的查询记录为慢查询

在PMM界面中，切换到”慢查询日志”页面，可以看到所有慢查询的SQL语句、执行时间、扫描行数等详细信息。PMM还会自动对慢查询进行分类统计，让你一眼看出哪些SQL最消耗资源。

二、MySQL慢查询日志（Slow Query Log）

慢查询日志是MySQL自带的功能，不需要额外安装任何东西，是最基础也是最实用的工具。

2.1 开启慢查询日志

-- 查看慢查询日志是否开启以及文件路径
SHOW VARIABLES LIKE 'slow_query_log%';

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';

-- 设置慢查询阈值（单位：秒）
SET GLOBAL long_query_time = 1;

-- 记录没有使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 设置日志文件路径
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

2.2 分析慢查询日志

MySQL官方提供了一个分析工具mysqldumpslow，可以帮你快速找出消耗时间最多的SQL。

# 找出执行时间最长的10条SQL
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 找出扫描行数最多的10条SQL
mysqldumpslow -s s -t 10 /var/log/mysql/slow.log

# 找出返回记录最多的10条SQL
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

参数说明：

  • -s：排序方式（t=时间，l=锁定时间，r=返回记录数，c=计数）
  • -t：取前多少条
  • -g：可以用正则过滤SQL

2.3 实际案例分析

有一次，我们的线上数据库突然变慢，用户反馈页面加载需要好几秒。通过查看慢查询日志，发现了一条问题SQL：

SELECT * FROM orders WHERE create_time > '2024-01-01' ORDER BY create_time DESC;

这条SQL没有走索引，全表扫描了数百万条记录。我们加了复合索引：

ALTER TABLE orders ADD INDEX idx_create_time (create_time);

加完索引后，查询时间从5秒降到了0.02秒。慢查询日志帮我们快速定位了问题，这就是它的价值所在。

三、MySQL Enterprise Monitor（MySQL企业监控）

这是Oracle官方的商业监控工具，功能非常全面，适合对MySQL有深度监控需求的企业用户。

3.1 主要功能

  • 数据库状态监控：实时监控MySQL实例的健康状态
  • SQL监控：捕获和分析慢查询，自动推荐索引
  • 告警系统：支持邮件、短信、Webhook等多种告警方式
  • 性能趋势分析：可以查看历史性能数据，发现趋势性问题

3.2 安装与配置

# 下载MySQL Enterprise Monitor
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-enterprise-monitor-3.5.1-1.el7.x86_64.rpm

# 安装
sudo yum install mysql-enterprise-monitor-3.5.1-1.el7.x86_64.rpm

# 启动服务
sudo /etc/init.d/mysql-em-agent start
sudo /etc/init.d/mysql-em-server start

安装完成后，通过浏览器访问http://your-server:18443即可进入管理界面。

3.3 使用示例

在MySQL Enterprise Monitor中，有一个”慢查询分析”功能非常强大。它会自动收集慢查询，并进行分类、统计和排名。

-- 在MySQL中开启Performance Schema
SET GLOBAL performance_schema = ON;

-- 查看已采集的慢查询
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

这个表记录了每个SQL语句的汇总统计信息，包括执行次数、平均响应时间、锁等待时间等，非常有用。

四、Prometheus + Grafana

这是一套开源的监控解决方案，近年来非常流行，特别适合云原生环境和容器化部署。

4.1 整体架构

MySQL实例
    ↓
mysqld_exporter（采集MySQL指标）
    ↓
Prometheus（存储和查询指标）
    ↓
Grafana（可视化展示）

4.2 安装mysqld_exporter

# 下载mysqld_exporter
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.1/mysqld_exporter-0.15.1.linux-amd64.tar.gz
tar xvf mysqld_exporter-0.15.1.linux-amd64.tar.gz
cd mysqld_exporter-0.15.1.linux-amd64

# 创建MySQL用户供exporter使用
mysql -u root -p <<EOF
CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'password' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';
FLUSH PRIVILEGES;
EOF

# 创建配置文件
cat > .my.cnf <<EOF
[client]
user=exporter
password=password
EOF

# 启动exporter
./mysqld_exporter --config.my-cnf=.my.cnf

4.3 配置Prometheus

# prometheus.yml
global:
  scrape_interval: 15s

scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['localhost:9104']

# 启动Prometheus
./prometheus --config.file=prometheus.yml

4.4 配置Grafana

# 安装Grafana
sudo apt-get install -y apt-transport-https
sudo apt-get install -y software-properties wget
wget -q -O - https://packages.grafana.com/gpg.key | sudo apt-key add -
echo "deb https://packages.grafana.com/oss/deb stable main" | sudo tee /etc/apt/sources.list.d/grafana.list
sudo apt-get update
sudo apt-get install -y grafana

# 启动Grafana
sudo systemctl start grafana-server

启动后访问http://your-server:3000，默认账号密码是admin/admin。

在Grafana中添加Prometheus作为数据源，然后导入MySQL的Dashboard模板（ID通常为8937或8946），就可以看到一个非常详细的MySQL监控面板了。

4.5 关键监控指标

在Grafana面板中，有几个指标特别值得关注：

- MySQL Queries per Second (QPS)：每秒查询数
- MySQL Connections：当前连接数
- MySQL InnoDB Buffer Pool Hit Rate：缓冲池命中率
- MySQL Threads Connected：当前连接线程数
- MySQL Replication Lag：主从复制延迟

五、pt-query-digest（Percona Toolkit）

Percona Toolkit是一组命令行工具，其中pt-query-digest是最强大的慢查询分析工具之一。

5.1 安装

# Ubuntu/Debian
sudo apt-get install -y percona-toolkit

# CentOS/RHEL
sudo yum install -y percona-toolkit

5.2 分析慢查询日志

# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log

# 输出详细报告
pt-query-digest --report /var/log/mysql/slow.log > slow_report.txt

# 按响应时间排序，显示前20条
pt-query-digest --order-by Query_time:sum --limit 20 /var/log/mysql/slow.log

5.3 输出报告解读

# 210 total queries, avg 12.3ms, max 2341.0ms
# 1 queries, avg 2341.0ms, max 2341.0ms
# Select tables optimized: 0

SELECT * FROM orders WHERE create_time > '2024-01-01' ORDER BY create_time DESC;
# Query_time distribution
#   1ms  #
#  10ms  #
# 100ms  ##
#   1s  ##############
#  10s  #

报告中会告诉你：

  • 哪些SQL消耗的时间最多
  • 哪些SQL返回的记录数最多
  • 哪些SQL使用了临时表
  • 哪些SQL进行了全表扫描

5.4 实时分析正在执行的查询

# 实时监控正在执行的查询
pt-query-digest --live

# 分析最近10分钟内的慢查询
pt-query-digest --since 10m /var/log/mysql/slow.log

六、SHOW PROCESSLIST 和 performance_schema

这两个是MySQL自带的工具，不需要安装任何东西，在紧急情况下特别有用。

6.1 SHOW PROCESSLIST

-- 查看所有连接
SHOW FULL PROCESSLIST;

-- 只看当前执行的SQL
SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO
FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep'
ORDER BY TIME DESC;

-- 找出运行时间超过60秒的查询
SELECT * FROM information_schema.PROCESSLIST
WHERE TIME > 60 AND COMMAND != 'Sleep';

SHOW PROCESSLIST可以让你看到当前所有连接的状态，包括正在执行的SQL、已经等待了多长时间、连接来源等。这是排查问题最直接的命令。

6.2 performance_schema

-- 开启performance_schema
SET GLOBAL performance_schema = ON;

-- 查看当前执行的SQL
SELECT * FROM performance_schema.events_statements_current
WHERE TIMER_WAIT IS NOT NULL;

-- 查看各表的访问频率
SELECT * FROM performance_schema.table_io_waits_summary_by_table
ORDER BY COUNT_STAR DESC;

-- 查看各索引的使用情况
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL;

performance_schema是MySQL内置的性能监控引擎，可以记录非常详细的性能数据，包括SQL执行时间、锁等待、IO消耗等。虽然默认不开启，但开启后对性能的影响非常小，值得生产环境启用。

6.3 实际排查场景

有一次，线上MySQL突然连接数暴增，导致新连接无法建立。通过SHOW PROCESSLIST发现大量连接处于”Waiting for table metadata lock”状态：

-- 查看metadata lock等待情况
SELECT * FROM performance_schema.metadata_locks;

-- 找到持有锁的事务
SELECT t.TRX_ID, t.TRX_STATE, t.TRX_STARTED, t.TRX_QUERY
FROM information_schema.INNODB_TRX t;

原来是一个长时间运行的事务没有提交，导致其他查询被阻塞。找到这个事务后，我们杀掉了它，问题立刻解决。

七、连接数监控与优化

连接数是MySQL性能的一个重要指标，连接数过多会导致资源耗尽，过少则可能影响并发能力。

7.1 监控连接数

-- 查看最大连接数配置
SHOW VARIABLES LIKE 'max_connections';

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';

-- 查看当前正在运行的连接数
SHOW STATUS LIKE 'Threads_running';

-- 查看连接数历史趋势（需要开启performance_schema）
SELECT * FROM performance_schema.hosts;

7.2 连接数瓶颈排查

-- 查看各用户的连接数分布
SELECT USER, HOST, COUNT(*) AS connections
FROM information_schema.PROCESSLIST
GROUP BY USER, HOST
ORDER BY connections DESC;

-- 查看等待连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

Max_used_connections表示历史上同时使用的最大连接数，如果这个值接近max_connections，说明连接数配置可能不够。

7.3 优化建议

-- 调整最大连接数
SET GLOBAL max_connections = 500;

-- 调整连接超时时间
SET GLOBAL wait_timeout = 600;
SET GLOBAL interactive_timeout = 600;

同时，在应用层面使用连接池（如HikariCP、Druid）来管理数据库连接，避免频繁创建和销毁连接。

八、慢查询定位的完整流程

当数据库变慢时，我建议按以下步骤排查：

第一步：查看实时状态

SHOW PROCESSLIST;
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';

第二步：检查关键指标

-- 检查缓冲池命中率
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

-- 检查慢查询数
SHOW STATUS LIKE 'Slow_queries';

-- 检查连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

第三步：分析慢查询日志

pt-query-digest /var/log/mysql/slow.log --order-by Query_time:sum --limit 20

第四步：查看执行计划

EXPLAIN SELECT * FROM orders WHERE create_time > '2024-01-01' ORDER BY create_time DESC;

第五步：根据分析结果优化

  • 添加合适的索引
  • 优化SQL语句
  • 调整MySQL配置参数
  • 考虑分库分表

九、工具选择建议

不同的场景适合不同的工具：

场景 推荐工具
快速排查临时问题 SHOW PROCESSLIST + pt-query-digest
长期监控和告警 Prometheus + Grafana
专业DBA深度分析 PMM + pt-query-digest
企业级监控需求 MySQL Enterprise Monitor
云原生环境 Prometheus + Grafana + mysqld_exporter
没有安装权限的环境 自带的慢查询日志 + performance_schema

数据库性能优化是一个持续的过程，不是一蹴而就的。这些工具可以帮你快速定位问题，但更重要的是理解数据库的工作原理，这样才能从根本上解决问题。希望这篇文章能帮你更好地监控和优化MySQL数据库的性能。如果遇到问题，多看看慢查询日志，大部分问题都能从那里找到答案。

-- 展开阅读全文 --