从前慢现在也慢

这个屌丝很懒，什么也没留下！

热门标签

27.19 使用Performance Schema诊断问题_select from performance_schema.processlist where s

作者：从前慢现在也慢 | 2024-02-21 08:09:45

踩

select from performance_schema.processlist where state like '%lock%';

27.19 使用Performance Schema诊断问题

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

27.19.2 获取父事件信息

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

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

运行用例。
使用 Performance Schema 表，分析性能问题的根本原因。这种分析在很大程度上依赖于后过滤。
对于排除的问题区域，禁用相应的 instruments。例如，如果分析表明问题与特定存储引擎中的文件 I/O 无关，请禁用该引擎的文件 I/O instruments 。然后truncate history 和summary 表以删除以前收集的事件。
重复步骤 1 的过程。
在每次迭代中，Performance Schema 输出，尤其是 events_waits_history_long表，包含越来越少的由不重要instruments引起的“噪音”，并且鉴于该表具有固定大小，包含越来越多与手头问题分析相关的数据。

在每次迭代中，随着“信噪比”的提高，调查应该越来越接近问题的根本原因，从而使分析变得更加容易。
一旦确定了性能瓶颈的根本原因，就采取适当的纠正措施，例如：
- 调整服务器参数（缓存大小、内存等）。
- 通过以不同方式编写查询来调整查询，
- 调整数据库模式（表、索引等）。
- 调整代码（这仅适用于存储引擎或服务器开发人员）。
从第 1 步重新开始，以查看更改对性能的影响。

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

假设线程 1 卡在等待互斥锁中。
您可以确定线程正在等待什么：
```
SELECT * FROM performance_schema.events_waits_current
WHERE THREAD_ID = thread_1;
```
假设查询结果表明该线程正在等待 mutex A，在 events_waits_current.OBJECT_INSTANCE_BEGIN 中发现.
您可以确定哪个线程持有互斥锁 A：
```
SELECT * FROM performance_schema.mutex_instances
WHERE OBJECT_INSTANCE_BEGIN = mutex_A;
```
假设查询结果标识它是线程 2 持有互斥锁 A，如 mutex_instances.LOCKED_BY_THREAD_ID.

你可以看到线程 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语句相同的格式显示数据。

将历史事件的收集限制为运行查询的用户。默认情况下， 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表确保启用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/%';

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

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

查询 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 */
  )

加入的条件是：

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

要查找锁信息， 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

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/从前慢现在也慢/article/detail/121862