利用 Performance Schema 分析 MySQL 数据库性能

背景

Performance Schema 是 MySQL 数据库中的一个功能，它用于监控数据库运行时的各种性能参数。通过启用 Performance Schema，你可以收集有关数据库服务器操作的详细信息，比如查询执行时间、索引使用情况、表锁定信息等，这些数据对于理解数据库的性能特性和瓶颈非常有帮助，与传统的慢查询日志和 SHOW PROFILE 等工具相比，Performance Schema 提供了更全面、更细粒度的性能数据，同时对系统性能的影响也更小，可以通过程序的简单处理来统计 SQL 的执行频率和延迟。

Performance Schema 的优势和局限性

优势：

提供全面的性能数据，涵盖事件、对象、会话、内存等多个维度。
相比慢查询和 Show Profile 等工作相比，对系统性能影响更小。
支持实时监控，可及时发现问题。
提供细粒度的性能数据，便于深入分析。

局限性：

需要额外的内存来存储性能数据，所以建议定期清空老信息。
对于某些特定类型的问题（如死锁），可能需要结合其他工具进行分析。

启用和配置 Performance Schema

启用 Performance Schema

Amazon RDS MySQL 如果启用了 Performance Insights，Performance Schema 默认是启用的；如果需要手动启用，可以在参数组修改 performance_schema，检查是否开启了 Performance Schema：

mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
1 row in set (0.00 sec)

监控数据库事件

事件类型介绍

Performance Schema 主要监控以下几类事件：

等待事件（Wait Events）
阶段事件（Stage Events）
语句事件（Statement Events）
事务事件（Transaction Events）

分析慢查询，每一类 SQL（Digest）执行次数以及平均延迟（ram table from db start）

SELECT
        DIGEST_TEXT,
        ROUND(AVG_TIMER_WAIT/1000000000,2) as AVG_LATENCY_MS,
        ROUND(QUANTILE_95/1000000000,2) as P95_LATENCY_MS,
        ROUND(QUANTILE_99/1000000000,2) as P99_LATENCY_MS,
        ROUND(QUANTILE_999 /1000000000,2) as P999_LATENCY_MS
        FROM
        performance_schema.events_statements_summary_by_digest
        where
        SCHEMA_NAME = 'DB_NAME'
        ORDER BY
        COUNT_STAR desc

验证过程

1. 准备测试环境

创建 RDS Aurora MySQL 集群用于测试，记录集群读写 endpoint 以便用于后续测试。

创建一台测试 server，安装必备工具：mysql-cli 客户端及 sysbench 工具。

apt update -y
apt install sysbench mysql-server

2. 准备测试数据

通过 sysbench 工具生成测试数据库及 10 张数据表，每个数据表装填 100w 条测试数据：

# MySQL连接信息
MYSQL_HOST="wirte endpoint"
MYSQL_PORT="3306"
MYSQL_USER="mysql-user"
MYSQL_PASS="password"
MYSQL_DB="mytest"
# Sysbench参数
THREADS=4
TIME=300
TABLE_SIZE=1000000
TABLES=10

# 准备测试数据
sysbench /usr/share/sysbench/oltp_common.lua \
  --mysql-host=$MYSQL_HOST \
  --mysql-port=$MYSQL_PORT \
  --mysql-user=$MYSQL_USER \
  --mysql-password=$MYSQL_PASS \
  --mysql-db=$MYSQL_DB \
  --db-driver=mysql \
  --tables=$TABLES \
  --table-size=$TABLE_SIZE \
  --threads=$THREADS \
  prepare
 
 # DESC Table
 +-------+-----------+------+-----+---------+----------------+
| Field | Type      | Null | Key | Default | Extra          |
+-------+-----------+------+-----+---------+----------------+
| id    | int       | NO   | PRI | NULL    | auto_increment |
| k     | int       | NO   | MUL | 0       |                |
| c     | char(120) | NO   |     |         |                |
| pad   | char(60)  | NO   |     |         |                |
+-------+-----------+------+-----+---------+----------------+
4 rows in set (0.003 sec)

3. 拉取 GitHub 代码，通过 Python 脚本随机生成测试 SQL，涵盖不同的查询方式 in，or，join 等操作组成，模拟不同场景下的 SQL

git clone https://github.com/aws-samples/perf-schema-analyzer-for-mysql.git 
cd perf-schema-analyzer-for-mysql
pip install -r requirements.txt
python3 slow-query-generator.py

示例 SQL：

