27.19 使用Performance Schema诊断问题
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表(事务除外),假设服务器中没有其他查询运行(以便保留历史记录)。