[MySQL] MySQL Profiling

■ 프로파일링 방법.

▶︎ SHOW PROFILE 문법

SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]

type: {
    ALL
  | BLOCK IO
  | CONTEXT SWITCHES
  | CPU
  | IPC
  | MEMORY
  | PAGE FAULTS
  | SOURCE
  | SWAPS
}

 

SHOW PROFILE 및 SHOW PROFILES 문은 현재 세션중에서 실행된 명령문의 자원 사용량을 나타내는 프로파일링 정보를 표시합니다.

 

참고사항

SHOW PROFILE 및 SHOW PROFILES 문은 더 이상 사용되지 않으며 향후 MySQL 릴리스에서 제거 될 예정입니다. 대신 PPerformance Schema를 사용합니다. 아래에서 사용방법을 설명합니다.

 

프로파일링을 제어하려면 기본값이 0(OFF) 인 프로파일링 세션 변수를 사용합니다. 프로파일링을 1 또는 ON으로 설정하여 프로파일 링을 활성화합니다.

mysql> SET profiling = 1;

SHOW PROFILES는 서버로 전송된 가장 최근 명령문 목록을 표시합니다. 목록의 크기는 기본값이 15인 profiling_history_size 세션 변수에 의해 제어됩니다. 최대 값은 100입니다. 값을 0으로 설정하면 프로파일링을 비활성화하는 실질적인 효과가 있습니다.

 

SHOW PROFILE 및 SHOW PROFILES를 제외한 모든 명령문은 프로파일링되므로 프로파일 목록에서 해당 명령문을 찾을 수 없습니다. 잘못된 명령문은 프로파일로 작성됩니다. 예를 들어 SHOW PROFILING은 잘못된 명령문이며 실행하려고 하면 구문 오류가 발생하지만 프로파일링 목록에 표시됩니다.

 

SHOW PROFILE은 단일 명령문에 대한 자세한 정보를 표시합니다. FOR QUERY n 절이 없으면 출력은 가장 최근에 실행된 명령문과 관련됩니다. FOR QUERY n이 포함된 경우 SHOW PROFILE은 명령문 n에 대한 정보를 표시합니다. n값은 SHOW PROFILES에 의해 표시되는 Query_ID 값에 해당합니다.

 

LIMIT row_count절은 출력을 row_count 행으로 제한하기 위해 제공될 수 있습니다. LIMIT가 제공되면, OFFSET 명령으로 오프셋을 추가하여 출력 오프셋 행을 전체행 세트로 시작할 수 있습니다.

 

기본적으로 SHOW PROFILE은 상태 및 기간 열을 표시합니다. 상태 값은 SHOW PROCESSLIST에 의해 표시되는 상태 값과 비슷하지만 일부 상태 값에 대한 두 명령문의 해석에는 약간의 차이가있을 수 있습니다.

 

특정 추가 유형 정보를 표시하기 위해 선택적 유형 값을 지정할 수 있습니다.

+ ALL은 모든 정보를 표시합니다

+ BLOCK IO는 블록 입력 및 출력 작업에 대한 카운트를 표시합니다

+ 컨텍스트 스위치는 자발적 및 비자발적 컨텍스트 전환에 대한 카운트를 표시합니다

+ CPU는 사용자 및 시스템 CPU 사용 시간을 표시합니다.

+ IPC는주고받은 메시지의 개수를 표시합니다.

+ MEMORY는 현재 구현되지 않았습니다.

+ PAGE FAULTS (페이지 오류)는 주요 및 사소한 페이지 결함 수를 표시합니다.

+ SOURCE는 함수가 발생한 파일의 이름 및 줄 번호와 함께 소스 코드의 함수 이름을 표시합니다.

+ SWAPS는 스왑 카운트를 표시합니다

 

세션당 프로파일링이 활성화됩니다. 세션이 끝나면 프로파일링 정보가 손실됩니다.

mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE T1 (id INT);
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW PROFILES;
+----------+----------+--------------------------+
| Query_ID | Duration | Query                    |
+----------+----------+--------------------------+
|        0 | 0.000088 | SET PROFILING = 1        |
|        1 | 0.000136 | DROP TABLE IF EXISTS t1  |
|        2 | 0.011947 | CREATE TABLE t1 (id INT) |
+----------+----------+--------------------------+
3 rows in set (0.00 sec)

