发布于 由admin

如何利用 `MySQL` 的 `Performance Schema` 追踪`函数`和`存储过程`的`性能`？

利用 Performance Schema 追踪 MySQL 函数和存储过程的性能

大家好，今天我们来深入探讨如何利用 MySQL 的 Performance Schema 来追踪函数和存储过程的性能。对于复杂的应用来说，存储过程和函数是提高效率、封装逻辑的重要手段。然而，如果这些存储过程或函数本身存在性能瓶颈，反而会影响整个系统的性能。Performance Schema 提供了一套强大的工具，让我们能够细粒度地分析这些代码的执行情况，找到性能热点，并进行优化。

Performance Schema 简介

Performance Schema 是 MySQL 5.5 版本引入的一个性能监控和分析工具，它通过收集服务器运行时的各种事件信息，并将这些信息存储在内存中的表中，供用户查询和分析。与传统的慢查询日志相比，Performance Schema 提供了更丰富的性能指标，并且对系统性能的影响更小。

启用 Performance Schema

默认情况下，Performance Schema 可能是禁用的。可以通过检查 performance_schema 系统变量来确认其状态：

SHOW VARIABLES LIKE 'performance_schema';

如果 performance_schema 的值为 OFF，则需要修改 MySQL 的配置文件（通常是 my.cnf 或 my.ini）来启用它。在 [mysqld] 部分添加或修改以下行：

performance_schema=ON

修改配置文件后，需要重启 MySQL 服务器才能使更改生效。

配置 Performance Schema 以收集存储过程和函数信息

Performance Schema 默认情况下可能没有收集所有关于存储过程和函数的信息。我们需要检查并修改相关的设置来确保我们能够获取所需的数据。

  1. 检查 setup_instruments 表： 这个表定义了哪些事件会被收集。我们需要确保 statement/sp/% 和 stage/sql/sp/% 这两个 instrument 处于启用状态。

    SELECT * FROM performance_schema.setup_instruments
WHERE NAME LIKE 'statement/sp/%' OR NAME LIKE 'stage/sql/sp/%';

    如果 ENABLED 列的值不是 YES，则需要更新该表：

    UPDATE performance_schema.setup_instruments SET ENABLED = 'YES'
WHERE NAME LIKE 'statement/sp/%' OR NAME LIKE 'stage/sql/sp/%';

  2. 检查 setup_consumers 表： 这个表定义了哪些消费者会使用收集到的事件。我们需要确保 events_statements_current, events_statements_history, events_statements_history_long, events_stages_current, events_stages_history, events_stages_history_long 这些消费者处于启用状态。

    SELECT * FROM performance_schema.setup_consumers
WHERE NAME LIKE '%statements%' OR NAME LIKE '%stages%';

    如果 ENABLED 列的值不是 YES，则需要更新该表：

    UPDATE performance_schema.setup_consumers SET ENABLED = 'YES'
WHERE NAME LIKE '%statements%' OR NAME LIKE '%stages%';

关键的 Performance Schema 表

以下是一些在追踪存储过程和函数性能时常用的 Performance Schema 表：

  • events_statements_current： 包含当前正在执行的语句的信息，包括存储过程和函数的调用。
  • events_statements_history： 包含最近执行的语句的历史记录。这个表的大小有限，由 performance_schema_events_statements_history_size 系统变量控制。
  • events_statements_history_long： 包含更长时间的语句历史记录。这个表的大小也有限，由 performance_schema_events_statements_history_long_size 系统变量控制。
  • events_stages_current： 包含当前正在执行的语句的不同阶段的信息。对于存储过程和函数来说，这可以帮助我们了解哪些阶段消耗了最多的时间。
  • events_stages_history： 包含最近执行的语句的不同阶段的历史记录。
  • events_stages_history_long： 包含更长时间的语句的不同阶段的历史记录。
  • events_errors_summary_global_by_error： 汇总了全局范围内的错误信息，可以帮助我们发现存储过程和函数中可能存在的错误。
  • events_waits_summary_global_by_event_name： 汇总了全局范围内的等待事件信息，可以帮助我们发现存储过程和函数中是否存在锁等待或I/O等待等问题。
  • performance_timers： 这个表定义了用于测量时间的计时器。通常使用 NANOSECOND 计时器。

追踪存储过程性能的示例

假设我们有以下存储过程：

