发布于 由admin

MySQL的Performance Schema：如何利用它监控和分析存储过程的性能，并找出其中的瓶颈？

MySQL Performance Schema 是一个用于监控 MySQL Server 内部运行情况的底层架构。它收集服务器运行时的各种事件信息，例如语句执行时间、锁的等待时间、I/O 操作等。通过分析这些信息，我们可以深入了解 MySQL Server 的性能瓶颈，并进行优化。对于存储过程的性能监控和分析，Performance Schema 提供了非常强大的支持。

一、Performance Schema 的基本概念和启用

Performance Schema 默认情况下可能并未完全启用。我们需要确认以下几个关键参数：

  • performance_schema: 总开关，必须设置为 ON。
  • performance_schema_instrument: 控制哪些事件被收集。
  • performance_schema_consumer_*: 控制事件数据如何被存储和使用。

要启用 Performance Schema，可以在 MySQL 配置文件 (my.cnf 或 my.ini) 中设置：

[mysqld]
performance_schema=ON
performance_schema_instrument='%=ON' # 开启所有instruments (生产环境慎用，选择需要的)

修改配置文件后，需要重启 MySQL Server。

或者，可以使用 SQL 命令动态设置：

SET GLOBAL performance_schema = ON;
SET GLOBAL performance_schema_instrument = '%=ON'; -- 生产环境慎用

重要提示: 在生产环境中，开启所有 instruments 可能会显著增加服务器的开销。建议只启用与存储过程性能分析相关的 instruments。

二、与存储过程相关的 Performance Schema 表

Performance Schema 提供了多个表来存储与存储过程执行相关的信息。以下是一些重要的表：

  • events_statements_current: 记录当前正在执行的语句的事件信息。
  • events_statements_history: 记录最近执行过的语句的事件信息（会轮询）。
  • events_statements_history_long: 记录更长时间的语句事件信息（需要配置，会轮询）。
  • events_stages_current: 记录当前语句执行阶段的事件信息。
  • events_stages_history: 记录最近语句执行阶段的事件信息（会轮询）。
  • events_stages_history_long: 记录更长时间的语句执行阶段的事件信息（需要配置，会轮询）。
  • events_transactions_current: 记录当前事务的事件信息。
  • events_transactions_history: 记录最近事务的事件信息（会轮询）。
  • events_transactions_history_long: 记录更长时间的事务事件信息（需要配置，会轮询）。
  • events_waits_current: 记录当前等待事件的信息。
  • events_waits_history: 记录最近等待事件的信息（会轮询）。
  • events_waits_history_long: 记录更长时间的等待事件信息（需要配置，会轮询）。
  • setup_instruments: 定义了哪些事件可以被收集。
  • setup_consumers: 定义了事件数据如何被存储。
  • threads: 记录了当前服务器上的线程信息。

这些表包含了执行时间、CPU 使用率、I/O 操作、锁等待等信息，能够帮助我们识别存储过程中的瓶颈。

三、监控存储过程执行时间

可以使用 events_statements_summary_by_program 表来查看存储过程的总体执行统计信息。但是这个表需要先配置，因为默认不会收集。需要修改 setup_instruments 和 setup_consumers 表。

-- 开启存储过程的事件收集
UPDATE setup_instruments SET enabled = 'YES', timed = 'YES' WHERE name LIKE 'statement/%';
UPDATE setup_instruments SET enabled = 'YES', timed = 'YES' WHERE name LIKE 'stage/%';

-- 开启 statements summary by program
UPDATE setup_consumers SET enabled = 'YES' WHERE name LIKE '%statements_summary_by_program%';

FLUSH PERFORMANCE_SCHEMA;  -- 清空Performance Schema缓存，使配置生效

创建测试存储过程：

