使用性能模式进行查询分析
以下示例演示了如何使用Performance Schema语句事件和stage事件来检索与SHOW PROFILES
和SHOW PROFILE
语句提供的概要分析信息相当的数据。
该setup_actors
表可用于限制主机,用户或帐户对历史事件的收集,以减少运行时开销和历史表中收集的数据量。该示例的第一步显示了如何将历史事件的收集限制为特定用户。
性能架构以皮秒(万亿分之一秒)为单位显示事件计时器信息,以将计时数据标准化为标准单位。在以下示例中,将TIMER_WAIT
值除以1000000000000,以秒为单位显示数据。值也将被截断为小数点后6位,以与SHOW PROFILES
and 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_actorsSET ENABLED = 'NO',HISTORY = 'NO'WHERE HOST = '%' ANDUSER = '%'; 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
表,确保已启用语句和阶段检测。默认情况下,某些仪器可能已启用。mysql>
UPDATE performance_schema.setup_instrumentsSET ENABLED = 'YES', TIMED = 'YES'WHERE NAME LIKE '%statement/%'; mysql>UPDATE performance_schema.setup_instrumentsSET ENABLED = 'YES', TIMED = 'YES'WHERE NAME LIKE '%stage/%';确保已启用
events_statements_*
和events_stages_*
使用者。默认情况下,某些使用者可能已启用。mysql>
UPDATE performance_schema.setup_consumersSET ENABLED = 'YES'WHERE NAME LIKE '%events_statements_%'; mysql>UPDATE performance_schema.setup_consumersSET ENABLED = 'YES'WHERE NAME LIKE '%events_stages_%';在您要监视的用户帐户下,运行要分析的语句。例如:
mysql>
SELECT *FROM employees.employeesWHERE 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_TEXTFROM performance_schema.events_statements_history_longWHERE 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_nameAS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6)AS DurationFROM performance_schema.events_stages_history_longWHERE 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 | +-------------------------------- +---------- +