DROP PROCEDURE IF EXISTS `calculate_sum`;
CREATE PROCEDURE `calculate_sum`(IN `input_value` INT, OUT `result` INT)
BEGIN
  DECLARE i INT;
  SET i = 1;
  SET result = 0;

  WHILE i <= input_value DO
    SET result = result + i;
    SET i = i + 1;
  END WHILE;
END;

现在，我们来使用 Performance Schema 追踪这个存储过程的性能。

  1. 执行存储过程： 首先，我们需要执行存储过程几次，以便 Performance Schema 能够收集到相关的信息。

    SET @result = 0;
CALL calculate_sum(1000, @result);
SELECT @result;

SET @result = 0;
CALL calculate_sum(5000, @result);
SELECT @result;

  2. 查询 events_statements_history_long 表： 我们可以查询 events_statements_history_long 表来获取存储过程的执行信息。

    SELECT
    EVENT_NAME,
    SQL_TEXT,
    EXECUTION_TIME,
    LOCK_TIME,
    ERRORS,
    WARNINGS
FROM
    performance_schema.events_statements_history_long
WHERE
    EVENT_NAME LIKE 'statement/sp/%'
    AND SQL_TEXT LIKE '%calculate_sum%'
ORDER BY
    START_TIME DESC
LIMIT 10;

    这个查询会返回 calculate_sum 存储过程的执行次数，总执行时间，锁等待时间，错误数和警告数。SQL_TEXT 列会显示执行的 SQL 语句，EXECUTION_TIME 列显示执行时间（以皮秒为单位），LOCK_TIME 列显示锁等待时间（同样以皮秒为单位）。ERRORS 和 WARNINGS 列显示执行过程中发生的错误和警告的数量。

    EVENT_NAME SQL_TEXT EXECUTION_TIME LOCK_TIME ERRORS WARNINGS
    statement/sp/stmt CALL calculate_sum(1000, @result) 123456789 0 0 0
    statement/sp/stmt CALL calculate_sum(5000, @result) 678901234 0 0 0

  3. 查询 events_stages_history_long 表： 我们可以查询 events_stages_history_long 表来获取存储过程执行过程中各个阶段的信息。

    SELECT
    event_name,
    NESTING_EVENT_ID,
    SOURCE,
    TIMER_WAIT
FROM
    performance_schema.events_stages_history_long
WHERE
    NESTING_EVENT_NAME LIKE 'statement/sp/%'
    AND NESTING_EVENT_ID IN (SELECT event_id FROM performance_schema.events_statements_history_long WHERE SQL_TEXT LIKE '%calculate_sum%')
ORDER BY
    START_TIME DESC;

    这个查询会返回 calculate_sum 存储过程执行过程中各个阶段的名称，执行时间等信息。event_name 列显示阶段名称，TIMER_WAIT 列显示阶段执行时间（以皮秒为单位）。

    event_name NESTING_EVENT_ID SOURCE TIMER_WAIT
    stage/sql/init 123 sql/sp_head.cc:100 12345
    stage/sql/exec 123 sql/sp_instr.cc:200 67890
    stage/sql/end 123 sql/sp_end.cc:300 1234
    stage/sql/init 456 sql/sp_head.cc:100 45678
    stage/sql/exec 456 sql/sp_instr.cc:200 90123
    stage/sql/end 456 sql/sp_end.cc:300 5678

  4. 分析结果： 通过分析 events_statements_history_long 和 events_stages_history_long 表的数据，我们可以了解存储过程的整体执行时间和各个阶段的执行时间。如果发现某个阶段消耗了大量的时间，那么这个阶段就可能是性能瓶颈所在。例如，如果 stage/sql/exec 阶段消耗了大量的时间，那么就应该检查存储过程中的 SQL 语句是否存在性能问题。

追踪函数性能的示例

与存储过程类似，我们也可以使用 Performance Schema 追踪函数的性能。假设我们有以下函数：

DROP FUNCTION IF EXISTS `calculate_factorial`;
CREATE FUNCTION `calculate_factorial`(input_value INT)
RETURNS BIGINT
DETERMINISTIC
BEGIN
  DECLARE result BIGINT;
  DECLARE i INT;
  SET result = 1;
  SET i = 1;
  WHILE i <= input_value DO
    SET result = result * i;
    SET i = i + 1;
  END WHILE;
  RETURN result;
END;

