29.19.1 Query Profiling Using Performance Schema

29.19.1 Query Profiling Using Performance Schema

다음 예제는 Performance Schema statement 이벤트와 stage 이벤트를 사용하여 SHOW PROFILES 및 SHOW PROFILE statement가 제공하는 프로파일링 정보에 상응하는 데이터를 조회하는 방법을 보여 줍니다.

setup_actors 테이블은 host, user, account 별로 history 이벤트 수집을 제한하는 데 사용할 수 있으며, 이를 통해 런타임 오버헤드와 history 테이블에 수집되는 데이터 양을 줄일 수 있습니다. 예제의 첫 단계에서는 특정 user로 history 이벤트 수집을 제한하는 방법을 보여 줍니다.

Performance Schema는 타이밍 데이터를 표준 단위로 정규화하기 위해 이벤트 타이머 정보를 피코초(1조분의 1초) 단위로 표시합니다. 다음 예제에서는 TIMER_WAIT 값을 1000000000000으로 나누어 초 단위로 데이터를 표시합니다. 값은 SHOW PROFILES 및 SHOW PROFILE statement와 동일한 형식으로 표시되도록 소수점 여섯 자리까지 잘립니다.

1. Query를 실행하는 user로 history 이벤트 수집을 제한합니다. 기본적으로 setup_actors는 모든 포그라운드 스레드에 대해 모니터링 및 history 이벤트 수집을 허용하도록 구성되어 있습니다:

1mysql> SELECT * FROM performance_schema.setup_actors;
2+------+------+------+---------+---------+
3| HOST | USER | ROLE | ENABLED | HISTORY |
4+------+------+------+---------+---------+
5| %    | %    | %    | YES     | YES     |
6+------+------+------+---------+---------+

기본 row를 setup_actors 테이블에서 업데이트하여 모든 포그라운드 스레드에 대한 history 이벤트 수집 및 모니터링을 비활성화하고, query를 실행하는 user에 대해 모니터링 및 history 이벤트 수집을 활성화하는 새 row를 삽입합니다:

1mysql> UPDATE performance_schema.setup_actors
2          SET ENABLED = 'NO', HISTORY = 'NO'
3          WHERE HOST = '%' AND USER = '%';
4
5mysql> INSERT INTO performance_schema.setup_actors
6          (HOST,USER,ROLE,ENABLED,HISTORY)
7          VALUES('localhost','test_user','%','YES','YES');

setup_actors 테이블의 데이터는 이제 다음과 유사하게 표시되어야 합니다:

1mysql> SELECT * FROM performance_schema.setup_actors;
2+-----------+-----------+------+---------+---------+
3| HOST      | USER      | ROLE | ENABLED | HISTORY |
4+-----------+-----------+------+---------+---------+
5| %         | %         | %    | NO      | NO      |
6| localhost | test_user | %    | YES     | YES     |
7+-----------+-----------+------+---------+---------+

2. 일부 instrument는 기본적으로 이미 활성화되어 있을 수 있으므로, setup_instruments 테이블을 업데이트하여 statement 및 stage 인스트루멘테이션이 활성화되어 있는지 확인합니다.

1mysql> UPDATE performance_schema.setup_instruments
2          SET ENABLED = 'YES', TIMED = 'YES'
3          WHERE NAME LIKE '%statement/%';
4
5mysql> UPDATE performance_schema.setup_instruments
6          SET ENABLED = 'YES', TIMED = 'YES'
7          WHERE NAME LIKE '%stage/%';

3. events_statements_* 및 events_stages_* consumer가 활성화되어 있는지 확인합니다. 일부 consumer는 기본적으로 이미 활성화되어 있을 수 있습니다.

1mysql> UPDATE performance_schema.setup_consumers
2          SET ENABLED = 'YES'
3          WHERE NAME LIKE '%events_statements_%';
4
5mysql> UPDATE performance_schema.setup_consumers
6          SET ENABLED = 'YES'
7          WHERE NAME LIKE '%events_stages_%';

4. 모니터링 중인 user account로, 프로파일링하려는 statement를 실행합니다. 예를 들면 다음과 같습니다:

1mysql> SELECT * FROM employees.employees WHERE emp_no = 10001;
2+--------+------------+------------+-----------+--------+------------+
3| emp_no | birth_date | first_name | last_name | gender | hire_date |
4+--------+------------+------------+-----------+--------+------------+
5|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
6+--------+------------+------------+-----------+--------+------------+

5. events_statements_history_long 테이블을 query하여 statement의 EVENT_ID를 식별합니다. 이 단계는 Query_ID를 식별하기 위해 SHOW PROFILES를 실행하는 것과 유사합니다. 다음 query는 SHOW PROFILES와 유사한 출력을 생성합니다:

1mysql> SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT
2          FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%10001%';
3+----------+----------+--------------------------------------------------------+
4| event_id | duration | sql_text                                               |
5+----------+----------+--------------------------------------------------------+
6|       31 | 0.028310 | SELECT * FROM employees.employees WHERE emp_no = 10001 |
7+----------+----------+--------------------------------------------------------+

6. events_stages_history_long 테이블을 query하여 statement의 stage 이벤트를 조회합니다. Stage는 이벤트 네스팅을 사용하여 statement에 연결됩니다. 각 stage 이벤트 레코드에는 parent statement의 EVENT_ID가 저장된 NESTING_EVENT_ID 컬럼이 있습니다.

1mysql> SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration
2          FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=31;
3+--------------------------------+----------+
4| Stage                          | Duration |
5+--------------------------------+----------+
6| stage/sql/starting             | 0.000080 |
7| stage/sql/checking permissions | 0.000005 |
8| stage/sql/Opening tables       | 0.027759 |
9| stage/sql/init                 | 0.000052 |
10| stage/sql/System lock          | 0.000009 |
11| stage/sql/optimizing           | 0.000006 |
12| stage/sql/statistics           | 0.000082 |
13| stage/sql/preparing            | 0.000008 |
14| stage/sql/executing            | 0.000000 |
15| stage/sql/Sending data         | 0.000017 |
16| stage/sql/end                  | 0.000001 |
17| stage/sql/query end            | 0.000004 |
18| stage/sql/closing tables       | 0.000006 |
19| stage/sql/freeing items        | 0.000272 |
20| stage/sql/cleaning up          | 0.000001 |
21+--------------------------------+----------+