ps_trace_statement_digest()过程
跟踪所有Performance Schema检测以获取特定的语句摘要。
如果在“性能模式”events_statements_summary_by_digest
表中找到感兴趣的声明,请DIGEST
为此过程指定其MD5列值,并指示轮询持续时间和间隔。结果是该时间间隔内该摘要的“性能模式”中跟踪的所有统计信息的报告。
该过程还将尝试EXPLAIN
在该间隔内执行运行时间最长的摘要示例。由于性能架构会截断长SQL_TEXT
值,因此此尝试可能会失败。因此,EXPLAIN
将由于解析错误而失败。
此过程通过操纵sql_log_bin
系统变量的会话值来在执行过程中禁用二进制日志记录。那是一个受限的操作,因此该过程需要足以设置受限会话变量的特权。请参见“系统变量特权”。
参量
in_digest VARCHAR(32)
:要分析的语句摘要标识符。in_runtime INT
:以秒为单位运行分析的时间。in_interval DECIMAL(2,2)
:尝试拍摄快照的时间间隔(以秒为单位)(可以是分数)。in_start_fresh BOOLEAN
:是否在开始之前截断性能模式events_statements_history_long
和events_stages_history_long
表。in_auto_enable BOOLEAN
:是否自动启用所需的使用者。
例
mysql>CALL sys.ps_trace_statement_digest('891ec6860f98ba46d89dd20b0c03652c', 10, 0.1, TRUE, TRUE); +-------------------- + | SUMMARY STATISTICS | +-------------------- + | SUMMARY STATISTICS | +-------------------- + 1 row in set (9.11 sec) +------------ +----------- +----------- +----------- +--------------- +------------ +------------ + | executions | exec_time | lock_time | rows_sent | rows_examined | tmp_tables | full_scans | +------------ +----------- +----------- +----------- +--------------- +------------ +------------ + | 21 | 4.11 ms | 2.00 ms | 0 | 21 | 0 | 0 | +------------ +----------- +----------- +----------- +--------------- +------------ +------------ + 1 row in set (9.11 sec) +------------------------------------------ +------- +----------- + | event_name | count | latency | +------------------------------------------ +------- +----------- + | stage/sql/statistics | 16 | 546.92 us | | stage/sql/freeing items | 18 | 520.11 us | | stage/sql/init | 51 | 466.80 us | ... | stage/sql/cleaning up | 18 | 11.92 us | | stage/sql/executing | 16 | 6.95 us | +------------------------------------------ +------- +----------- + 17 rows in set (9.12 sec) +--------------------------- + | LONGEST RUNNING STATEMENT | +--------------------------- + | LONGEST RUNNING STATEMENT | +--------------------------- + 1 row in set (9.16 sec) +----------- +----------- +----------- +----------- +--------------- +------------ +----------- + | thread_id | exec_time | lock_time | rows_sent | rows_examined | tmp_tables | full_scan | +----------- +----------- +----------- +----------- +--------------- +------------ +----------- + | 166646 | 618.43 us | 1.00 ms | 0 | 1 | 0 | 0 | +----------- +----------- +----------- +----------- +--------------- +------------ +----------- + 1 row in set (9.16 sec) # Truncated for clarity... +----------------------------------------------------------------- + | sql_text | +----------------------------------------------------------------- + | select hibeventhe0_.id as id1382_, hibeventhe0_.createdTime ... | +----------------------------------------------------------------- + 1 row in set (9.17 sec) +------------------------------------------ +----------- + | event_name | latency | +------------------------------------------ +----------- + | stage/sql/init | 8.61 us | | stage/sql/init | 331.07 ns | ... | stage/sql/freeing items | 30.46 us | | stage/sql/cleaning up | 662.13 ns | +------------------------------------------ +----------- + 18 rows in set (9.23 sec) +---- +------------- +-------------- +------- +--------------- +----------- +--------- +------------- +------ +------- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +---- +------------- +-------------- +------- +--------------- +----------- +--------- +------------- +------ +------- + | 1 | SIMPLE | hibeventhe0_ | const | fixedTime | fixedTime | 775 | const,const | 1 | NULL | +---- +------------- +-------------- +------- +--------------- +----------- +--------- +------------- +------ +------- + 1 row in set (9.27 sec) Query OK, 0 rows affected (9.28 sec)