SELECT * FROM mytest.sbtest3 WHERE id BETWEEN 800 AND 1200 AND k IN (1600, 1700, 1800) LIMIT 100;
SELECT * FROM mytest.sbtest4 JOIN mytest.sbtest5 ON mytest.sbtest4.k = mytest.sbtest5.k JOIN mytest.sbtest6 ON mytest.sbtest5.k = mytest.sbtest6.k WHERE mytest.sbtest4.c LIKE '%pattern%' AND mytest.sbtest5.pad LIKE '%test%' AND mytest.sbtest6.k < 1000 LIMIT 100;
SELECT * FROM mytest.sbtest7 WHERE k IN (SELECT k FROM mytest.sbtest8 WHERE pad LIKE '%data%') OR c LIKE 'prefix%' LIMIT 100;
SELECT * FROM mytest.sbtest9 WHERE id BETWEEN 2000 AND 2500 AND k IN (4000, 5000, 6000) LIMIT 100;
SELECT * FROM mytest.sbtest10 JOIN mytest.sbtest1 ON mytest.sbtest10.id = mytest.sbtest1.id WHERE mytest.sbtest10.k > 1500 OR mytest.sbtest1.c LIKE '%join%' LIMIT 100;
SELECT * FROM mytest.sbtest2 WHERE pad LIKE '%suffix' AND k IN (SELECT k FROM mytest.sbtest3 WHERE c LIKE '%pattern%') LIMIT 100;
SELECT * FROM mytest.sbtest4 WHERE id IN (SELECT id FROM mytest.sbtest5 WHERE k < 2000) AND c LIKE '%test%' LIMIT 100;
SELECT * FROM mytest.sbtest6 JOIN mytest.sbtest7 ON mytest.sbtest6.k = mytest.sbtest7.k WHERE mytest.sbtest6.pad LIKE '%data%' OR mytest.sbtest7.id > 500 LIMIT 100;
SELECT * FROM mytest.sbtest8 WHERE k BETWEEN 500 AND 1500 AND c IN (SELECT c FROM mytest.sbtest9 WHERE k > 1000) LIMIT 100;
SELECT * FROM mytest.sbtest10 WHERE id IN (25, 75, 125) OR pad LIKE 'prefix%' LIMIT 100;
SELECT * FROM mytest.sbtest1 JOIN mytest.sbtest2 ON mytest.sbtest1.id = mytest.sbtest2.id WHERE mytest.sbtest1.k < 3000 AND mytest.sbtest2.c LIKE '%join%' LIMIT 100;

4. 通过 python 程序模拟数据库进行复杂查询

python3 slow-query-execute-query.py --duration 600 
    --host localhost 
    --user your_username 
    --password your_password 
    --database mytest 
    --max_workers 10

5. 查看和统计慢 SQL 语句

该脚本可以统计出 top SQL，以及每个查询的详细分析，包括执行次数、总执行时间、平均执行时间、发送的行数和检查的行数。这些指标是优化查询性能的重要参考依据，可以帮助数据库管理员和开发人员识别需要优化的查询，并了解查询的整体性能特征。

python3 slow-query-digest.py 
    --host localhost 
    --user your_username 
    --password your_password 
    --database mytest  
    --days 3 
    --limit 10

输出 Top SQL 统计：

输出每个 Top SQL 的详细分析，包括执行次数、总执行时间、平均执行时间、发送的行数和检查的行数：

最佳实践和建议

Performance Schema 使用的最佳实践

根据实际需求启用适当的 instruments 和 consumers
定期清理 Performance Schema 表以避免数据过多，TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
使用聚合表（如 summary 表）来减少查询开销
结合其他监控工具（如 Prometheus、Grafana）实现可视化监控

与其他工具的结合使用

使用慢查询日志捕获具体的 SQL 语句
使用 EXPLAIN 分析查询计划
结合系统监控工具（如 top、iostat）分析系统级性能问题

性能优化的通用建议

定期进行索引优化和表优化
合理设置 MySQL 配置参数，如 innodb_buffer_pool_size
优化 SQL 语句，避免全表扫描和复杂 JOIN
实施分区、分表策略以提高大表的查询效率
使用缓存技术（如 Redis）减轻数据库压力

结论

Performance Schema 是 MySQL 中一个强大而全面的性能监控工具，这些指标是优化查询性能的重要参考依据，可以帮助数据库管理员和开发人员识别需要优化的查询，并了解查询的整体性能特征。

通过合理使用 Performance Schema，结合其他工具和最佳实践，我们可以更有效地识别和解决数据库性能问题，从而提升整体系统性能和用户体验。

希望本文能为您的 MySQL 性能优化工作提供有价值的参考和指导。记住，性能优化是一个持续的过程，需要不断地监控、分析和改进。

亚马逊AWS官方博客