query profiling하는 법 (OLD: SHOW PROFILES)

일단 우선 엑터를 확인하자.

mysql>  SELECT * FROM performance_schema.setup_actors;
+------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
| %    | %    | %    | NO      | NO      |
+------+------+------+---------+---------+
1 row in set (0.00 sec)

 

이 것을 활성화 해야 한다.

 

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/%';
       
       
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_%';

 

그럼 준비 끝.

 

이제 프로파일링하고 싶은 쿼리를 실행한다.

select * from junitest.card_info

 

이 쿼리의 event_id를 찾아야한다 (like문에 관련 키워드를 넣는다.)

 

mysql> SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT \
       FROM performance_schema.events_statements_history_long \
       WHERE SQL_TEXT like '%card_info%';
    
    
    
+----------+----------+------------------------------------+
| EVENT_ID | Duration | SQL_TEXT                           |
+----------+----------+------------------------------------+
|      147 |   0.0013 | select * from junitest.card_info   |
|       28 |   0.0021 | show index from junitest.card_info |
+----------+----------+------------------------------------+
2 rows in set (0.00 sec)

 

여기서 147번을 선택하고 다음의 쿼리를 실행한다.

 

mysql> SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration \
       FROM performance_schema.events_stages_history_long  \ 
       WHERE NESTING_EVENT_ID=147;
    
    
    
    
    
+------------------------------------------------+----------+
| Stage                                          | Duration |
+------------------------------------------------+----------+
| stage/sql/starting                             |   0.0000 |
| stage/sql/Executing hook on transaction begin. |   0.0000 |
| stage/sql/starting                             |   0.0000 |
| stage/sql/checking permissions                 |   0.0000 |
| stage/sql/Opening tables                       |   0.0000 |
| stage/sql/init                                 |   0.0000 |
| stage/sql/System lock                          |   0.0000 |
| stage/sql/optimizing                           |   0.0000 |
| stage/sql/statistics                           |   0.0000 |
| stage/sql/preparing                            |   0.0000 |
| stage/sql/executing                            |   0.0001 |
| stage/sql/end                                  |   0.0000 |
| stage/sql/query end                            |   0.0000 |
| stage/sql/waiting for handler commit           |   0.0000 |
| stage/sql/closing tables                       |   0.0000 |
| stage/sql/freeing items                        |   0.0010 |
| stage/sql/cleaning up                          |   0.0000 |
+------------------------------------------------+----------+
17 rows in set (0.00 sec)

잘 분석한다!!