DROP PROCEDURE IF EXISTS `test_procedure`;
CREATE PROCEDURE `test_procedure`(IN `in_rows` INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < in_rows DO
    INSERT INTO test_table (name) VALUES (CONCAT('Test', i));
    SET i = i + 1;
  END WHILE;
  SELECT SLEEP(0.1); -- 模拟耗时操作
END;

DROP TABLE IF EXISTS `test_table`;
CREATE TABLE `test_table` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL,
  PRIMARY KEY (`id`)
);

调用存储过程：

CALL test_procedure(100);

查询 events_statements_summary_by_program 表：

SELECT
    PROGRAM_NAME,
    COUNT_STAR,
    SUM_TIMER_WAIT,
    AVG_TIMER_WAIT,
    MIN_TIMER_WAIT,
    MAX_TIMER_WAIT
FROM
    events_statements_summary_by_program
WHERE
    PROGRAM_NAME = 'test_procedure';

这个查询会返回存储过程 test_procedure 的执行次数、总执行时间、平均执行时间、最小执行时间和最大执行时间。 SUM_TIMER_WAIT、AVG_TIMER_WAIT、MIN_TIMER_WAIT、MAX_TIMER_WAIT 都是以皮秒为单位。

四、分析存储过程内部的性能瓶颈

仅仅知道存储过程的总体执行时间是不够的，我们需要深入分析存储过程内部的性能瓶颈。Performance Schema 提供了 events_stages_* 表来记录语句执行的各个阶段。

首先，需要确保启用了 stage events 的收集：

UPDATE setup_instruments SET enabled = 'YES' WHERE name LIKE 'stage/%';
FLUSH PERFORMANCE_SCHEMA;

然后，执行存储过程：

CALL test_procedure(100);

查询 events_stages_history_long 表，或者 events_stages_history 表，取决于历史记录的配置：

SELECT
    EVENT_NAME,
    SUM_TIMER_WAIT,
    AVG_TIMER_WAIT,
    COUNT_STAR
FROM
    events_stages_history_long
WHERE
    NESTING_EVENT_NAME LIKE 'statement/sql/call_procedure'
    AND NESTING_EVENT_OBJECT = 'test_procedure'  -- 存储过程名称
ORDER BY SUM_TIMER_WAIT DESC;

这个查询会返回 test_procedure 存储过程执行过程中各个阶段的执行时间和次数。EVENT_NAME 列表示阶段的名称，SUM_TIMER_WAIT 列表示总执行时间，AVG_TIMER_WAIT 列表示平均执行时间，COUNT_STAR 列表示执行次数。通过分析这些数据，我们可以找到存储过程中耗时最多的阶段，从而确定性能瓶颈所在。

五、分析存储过程中的锁等待

锁等待是导致存储过程性能下降的常见原因。Performance Schema 提供了 events_waits_* 表来记录锁等待事件。

首先，确保启用了 wait events 的收集：

UPDATE setup_instruments SET enabled = 'YES', timed = 'YES' WHERE name LIKE 'wait/%';
FLUSH PERFORMANCE_SCHEMA;

然后，执行存储过程：

CALL test_procedure(100);

查询 events_waits_history_long 表，或者 events_waits_history 表：

SELECT
    EVENT_NAME,
    SUM_TIMER_WAIT,
    AVG_TIMER_WAIT,
    COUNT_STAR
FROM
    events_waits_history_long
WHERE
    NESTING_EVENT_NAME LIKE 'statement/sql/call_procedure'
    AND NESTING_EVENT_OBJECT = 'test_procedure'
ORDER BY SUM_TIMER_WAIT DESC;

这个查询会返回 test_procedure 存储过程执行过程中发生的锁等待事件。EVENT_NAME 列表示锁等待事件的名称，SUM_TIMER_WAIT 列表示总等待时间，AVG_TIMER_WAIT 列表示平均等待时间，COUNT_STAR 列表示等待次数。通过分析这些数据，我们可以找到导致存储过程锁等待的原因。 常见的锁等待事件包括：lock/table/sql/handler (表锁), lock/innodb/mutex/ (InnoDB 内部 mutex 锁), lock/innodb/rw_lock/ (InnoDB 读写锁)等。

