27.19 使用Performance Schema诊断问题

27.19 使用Performance Schema诊断问题

27.19.1 使用性能模式的查询分析

27.19.2 获取父事件信息

Performance Schema 是一种帮助 DBA 进行性能调优的工具，它通过进行实际测量而不是“胡乱猜测”。”本节演示了为此目的使用 Performance Schema 的一些方法。这里的讨论依赖于事件过滤的使用，这在 第 27.4.2 节，“性能模式事件过滤”中进行了描述。

以下示例提供了一种可用于分析可重复问题的方法，例如调查性能瓶颈。首先，您应该有一个可重复的用例，其中性能被认为“太慢”并且需要优化，并且您应该启用所有检测（根本没有预过滤）。

1. 运行用例。
2. 使用 Performance Schema 表，分析性能问题的根本原因。这种分析在很大程度上依赖于后过滤。
3. 对于排除的问题区域，禁用相应的 instruments。例如，如果分析表明问题与特定存储引擎中的文件 I/O 无关，请禁用该引擎的文件 I/O instruments 。然后truncate history 和summary 表以删除以前收集的事件。
4. 重复步骤 1 的过程。

   在每次迭代中，Performance Schema 输出，尤其是 events_waits_history_long表，包含越来越少的由不重要instruments引起的“噪音”，并且鉴于该表具有固定大小，包含越来越多与手头问题分析相关的数据。

   在每次迭代中，随着“信噪比”的提高，调查应该越来越接近问题的根本原因 ，从而使分析变得更加容易。

5. 一旦确定了性能瓶颈的根本原因，就采取适当的纠正措施，例如：
   • 调整服务器参数（缓存大小、内存等）。
   • 通过以不同方式编写查询来调整查询，
   • 调整数据库模式（表、索引等）。
   • 调整代码（这仅适用于存储引擎或服务器开发人员）。
6. 从第 1 步重新开始，以查看更改对性能的影响。

mutex_instances.LOCKED_BY_THREAD_ID与 rwlock_instances.WRITE_LOCKED_BY_THREAD_ID 列对于调查性能瓶颈或死锁非常重要。Performance Schema instrumentation 使这成为可能，如下所示：

1. 假设线程 1 卡在等待互斥锁中。
2. 您可以确定线程正在等待什么：

   SELECT * FROM performance_schema.events_waits_current
   WHERE THREAD_ID = thread_1;

   假设查询结果表明该线程正在等待 mutex A，在 events_waits_current.OBJECT_INSTANCE_BEGIN 中发现.

3. 您可以确定哪个线程持有互斥锁 A：

   SELECT * FROM performance_schema.mutex_instances
   WHERE OBJECT_INSTANCE_BEGIN = mutex_A;
   

   假设查询结果标识它是线程 2 持有互斥锁 A，如 mutex_instances.LOCKED_BY_THREAD_ID.

4. 你可以看到线程 2 在做什么：

   SELECT * FROM performance_schema.events_waits_current
   WHERE THREAD_ID = thread_2;

27.19.1 使用Performance Schema查询Profiling 

以下示例演示了如何使用 Performance Schema statement events 和stage events来检索与由SHOW PROFILES和SHOW PROFILE语句提供的分析信息相当的数据。

setup_actors表可用于限制主机、用户或帐户对历史事件的收集，以减少运行时开销和历史表中收集的数据量。该示例的第一步显示了如何将历史事件的收集限制为特定用户。

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

1. 将历史事件的收集限制为运行查询的用户。默认情况下， 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     |
   +-----------+-----------+------+---------+---------+

2. 通过更新setup_instruments表确保启用statement 和stage 检测 。默认情况下，某些 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/%';

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

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

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

5. 通过查询events_statements_history_long 表来获取EVENT_ID 。此步骤类似于运行 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 |
   +----------+----------+--------------------------------------------------------+

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

27.19.2 获取父事件信息

data_locks表显示了持有和请求的数据锁。其中 THREAD_ID列指示拥有锁的会话的线程 ID，EVENT_ID列指示导致锁的Performance Schema事件。( THREAD_ID, EVENT_ID) 值的元组隐式标识其他 Performance Schema 表中的父事件：

• 父等待事件在 events_waits_xxx
• 父阶段事件在 events_stages_xxx
• 父语句事件在 events_statements_xxx
• 父事务事件在 events_transactions_current 

要获取有关父事件的详细信息，请将 THREAD_ID和EVENT_ID 列与相应父事件表中名称对应的列连接起来。该关系基于嵌套集数据模型，因此连接具有多个子句。连接如下所示：

WHERE
  parent.THREAD_ID = child.THREAD_ID        /* 1 */
  AND parent.EVENT_ID < child.EVENT_ID      /* 2 */
  AND (
    child.EVENT_ID <= parent.END_EVENT_ID   /* 3a */
    OR parent.END_EVENT_ID IS NULL          /* 3b */
  )

加入的条件是：

1. 父事件和子事件在同一个线程中。
2. 子事件在父事件之后开始，所以它的EVENT_ID值大于父事件的值。
3. 父事件已完成或仍在运行。

要查找锁信息， data_locks是包含子事件的表。

该data_locks表仅显示现有锁，因此这些注意事项适用于哪个表包含父事件：

• 对于事务，唯一的选择是 events_transactions_current。如果一个事务完成了，它可能在 transaction history 表中，但锁已经消失了。
• 对于语句，这完全取决于获取锁的语句是已完成事务（use events_statements_history）中的语句还是该语句仍在运行（use events_statements_current）。
• 对于阶段，逻辑与语句类似；使用 events_stages_history或 events_stages_current。
• 对于等待，逻辑与语句类似；使用 events_waits_history或 events_waits_current。然而，记录了如此多的等待，导致锁定的等待很可能已经从历史表中消失了。

等待、阶段和声明事件迅速从历史记录中消失。如果一个很久以前执行的语句拿到了一个锁，但是仍然在一个打开的事务中，它可能无法找到该语句，但可以找到该事务。

这就是嵌套集数据模型更适合定位父事件的原因。当中间节点已经从历史表中消失时，跟踪父/子关系中的链接（数据锁定 -> 父等待 -> 父阶段 -> 父事务）无法正常工作。

以下场景说明了如何查找获取锁的语句的父事务：

会话A：

[1] START TRANSACTION;
[2] SELECT * FROM t1 WHERE pk = 1;
[3] SELECT 'Hello, world';

会话B：

SELECT ...
FROM performance_schema.events_transactions_current AS parent
  INNER JOIN performance_schema.data_locks AS child
WHERE
  parent.THREAD_ID = child.THREAD_ID
  AND parent.EVENT_ID < child.EVENT_ID
  AND (
    child.EVENT_ID <= parent.END_EVENT_ID
    OR parent.END_EVENT_ID IS NULL
  );

会话 B 的查询应该将语句 [2] 显示为拥有记录上的数据锁pk=1。

如果会话 A 执行更多语句，[2] 会从历史表中淡出。

无论执行了多少语句、阶段或等待，查询都应显示在 [1] 中启动的事务。

要查看更多数据，您还可以使用events_xxx_history_long表（事务除外），假设服务器中没有其他查询运行（以便保留历史记录）。 

来源： MySQL :: MySQL 8.0 Reference Manual :: 27.19.2 Obtaining Parent Event Information