以下示例演示如何使用 Performance Schema 语句事件和阶段事件来检索SHOW
PROFILES
与SHOW
PROFILE
语句提供的分析信息相当的数据。
在此示例中,语句和阶段事件数据收集在events_statements_history_long
和events_stages_history_long
表中。在具有许多活动前台线程的繁忙服务器上,数据可能会在您能够检索要分析的信息之前从历史表中老化。如果遇到此问题,选项包括:
在前台线程活动较少的测试实例上运行查询。
通过将表 的
INSTRUMENTED
字段设置为用于其他线程记录来 禁用对其他现有前台线程的检测。例如,以下语句禁用除线程之外的所有前台线程的检测 :threads
NO
test_user
mysql> UPDATE performance_schema.threads SET INSTRUMENTED = 'NO' WHERE TYPE='FOREGROUND' AND PROCESSLIST_USER NOT LIKE 'test_user';
但是,请注意,默认情况下始终会检测新线程。
增加
events_statements_history_long
和events_stages_history_long
表中的行数。和 配置选项默认自动调整大小,但也可以在启动时显式设置performance_schema_events_statements_history_size
。performance_schema_events_stages_history_size
您可以通过运行查看当前设置SHOW VARIABLES
。有关自动调整性能模式参数的信息,请参阅 第 22.3 节,“性能模式启动配置”。
Performance Schema 以皮秒(万亿分之一秒)为单位显示事件计时器信息,以将计时数据规范化为标准单位。在以下示例中,
TIMER_WAIT
值除以 1000000000000 以秒为单位显示数据。值也被截断为小数点后 6 位,以便以SHOW PROFILES
与
SHOW PROFILE
语句相同的格式显示数据。
确保通过更新
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 | +--------------------------------+----------+