正文

某银行数据库卡顿排查实录从慢查询日志到实时监控工具全解析MySQL性能监控利器有哪些

/0 浏览量
0902

上周三下午三点，正值银行核心业务的高峰时段，监控大屏上的数据库响应时间突然从平时的20毫秒飙升至800毫秒，紧接着是投诉电话开始此起彼伏——网银转账失败、手机银行加载圈转个不停。作为负责MySQL数据库运维的一员，我瞬间意识到：这是典型的性能雪崩前兆。

这次排查经历让我深刻体会到，MySQL性能监控不是单一工具能搞定的事情，它需要一个完整的”感知-诊断-定位-解决”闭环体系。今天就把这次实战经验和积累的性能监控利器梳理给大家，希望能帮大家在遇到类似问题时少走弯路。

慢查询日志：最先发出的”警报信号”

排查任何问题，第一步都是收集证据。在MySQL中，慢查询日志就是我们最重要的”黑匣子”。

很多DBA对慢查询日志的理解停留在开启它、设置阈值这个层面，但实际上，慢查询日志的价值远不止于此。在这次银行卡顿事件中，我们首先确认了慢查询日志是否正常工作。登录数据库执行：

SHOW VARIABLES LIKE 'slow_query_log%';

输出结果显示：

+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| slow_query_log      | ON                              |
| slow_query_log_file | /var/lib/mysql/db-slow.log      |
| long_query_time     | 1                               |
| log_queries_not_using_indexes | OFF                   |
+---------------------+---------------------------------+

这里有个关键细节：log_queries_not_using_indexes默认是关闭的。在生产环境中，我强烈建议开启这个参数，因为不使用索引的查询往往是性能杀手。开启方法是在my.cnf中配置：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/db-slow.log
long_query_time = 0.5  # 银行系统要求更严格，设为0.5秒
log_queries_not_using_indexes = 1

修改配置后需要重启MySQL服务，或者动态修改（部分参数支持）：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = ON;

等了一段时间后，我们导出了慢查询日志进行分析。这里推荐使用mysqldumpslow工具，它能帮你快速聚合相似的慢查询：

# 按查询次数排序，查看最频繁执行的慢查询
mysqldumpslow -s c -t 10 /var/lib/mysql/db-slow.log

# 按查询时间排序，查看最耗时的慢查询
mysqldumpslow -s t -t 10 /var/lib/mysql/db-slow.log

# 按锁定时间排序，查看锁等待严重的查询
mysqldumpslow -s l -t 10 /var/lib/mysql/db-slow.log

输出结果让我们发现了一个规律：大部分慢查询都集中在一张名为customer_transaction的交易表上，且都是类似这样的查询模式：

SELECT * FROM customer_transaction 
WHERE customer_id = 123456 
AND transaction_time BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY transaction_time DESC
LIMIT 100;

乍一看这个查询没什么问题，有索引、有分页。但当我们执行EXPLAIN分析时，发现了猫腻：

EXPLAIN SELECT * FROM customer_transaction 
WHERE customer_id = 123456 
AND transaction_time BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY transaction_time DESC
LIMIT 100\G

输出关键信息：

id: 1
select_type: SIMPLE
table: customer_transaction
type: range
possible_keys: idx_customer_time
key: idx_customer_time
key_len: 9
ref: NULL
rows: 150000  # 扫描了15万行！
Extra: Using where; Using filesort

问题出在Using filesort上。虽然用到了索引，但MySQL还需要额外的排序操作。当并发量上来时，这种filesort会消耗大量CPU和IO资源。我们给这个查询加了一个覆盖索引优化：

-- 原索引可能只有(customer_id, transaction_time)
-- 优化为包含所有查询字段的覆盖索引
ALTER TABLE customer_transaction 
ADD INDEX idx_cover(customer_id, transaction_time, amount, status);

优化后，EXPLAIN结果显示Extra变成了Using index，说明MySQL可以直接从索引中获取所有需要的数据，不需要回表查询，性能提升了近10倍。

Performance Schema：MySQL内置的”体检中心”

慢查询日志告诉我们”谁生病了”，但Performance Schema能告诉我们”为什么生病”。这是MySQL 5.5引入、5.7和8.0不断完善的性能监控框架，很多时候被DBA忽视，但实际上它非常强大。

默认情况下，Performance Schema的很多收集器是关闭的。我们先看看当前状态：

-- 查看Performance Schema是否启用
SHOW VARIABLES LIKE 'performance_schema%';

-- 查看已启用的消费者（consumers）
SELECT * FROM setup_consumers;

-- 查看仪器（instruments）的状态
SELECT COUNT(*) FROM setup_instruments WHERE ENABLED = 'YES';

在这次排查中，我们重点开启了几个关键的收集器：

-- 开启事件统计
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE '%statement%';

UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE '%stage%';

UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE '%wait%';

开启后，我们可以通过以下视图查看性能数据：

