MySQL :: MySQL 8.3 Reference Manual :: 29.19.1 Query Profiling Using Performance Schema

version 8.3

8.0 current
5.7

8.0 Japanese

MySQL 8.3 Reference Manual / ... / Query Profiling Using Performance Schema

29.19.1 使用性能模式查询分析

以下示例演示如何使用性能模式语句事件和阶段事件来检索与 SHOW PROFILES 和 SHOW PROFILE 语句提供的类似配置信息。

可以使用 setup_actors 表来限制历史事件的收集，以减少运行时开销和历史表中的数据量。第一个示例步骤显示如何将历史事件的收集限制到特定用户。

性能模式以皮秒（秒的万亿分之一）为单位显示事件计时器信息，以标准化计时数据。在以下示例中，TIMER_WAIT 值被除以 1000000000000，以显示以秒为单位的数据。值也被截断到 6 位小数，以显示与 SHOW PROFILES 和 SHOW PROFILE 语句相同的格式。

将历史事件的收集限制到运行查询的用户。默认情况下，setup_actors 配置为允许监控和历史事件收集所有前台线程：

Press CTRL+C to copy
 
mysql> SELECT * FROM performance_schema.setup_actors;
+------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
| %    | %    | %    | YES     | YES     |
+------+------+------+---------+---------+

更新 setup_actors 表中的默认行，以禁用所有前台线程的历史事件收集和监控，并插入一个新的行，以启用监控和历史事件收集 для运行查询的用户：

Press CTRL+C to copy
 
mysql> UPDATE performance_schema.setup_actors
       SET ENABLED = 'NO', HISTORY = 'NO'
       WHERE HOST = '%' AND USER = '%';

mysql> INSERT INTO performance_schema.setup_actors
       (HOST,USER,ROLE,ENABLED,HISTORY)
       VALUES('localhost','test_user','%','YES','YES');

现在，setup_actors 表中的数据应该类似于以下内容：

Press CTRL+C to copy
 
mysql> SELECT * FROM performance_schema.setup_actors;
+-----------+-----------+------+---------+---------+
| HOST      | USER      | ROLE | ENABLED | HISTORY |
+-----------+-----------+------+---------+---------+
| %         | %         | %    | NO      | NO      |
| localhost | test_user | %    | YES     | YES     |
+-----------+-----------+------+---------+---------+

确保语句和阶段仪器已经启用，通过更新 setup_instruments 表。一些仪器可能已经默认启用。

Press CTRL+C to copy
 
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/%';

确保 events_statements_* 和 events_stages_* 消费者已经启用。一些消费者可能已经默认启用。

Press CTRL+C to copy
 
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_%';

在要监控的用户帐户下，运行要分析的语句。例如：

Press CTRL+C to copy
 
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 |
+--------+------------+------------+-----------+--------+------------+

通过查询 events_statements_history_long 表，确定语句的 EVENT_ID。这步骤类似于运行 SHOW PROFILES 以确定 Query_ID。以下查询将生成类似于 SHOW PROFILES 的输出：

Press CTRL+C to copy
 
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 |
+----------+----------+--------------------------------------------------------+

查询 events_stages_history_long 表，以检索语句的阶段事件。阶段事件与语句相关联，使用事件嵌套。每个阶段事件记录都有一个 NESTING_EVENT_ID 列，包含父语句的 EVENT_ID。

Press CTRL+C to copy
 
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 |
+--------------------------------+----------+

PREV HOME UP NEXT