现在，我们来使用 Performance Schema 追踪这个函数的性能。

  1. 执行函数： 首先，我们需要执行函数几次，以便 Performance Schema 能够收集到相关的信息。

    SELECT calculate_factorial(5);
SELECT calculate_factorial(10);

  2. 查询 events_statements_history_long 表： 我们可以查询 events_statements_history_long 表来获取函数的执行信息。

    SELECT
    EVENT_NAME,
    SQL_TEXT,
    EXECUTION_TIME,
    LOCK_TIME,
    ERRORS,
    WARNINGS
FROM
    performance_schema.events_statements_history_long
WHERE
    EVENT_NAME LIKE 'statement/sql/%'
    AND SQL_TEXT LIKE '%calculate_factorial%'
ORDER BY
    START_TIME DESC
LIMIT 10;

    注意，这里 EVENT_NAME 使用 statement/sql/%，因为函数调用通常被视为 SQL 语句。

    EVENT_NAME SQL_TEXT EXECUTION_TIME LOCK_TIME ERRORS WARNINGS
    statement/sql/statement SELECT calculate_factorial(5) 123456789 0 0 0
    statement/sql/statement SELECT calculate_factorial(10) 678901234 0 0 0

  3. 查询 events_stages_history_long 表： 我们可以查询 events_stages_history_long 表来获取函数执行过程中各个阶段的信息。

    SELECT
    event_name,
    NESTING_EVENT_ID,
    SOURCE,
    TIMER_WAIT
FROM
    performance_schema.events_stages_history_long
WHERE
    NESTING_EVENT_NAME LIKE 'statement/sql/%'
    AND NESTING_EVENT_ID IN (SELECT event_id FROM performance_schema.events_statements_history_long WHERE SQL_TEXT LIKE '%calculate_factorial%')
ORDER BY
    START_TIME DESC;

  4. 分析结果： 类似于存储过程，通过分析 events_statements_history_long 和 events_stages_history_long 表的数据，我们可以了解函数的整体执行时间和各个阶段的执行时间，从而找到性能瓶颈。

其他有用的技巧

  • 使用 TRUNCATE TABLE 清空 Performance Schema 表： Performance Schema 表是内存表，会消耗一定的内存。如果需要重新开始收集数据，可以使用 TRUNCATE TABLE 命令清空这些表。例如：

    TRUNCATE TABLE performance_schema.events_statements_current;
TRUNCATE TABLE performance_schema.events_statements_history;
TRUNCATE TABLE performance_schema.events_statements_history_long;
TRUNCATE TABLE performance_schema.events_stages_current;
TRUNCATE TABLE performance_schema.events_stages_history;
TRUNCATE TABLE performance_schema.events_stages_history_long;

  • 调整 Performance Schema 表的大小： 可以通过修改系统变量 performance_schema_events_statements_history_size 和 performance_schema_events_statements_history_long_size 来调整 events_statements_history 和 events_statements_history_long 表的大小。例如：

    SET GLOBAL performance_schema_events_statements_history_size = 100;
SET GLOBAL performance_schema_events_statements_history_long_size = 10000;

  • 结合其他工具： Performance Schema 可以与其他性能分析工具结合使用，例如 pt-query-digest，来更深入地分析存储过程和函数的性能。

注意事项

  • Performance Schema 会消耗一定的系统资源： 启用 Performance Schema 会对系统性能产生一定的影响，尤其是在高并发的情况下。因此，需要根据实际情况进行调整。
  • 注意保护敏感数据： Performance Schema 可能会收集到包含敏感数据的 SQL 语句。需要注意保护这些数据，避免泄露。
  • 不同 MySQL 版本之间 Performance Schema 的实现可能存在差异： 在使用 Performance Schema 时，需要注意不同 MySQL 版本之间的差异，并参考相应的文档。

使用Performance Schema进行性能分析和优化

通过 Performance Schema，可以分析存储过程和函数的执行时间、锁等待、错误信息等，找到性能瓶颈并进行优化。例如，可以优化SQL语句、减少锁竞争、处理潜在的错误等。

对存储过程和函数性能追踪的总结

Performance Schema 是一个强大的工具，可以用于追踪 MySQL 函数和存储过程的性能。通过配置 Performance Schema，我们可以收集到存储过程和函数的执行信息，并使用这些信息来分析性能瓶颈，从而进行优化，提升系统的整体性能。

发表回复

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