Documentation Home
MySQL 8.0 参考手册  / 第 27 章 MySQL 性能模式  / 27.19 使用性能模式诊断问题  /  22.18.1 使用性能模式进行查询分析

22.18.1 使用性能模式进行查询分析

以下示例演示如何使用 Performance Schema 语句事件和阶段事件来检索SHOW PROFILESSHOW PROFILE语句提供的分析信息相当的数据。

在此示例中,语句和阶段事件数据收集在events_statements_history_longevents_stages_history_long 表中。在具有许多活动前台线程的繁忙服务器上,数据可能会在您能够检索要分析的信息之前从历史表中老化。如果遇到此问题,选项包括:

Performance Schema 以皮秒(万亿分之一秒)为单位显示事件计时器信息,以将计时数据规范化为标准单位。在以下示例中, TIMER_WAIT值除以 1000000000000 以秒为单位显示数据。值也被截断为小数点后 6 位,以便以SHOW PROFILESSHOW PROFILE语句相同的格式显示数据。

  1. 确保通过更新 setup_instruments表启用语句和阶段检测。某些仪器可能已经默认启用。

    mysql> UPDATE performance_schema.setup_instruments
           SET ENABLED = 'YES', TIMED = 'YES'
           WHERE NAME LIKE '%statement/%';
    
    mysql> UPDATE performance_schema.setup_instruments
           SET ENABLED = 'YES', TIMED = 'YES'
           WHERE NAME LIKE '%stage/%';
  2. 确保events_statements_*启用 events_stages_*消费者。一些消费者可能已经默认启用。

    mysql> UPDATE performance_schema.setup_consumers
           SET ENABLED = 'YES'
           WHERE NAME LIKE '%events_statements_%';
    
    mysql> UPDATE performance_schema.setup_consumers
           SET ENABLED = 'YES'
           WHERE NAME LIKE '%events_stages_%';
  3. 运行要分析的语句。例如:

    mysql> SELECT * FROM employees.employees WHERE emp_no = 10001;
    +--------+------------+------------+-----------+--------+------------+
    | emp_no | birth_date | first_name | last_name | gender | hire_date |
    +--------+------------+------------+-----------+--------+------------+
    |  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
    +--------+------------+------------+-----------+--------+------------+
  4. EVENT_ID通过查询 表来 识别语句的所在events_statements_history_long 。这一步类似于运行 SHOW PROFILES识别 Query_ID。以下查询产生类似于的输出SHOW PROFILES

    mysql> SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT
           FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%10001%';
    +----------+----------+--------------------------------------------------------+
    | event_id | duration | sql_text                                               |
    +----------+----------+--------------------------------------------------------+
    |       31 | 0.028310 | SELECT * FROM employees.employees WHERE emp_no = 10001 |
    +----------+----------+--------------------------------------------------------+
  5. 查询 events_stages_history_long 表以检索语句的阶段事件。阶段使用事件嵌套链接到语句。每个阶段事件记录都有一个NESTING_EVENT_ID包含EVENT_ID父语句的列。

    mysql> SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration
           FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=31;
    +--------------------------------+----------+
    | Stage                          | Duration |
    +--------------------------------+----------+
    | stage/sql/starting             | 0.000080 |
    | stage/sql/checking permissions | 0.000005 |
    | stage/sql/Opening tables       | 0.027759 |
    | stage/sql/init                 | 0.000052 |
    | stage/sql/System lock          | 0.000009 |
    | stage/sql/optimizing           | 0.000006 |
    | stage/sql/statistics           | 0.000082 |
    | stage/sql/preparing            | 0.000008 |
    | stage/sql/executing            | 0.000000 |
    | stage/sql/Sending data         | 0.000017 |
    | stage/sql/end                  | 0.000001 |
    | stage/sql/query end            | 0.000004 |
    | stage/sql/closing tables       | 0.000006 |
    | stage/sql/freeing items        | 0.000272 |
    | stage/sql/cleaning up          | 0.000001 |
    +--------------------------------+----------+