MySQL :: MySQL 8.4 参考手册 :: 29.19.1 Query Profiling Using Performance Schema

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 29 MySQL Performance Schema / 29.19 Using the Performance Schema to Diagnose Problems / 29.19.1 Query Profiling Using Performance Schema

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

29.19.1 使用 Performance Schema 进行查询分析29.19.1 Query Profiling Using Performance Schema

下面的示例演示了如何使用 Performance Schema 的语句事件和阶段事件来检索与 SHOW PROFILES 和 SHOW PROFILE 语句提供的分析信息可比的数据。The following example demonstrates how to use Performance Schema statement events and stage events to retrieve data comparable to profiling information provided by SHOW PROFILES and SHOW PROFILE statements.

setup_actors 表可用于按主机、用户或账户限制历史事件的收集，从而减少运行时开销以及历史表中收集的数据量。示例的第一步演示了如何将历史事件的收集限制为特定用户。The setup_actors table can be used to limit the collection of historical events by host, user, or account to reduce runtime overhead and the amount of data collected in history tables. The first step of the example shows how to limit collection of historical events to a specific user.

性能模式（Performance Schema）以皮秒（万亿分之一秒）显示事件计时器信息，以将定时数据规范化为标准单位。在以下示例中，TIMER_WAIT 的值除以 1000000000000，以秒为单位显示数据。数值也会截断到 6 位小数，以与 SHOW PROFILES 和 SHOW PROFILE 语句显示的数据格式一致。Performance Schema displays event timer information in picoseconds (trillionths of a second) to normalize timing data to a standard unit. In the following example, TIMER_WAIT values are divided by 1000000000000 to show data in units of seconds. Values are also truncated to 6 decimal places to display data in the same format as SHOW PROFILES and SHOW PROFILE statements.

将历史事件的收集限制为运行查询的用户。默认情况下，setup_actors 配置为允许对所有前台线程进行监控和历史事件收集：Limit the collection of historical events to the user that runs the query. By default, setup_actors is configured to allow monitoring and historical event collection for all foreground threads:
```
mysql> SELECT * FROM performance_schema.setup_actors;
+------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
| %    | %    | %    | YES     | YES     |
+------+------+------+---------+---------+
```
更新 setup_actors 表中的默认行，以禁用对所有前台线程的历史事件收集和监控，并插入一行新记录以启用对运行查询的用户的监控和历史事件收集：Update the default row in the setup_actors table to disable historical event collection and monitoring for all foreground threads, and insert a new row that enables monitoring and historical event collection for the user that runs the query:
```
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 表中的数据应类似如下：Data in the setup_actors table should now appear similar to the following:
```
mysql> SELECT * FROM performance_schema.setup_actors;
+-----------+-----------+------+---------+---------+
| HOST      | USER      | ROLE | ENABLED | HISTORY |
+-----------+-----------+------+---------+---------+
| %         | %         | %    | NO      | NO      |
| localhost | test_user | %    | YES     | YES     |
+-----------+-----------+------+---------+---------+
```

通过更新 setup_instruments 表，确保启用语句和阶段仪器。一些仪器可能已经默认启用。Ensure that statement and stage instrumentation is enabled by updating the setup_instruments table. Some instruments may already be enabled by default.

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_* 消费者。一些消费者可能已经默认启用。Ensure that events_statements_* and events_stages_* consumers are enabled. Some consumers may already be enabled by default.

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_%';

在您要监控的用户账户下，运行您想要分析的语句。例如：Under the user account you are monitoring, run the statement that you want to profile. For example:

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类似：Identify the EVENT_ID of the statement by querying the events_statements_history_long table. This step is similar to running SHOW PROFILES to identify the Query_ID. The following query produces output similar to 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 |
+----------+----------+--------------------------------------------------------+

查询 events_stages_history_long 表以检索语句的阶段事件。阶段通过事件嵌套与语句关联。每个阶段事件记录都有一个 NESTING_EVENT_ID 列，其中包含父语句的 EVENT_ID。Query the events_stages_history_long table to retrieve the statement's stage events. Stages are linked to statements using event nesting. Each stage event record has a NESTING_EVENT_ID column that contains the EVENT_ID of the parent statement.

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 |
+--------------------------------+----------+

首页上一级下一节