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

version 8.4

8.0 current
5.7

8.0 Japanese

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

29.19.1 使用 Performance Schema 进行查询分析

以下示例演示了如何使用 Performance Schema 语句事件和阶段事件来检索与SHOW PROFILES 和 SHOW PROFILE 语句提供的配置文件信息类似的数据。

可以使用setup_actors 表来限制历史事件的收集，根据主机、用户或账户进行限制，以减少运行时开销和收集到的历史数据量。示例中的第一步展示了如何对特定用户的历史事件收集进行限制。

Performance Schema 以-picoseconds（十亿分之一秒）为单位显示事件计时器信息，以便将计时数据标准化到一个统一的单位。在以下示例中，TIMER_WAIT 值被除以1000000000000来以秒为单位显示数据。值还被截断到6位小数，以与SHOW PROFILES 和 SHOW PROFILE 语句提供的格式相同。

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

mysql> SELECT * FROM performance_schema.setup_actors;
+------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
| %    | %    | %    | YES     | YES     |
+------+------+------+---------+---------+

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

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 表中的数据应该现在看起来与以下类似：

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

确保语句和阶段的性能计数器已启用，通过更新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/%';

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

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

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

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

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

PREV HOME UP NEXT