-- 查看耗时最长的SQL事件（最近1小时）
SELECT 
    DIGEST_TEXT AS sql_template,
    COUNT_STAR AS exec_count,
    ROUND(AVG_TIMER_WAIT/1000000000000, 2) AS avg_latency_ms,
    ROUND(MAX_TIMER_WAIT/1000000000000, 2) AS max_latency_ms,
    SUM_LOCK_TIME/1000000000000 AS total_lock_time_s,
    SUM_ROWS_EXAMINED AS total_rows_examined
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

这个查询的输出让我们看到了一个关键问题：有一条批量更新语句，平均执行时间达到2.3秒，锁等待时间累计超过50秒。这条语句是这样的：

UPDATE account_balance 
SET balance = balance + ?, 
    last_update_time = NOW()
WHERE account_id IN (SELECT account_id FROM temp_batch_process);

问题在于子查询返回了5000条记录，导致主表被锁定了很长时间。通过Performance Schema的events_statements_current视图，我们还能看到当前正在执行的语句及其状态：

SELECT 
    EVENT_ID,
    DIGEST_TEXT,
    TIMER_START,
    TIMER_END,
    TIMER_WAIT/1000000000000 AS wait_ms,
    LOCK_TIME/1000000000000 AS lock_time_ms,
    ROWS_AFFECTED,
    ROWS_SENT,
    CURRENT_SCHEMA
FROM performance_schema.events_statements_current
WHERE TIMER_WAIT > 1000000000  -- 执行时间超过1秒的语句
ORDER BY TIMER_WAIT DESC;

这个功能特别有用，能让你实时看到”谁在占用数据库资源”。

Sys Schema：Performance Schema的”翻译官”

直接查询Performance Schema的原始表，SQL语句复杂且难以理解。MySQL 5.7+提供的sys schema就是对Performance Schema数据的友好封装。

首先确保sys schema已安装：

SHOW DATABASES LIKE 'sys%';
-- 如果没有，执行安装脚本
SOURCE /usr/share/mysql/mysql_sys_schema.sql;

安装后，sys schema提供了一系列非常有用的视图。以下是我们在排查中常用的几个：

-- 1. 查看当前正在执行的语句（带详细等待信息）
SELECT * FROM sys.session;

-- 2. 查看全库最耗时的SQL语句
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile 
LIMIT 10;

-- 3. 查看全库排序最多的SQL（filesort）
SELECT * FROM sys.statements_with_sorting 
ORDER BY sorting_rows DESC 
LIMIT 10;

-- 4. 查看全库全表扫描的SQL
SELECT * FROM sys.statements_with_full_table_scans 
LIMIT 10;

-- 5. 查看全库使用临时表的SQL
SELECT * FROM sys.statements_with_temp_tables 
LIMIT 10;

其中，sys.session视图特别实用，它能显示当前每个连接的详细信息，包括：

  • 当前执行的语句
  • 语句执行时间
  • 锁等待情况
  • 事务状态
  • 连接的客户端信息

输出示例：

user: app_user
current_statement: UPDATE account_balance SET balance = balance + 100 WHERE account_id = 78945
last_statement: SELECT * FROM customer_transaction WHERE customer_id = 123456
statement_latency: 2.34s
progress: NULL
lock_latency: 1.23s
rows_examined: 0
rows_sent: 0
rows_affected: 0
tmp_tables: 0
tmp_disk_tables: 0
in_transaction: YES

通过这些数据，我们可以快速定位到那个正在执行批量更新、且锁等待时间很长的会话，然后决定是优化语句还是kill掉这个连接。

实时性能监控工具：从Percona PMM到Prometheus

日志和视图分析是事后追溯，而实时监控能让你在问题发生时就发现异常。在实际生产环境中，我们部署了一套完整的监控体系。

Percona Monitoring and Management (PMM)

PMM是Percona开发的开源MySQL监控解决方案，基于Prometheus和Grafana，提供非常详细的MySQL性能指标。

安装PMM Server（以Docker方式为例）：

docker run -d \
  --name pmm-server \
  --restart always \
  -p 80:80 \
  -v /opt/prometheus/data:/opt/prometheus/data \
  -v /opt/graphics-cache:/opt/graphics-cache \
  -v /socat/socket:/var/lib/docker/socat-graphite \
  percona/pmm-server:2

然后在MySQL服务器上安装PMM Client：

# Ubuntu/Debian
apt-get install -y pmm2-client

# CentOS/RHEL
yum install -y pmm2-client

添加MySQL实例到监控：

pmm-admin add mysql:metrics \
  --user=root \
  --password=your_password \
  --host=127.0.0.1 \
  --port=3306

PMM提供的关键监控指标包括：

  • QPS/TPS趋势图：每秒查询数和事务数的变化
  • 连接数监控：当前连接数、最大连接数使用率
  • InnoDB缓冲池命中率：理想值应大于95%
  • 慢查询速率：每秒慢查询数量
  • 死锁频率：每分钟死锁发生次数
  • 复制延迟：主从同步的秒级延迟