mysql> SHOW PROFILE;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| checking permissions | 0.000040 |
| creating table       | 0.000056 |
| After create         | 0.011363 |
| query end            | 0.000375 |
| freeing items        | 0.000089 |
| logging slow query   | 0.000019 |
| cleaning up          | 0.000005 |
+----------------------+----------+
7 rows in set (0.00 sec)

mysql> SHOW PROFILE FOR QUERY 1;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| query end          | 0.000107 |
| freeing items      | 0.000008 |
| logging slow query | 0.000015 |
| cleaning up        | 0.000006 |
+--------------------+----------+
4 rows in set (0.00 sec)

mysql> SHOW PROFILE CPU FOR QUERY 2;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| checking permissions | 0.000040 | 0.000038 |   0.000002 |
| creating table       | 0.000056 | 0.000028 |   0.000028 |
| After create         | 0.011363 | 0.000217 |   0.001571 |
| query end            | 0.000375 | 0.000013 |   0.000028 |
| freeing items        | 0.000089 | 0.000010 |   0.000014 |
| logging slow query   | 0.000019 | 0.000009 |   0.000010 |
| cleaning up          | 0.000005 | 0.000003 |   0.000002 |
+----------------------+----------+----------+------------+
7 rows in set (0.00 sec)

 

▶︎노트

프로파일링은 일부 아키텍처에서만 부분적으로 작동합니다. getrusage() 시스템 호출에 의존하는 값의 경우 호출을 지원하지 않는 Windows와 같은 시스템에서는 NULL이 리턴됩니다. 또한 프로파일링은 프로세스 별이며 스레드 별이 아닙니다. 이는 사용자 자신 이외의 서버내 스레드 활동이 사용자가 보는 타이밍 정보에 영향을 줄 수 있음을 의미합니다.

 

INFORMATION_SCHEMA PROFILING 테이블에서 프로파일링 정보를 볼 수도 있습니다. 예를 들면 다음과 같습니다.

mysql> SHOW PROFILE FOR QUERY 2;

mysql> SELECT STATE, FORMAT(DURATION, 6) AS DURATION

FROM INFORMATION_SCHEMA.PROFILING

WHERE QUERY_ID = 2 ORDER BY SEQ;

 

 

■ Query Profiling Using Performance Schema

다음 예제는 성능 스키마 명령문 이벤트 및 스테이지 이벤트를 사용하여 SHOW PROFILES 및 SHOW PROFILE 문에서 제공하는 프로파일링 정보와 유사한 데이터를 검색하는 방법을 보여줍니다.

 

setup_actors 테이블을 사용하여 호스트, 사용자 또는 계정별로 히스토리 이벤트 콜렉션을 제한하여 런타임 오버 헤드 및 히스토리 테이블에서 수집되는 데이터 양을 줄일 수 있습니다. 예제의 첫 번째 단계는 히스토리 이벤트 콜렉션을 특정 사용자로 제한하는 방법을 보여줍니다.

 

성능 스키마는 타이밍 데이터를 표준 단위로 정규화하기 위해 이벤트 타이머 정보를 피코 초(1 초)로 표시합니다. 다음 예에서 TIMER_WAIT 값은 1,000,000,000,000(약 1조)으로 나누어 데이터를 초 단위로 표시합니다. 또한 SHOW PROFILES 및 SHOW PROFILE 문과 같은 형식으로 데이터를 표시하기 위해 소수점 이하 6 자리로 잘립니다.

 

1. 히스토리 이벤트 콜렉션을 조회를 실행할 사용자로 제한합니다. 기본적으로 setup_actors는 모든 포그라운드(foreground) 스레드에 대한 모니터링 및 히스토리 이벤트 콜렉션을 허용하도록 구성됩니다.

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. events_statements_history_long 테이블을 쿼리하여 명령문의 EVENT_ID를 식별합니다. 이 단계는 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 테이블을 쿼리하여 명령문의 스테이지 이벤트를 검색합니다. 스테이지는 이벤트 중첩을 사용하여 명령문에 링크됩니다. 각 단계 이벤트 레코드에는 상위 명령문의 EVENT_ID를 포함하는 NESTING_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 |
+--------------------------------+----------+