• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • 使用性能模式进行查询分析

    以下示例演示了如何使用Performance Schema语句事件和stage事件来检索与SHOW PROFILESSHOW PROFILE语句提供的概要分析信息相当的数据。

    setup_actors表可用于限制主机,用户或帐户对历史事件的收集,以减少运行时开销和历史表中收集的数据量。该示例的第一步显示了如何将历史事件的收集限制为特定用户。

    性能架构以皮秒(万亿分之一秒)为单位显示事件计时器信息,以将计时数据标准化为标准单位。在以下示例中,将TIMER_WAIT值除以1000000000000,以秒为单位显示数据。值也将被截断为小数点后6位,以与SHOW PROFILESand SHOW PROFILE语句相同的格式显示数据。

    1. 将历史事件的收集限制为将运行查询的用户。默认情况下,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	|
      +-----------	+-----------	+------	+---------	+---------	+
      
    2. 通过更新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/%';
      
    3. 确保已启用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_%';
      
    4. 在您要监视的用户帐户下,运行要分析的语句。例如:

      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	|
      +--------	+------------	+------------	+-----------	+--------	+------------	+
      
    5. 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	|
      +----------	+----------	+--------------------------------------------------------	+
      
    6. 查询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	|
      +--------------------------------	+----------	+