其中，InnoDB缓冲池命中率是一个非常重要的指标。计算公式是：

命中率 = (1 - (INNODB_BUFFER_POOL_READS / INNODB_BUFFER_POOL_READ_REQUESTS)) * 100%

在PMM中可以直接查看这个指标的实时曲线。如果命中率低于90%，说明需要增大innodb_buffer_pool_size。我们银行的数据库这个参数配置如下：

[mysqld]
innodb_buffer_pool_size = 16G  # 物理内存的70%
innodb_buffer_pool_instances = 8

Prometheus + Grafana：自定义监控

除了PMM，我们还使用Prometheus采集自定义指标。在MySQL服务器上部署mysqld_exporter：

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

# 创建监控用户
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

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

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

然后在Prometheus配置中添加job：

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

通过Grafana导入MySQL监控面板（ID: 7362），可以看到非常详尽的指标。我们特别关注以下几个关键指标：

1. 活跃连接数（MySQL.Global_status_threads_connected） 这个指标应该稳定在合理范围。如果突然飙升，可能是应用层出现了连接泄漏。我们曾经遇到过这样的问题：

// 错误的连接使用方式
Connection conn = DriverManager.getConnection(url, user, password);
// 业务逻辑...
// 忘记关闭连接，导致连接数持续增长

正确的做法应该使用连接池，并确保在finally块中关闭连接：

try (Connection conn = dataSource.getConnection()) {
    // 业务逻辑
} catch (SQLException e) {
    logger.error("数据库操作失败", e);
}

2. 等待IO的线程数（MySQL.Global_status_threads_running + MySQL.Global_status_threads_waiting） 如果threads_waiting持续大于0，说明数据库在等待IO。这可能是磁盘性能瓶颈，也可能是锁竞争导致的。

3. 复制延迟（MySQL_slave_status_sec_behind_master） 对于银行系统，主从复制延迟必须控制在秒级以内。我们设置了告警阈值：当延迟超过5秒时触发P2告警。

MySQL Enterprise Monitor：企业级监控

如果预算允许，MySQL官方提供的Enterprise Monitor是非常专业的选择。它提供了：

  • 基于真实业务场景的性能基线（Baseline）
  • 自动检测异常查询模式
  • 详细的SQL执行计划历史
  • 与MySQL Workbench的深度集成

锁监控：被忽视的性能杀手

在银行系统中，锁问题是最常见的性能瓶颈之一。我们使用以下SQL实时监控锁状态：

-- 查看当前锁等待情况
SELECT 
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

这个查询能告诉我们：哪个事务在等待，哪个事务在阻塞它，以及各自在做什么操作。

另一个重要的视图是performance_schema.data_locks（MySQL 8.0+）：

SELECT 
    ENGINE_TRANSACTION_ID,
    ENGINE_LOCK_ID,
    LOCK_TYPE,
    LOCK_STATUS,
    LOCK_MODE,
    TABLE_SCHEMA,
    TABLE_NAME,
    INDEX_NAME,
    OBJECT_INSTANCE_BEGIN
FROM performance_schema.data_locks
WHERE LOCK_STATUS = 'GRANTED' 
ORDER BY ENGINE_TRANSACTION_ID;

这个视图能显示每个事务持有的所有锁，帮助我们分析锁粒度是否合理。

索引效率分析：用数据说话

索引是MySQL性能优化的核心，但索引也不是越多越好。我们定期运行以下查询来评估索引效率：

-- 查看每张表的索引使用情况
SELECT 
    table_schema AS database_name,
    table_name,
    index_name,
    cardinality,
    seq_in_index,
    column_name
FROM information_schema.statistics
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
ORDER BY table_schema, table_name, index_name, seq_in_index;

更实用的方式是使用sys.schema_unused_indexes视图：

SELECT * FROM sys.schema_unused_indexes 
WHERE db NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');

未使用的索引不仅占用存储空间，还会降低写入性能。每次INSERT、UPDATE、DELETE操作，MySQL都需要更新所有相关索引。

另外，我们还可以分析索引的选择性：

-- 计算索引列的选择性
SELECT 
    table_name,
    column_name,
    cardinality,
    ROUND(cardinality / table_rows * 100, 2) AS selectivity_percent
FROM information_schema.statistics
WHERE table_schema = 'bank_db'
AND table_name = 'customer_transaction'
ORDER BY table_name, column_name;

选择性越高（接近100%），索引效果越好。如果某个索引列的选择性很低（比如只有2%），那么这个索引可能不会被优化器选用。

实时诊断工具：pt-query-digest和mysqldumpslow的进阶使用

除了之前提到的慢查询分析工具，Percona Toolkit提供了一些更强大的诊断工具。

pt-query-digest：慢查询日志的瑞士军刀

”`bash

分析慢查询日志，生成详细报告

pt-query-digest /var/lib/mysql/db-s

-- 展开阅读全文 --