*六、结合 `eventsstatements` 表进行分析**

events_statements_* 表记录了语句级别的事件信息，可以与 events_stages_* 和 events_waits_* 表结合使用，以更精确地定位性能瓶颈。

执行存储过程：

CALL test_procedure(100);

查询 events_statements_history_long 表：

SELECT
    SQL_TEXT,
    SUM_TIMER_WAIT,
    AVG_TIMER_WAIT,
    COUNT_STAR
FROM
    events_statements_history_long
WHERE
    DIGEST LIKE '%.call_procedure%'
    AND OBJECT_NAME = 'test_procedure'
ORDER BY SUM_TIMER_WAIT DESC;

这个查询会返回存储过程 test_procedure 中执行的 SQL 语句的执行时间和次数。通过分析这些数据，我们可以找到存储过程中执行时间最长的 SQL 语句，然后结合 events_stages_* 和 events_waits_* 表，分析该语句的执行阶段和锁等待情况，从而确定性能瓶颈所在。

七、实际案例分析和优化策略

假设我们通过 Performance Schema 发现 test_procedure 存储过程中 INSERT INTO test_table 语句的执行时间很长，并且存在 lock/table/sql/handler 锁等待事件。这可能意味着表 test_table 存在表锁竞争。

优化策略：

  1. 使用更细粒度的锁： InnoDB 默认使用行锁。确保 test_table 使用 InnoDB 存储引擎，并避免使用 LOCK TABLES 语句显式加表锁。
  2. 减少事务的持有时间： 尽量缩短事务的执行时间，避免长时间持有锁。
  3. 优化 SQL 语句： 检查 INSERT INTO test_table 语句的执行计划，确保使用了索引，避免全表扫描。
  4. 批量插入： 将多个 INSERT 语句合并为一个批量插入语句，减少锁的竞争。
-- 优化后的存储过程
DROP PROCEDURE IF EXISTS `test_procedure_optimized`;
CREATE PROCEDURE `test_procedure_optimized`(IN `in_rows` INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE bulk_insert_values TEXT DEFAULT '';

  WHILE i < in_rows DO
    SET bulk_insert_values = CONCAT(bulk_insert_values, "('Test", i, "'),");
    SET i = i + 1;
  END WHILE;

  -- Remove the trailing comma
  SET bulk_insert_values = LEFT(bulk_insert_values, LENGTH(bulk_insert_values) - 1);

  SET @sql = CONCAT('INSERT INTO test_table (name) VALUES ', bulk_insert_values, ';');
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

  SELECT SLEEP(0.1); -- 模拟耗时操作
END;

八、注意事项

  • Performance Schema 的开销： 启用 Performance Schema 会增加服务器的开销。在生产环境中，需要谨慎选择要启用的 instruments。
  • 数据量： Performance Schema 表中的数据量可能会很大，需要定期清理历史数据。
  • 数据类型： Performance Schema 表中的时间单位通常是皮秒 (picoseconds)。需要进行转换才能得到更易于理解的时间单位。
  • 权限： 访问 Performance Schema 表需要相应的权限。
  • FLUSH PERFORMANCE_SCHEMA 命令： 在修改 setup_instruments 和 setup_consumers 表后，需要执行 FLUSH PERFORMANCE_SCHEMA 命令才能使配置生效。

九、总结

Performance Schema 是 MySQL 性能监控和分析的强大工具。通过分析与存储过程相关的事件信息，我们可以深入了解存储过程的性能瓶颈，并采取相应的优化策略。 关键在于理解各个 Performance Schema 表的含义，正确配置 instruments 和 consumers，以及结合实际案例进行分析。 最终目的是提高存储过程的执行效率，优化数据库性能，提升用户体验。

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注