[MySQL] events_statements_summary_by_digest를 이용하여 SQL성능 분석

performance schema 스키마 테이블중 events_statements_summary_by_digest 수행된 쿼리에 대해 실행 이력을 보관하는 테이블입니다. 쿼리가 처음 실행된 날짜부터 마지막으로 수행된 날짜, 테이블 Full scan수행여부, 인덱스 사용여부, 에러횟수, 그리고 수행된 쿼리등등을 볼수 있는 아주 유용한 테이블입니다.

테이블에 대해서 유용하게 조회해 볼수 있는 성능 조회 쿼리에 대해 알아봅니다.

 

활성화 방법

1. performance schema 스키마 활성화
mysql> show variables like 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
1 row in set (0.01 sec)

 

해당 파라미터가 on 되어 있지 않으면 my.cnf 아래와 같이 설정합니다.

shell> vi /etc/my.cnf

 

[mysqld]

performance_schema = ON

 

기본적으로 events_statements_summary_by_digest테이블의 데이터는 performance schema 환경을 수정하지 않으면 수집됩니다.

 

주요 컬럼 설명

DIGEST : 성능 스키마 다이제스트 . 레코드는 SCHEMA NAME 함께 요약됩니다 .

DIGEST TEXT : 해시되지 않은 형태의 다이제스트.

COUNT_STAR : 요약 이벤트

SUM_TIMER_WAIT : 시간이 지정된 요약 이벤트의 대기 시간(단위 - 피코초(1/1조초(1,000,000,000,000)

MIN_TIMER_WAIT : 시간이 지정된 요약 이벤트의 최소 대기 시간(단위 - 피코초(1/1조초(1,000,000,000,000)

AVG_TIMER_WAIT : 시간이 지정된 요약 이벤트의 평균 대기 시간(단위 - 피코초(1/1조초(1,000,000,000,000)

MAX_TIMER_WAIT : 시간이 지정된 요약 이벤트의 최대 대기 시간(단위 - 피코초(1/1조초(1,000,000,000,000)

SUM_NO_INDEX_USED : events_statements_current 테이블에서 NO_INDEX_USED컬럼의 . 쿼리에서 어떤 인덱스도 사용하지 않은 횟수. 인덱스 생성 고려 필요.

SUM_NO_GOOD_INDEX_USED : events_statements_current 테이블에서 NO_GOOD_INDEX_USED컬럼의 . 서버에 존재하는 기존 인덱스중 사용할 어떤 인덱스도 찾지 못한 횟수. 횟수가 증가하면 인덱스 점검 필요

 

주요 분석 방법

쿼리 분석방법으로, 지연 시간이 가장 쿼리로 정렬합니다. Full Scan 쿼리는 *() 표기됩니다. 수행 쿼리 분석합니다.

SELECT IF(LENGTH(DIGEST_TEXT) > 64, CONCAT(LEFT(DIGEST_TEXT, 30), ' ... ', RIGHT(DIGEST_TEXT, 30)), DIGEST_TEXT) AS query,
       IF(SUM_NO_GOOD_INDEX_USED > 0 OR SUM_NO_INDEX_USED > 0, '*', '') AS full_scan,
       COUNT_STAR AS exec_count,
       SUM_ERRORS AS err_count,
       SUM_WARNINGS AS warn_count,
       SEC_TO_TIME(SUM_TIMER_WAIT/1000000000000) AS exec_time_total,
       SEC_TO_TIME(MAX_TIMER_WAIT/1000000000000) AS exec_time_max,
       (AVG_TIMER_WAIT/1000000000) AS exec_time_avg_ms,
       SUM_ROWS_SENT AS rows_sent,
       ROUND(SUM_ROWS_SENT / COUNT_STAR) AS rows_sent_avg,
       SUM_ROWS_EXAMINED AS rows_scanned,
       DIGEST AS digest
  FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 5;
 
+-------------------------------------------------------------------+-----------+------------+-----------+------------+-----------------+---------------+------------------+------------+----------+--------------+----------------------------------+
| query                                                             | full_scan | exec_count | err_count | warn_count | exec_time_total | exec_time_max | exec_time_avg_ms | rows_total | rows_avg | rows_scanned | digest                           |
+-------------------------------------------------------------------+-----------+------------+-----------+------------+-----------------+---------------+------------------+------------+----------+--------------+----------------------------------+
| SELECT `mysqlserve0_` . `hid`  ... ` . `performanceSchema` AS ... | *         |       7747 |         0 |          0 | 00:00:02.5492   | 00:00:00.0009 |           0.3288 |       5200 |        1 |         5200 | 3176fe79ae8ce631eb328feaaafcf972 |
| SELECT `agent0_` . `hid` AS `h ... ent0_` . `id` = ? FOR UPDATE   |           |       7706 |         0 |          0 | 00:00:02.4067   | 00:00:00.0008 |           0.3122 |       7706 |        1 |         7706 | cbcf9ed706ce0974f1ef91b7c2d690eb |
| SELECT `os0_` . `hid` AS `hid1 ... ion` AS `hasVersion121_` , ... | *         |       5098 |         0 |          0 | 00:00:02.2526   | 00:00:00.0047 |           0.4416 |       5098 |        1 |         5098 | 1677026fd320b4876261b1270d28be38 |
| SELECT `replicatio0_` . `hid`  ... ess` AS `hasMast18_107_` , ... | *         |       2550 |         0 |          0 | 00:00:02.0543   | 00:00:00.0016 |           0.8055 |       2550 |        1 |         2550 | 90b8d082e28985953ff558546e00eb33 |
| SELECT `environmen0_` . `hid`  ... ck130_` , `environmen0_` . ... | *         |       2550 |         0 |          0 | 00:00:01.6082   | 00:00:00.0047 |           0.6304 |       2550 |        1 |         2550 | f0ba68f44b6d9357f1cff9670dc4ff2a |
+-------------------------------------------------------------------+-----------+------------+-----------+------------+-----------------+---------------+------------------+------------+----------+--------------+----------------------------------+

SEC_TO_TIME : ( 예를 들면 1시간인 3600) 시간형식(01:00:00)으로 변경.

query : 수행된 쿼리

full_scan : 쿼리 Full scan 여부

exec_count : 수행횟수

err_count : 에러 횟수

warn_count : 경고 횟수

exec_time_total : 전체 수행시간

exec_time_max : 최대 수행 시간

exec_time_avg_ms : 평균 수행 시간

rows_total : row

rows_sent_avg : 평균 보낸 행수

rows_scanned : 결과를 보내기 위해 검색된 row

digest : 수행된 쿼리

 

 

 

 

 

디스크 임시 테이블의 개수로 정렬된 임시 테이블을 먼저 내림차순으로 정렬한 다음 메모리 테이블의 개수로 정렬된 모든 명령문을 나열합니다. 정렬시 메모리 정렬 최적화 정도를 나타냅니다.

disk_tmp_tables사용률이 높을시 정렬 메모리 크기 증설을 고려해보아야 합니다.

SELECT IF(LENGTH(DIGEST_TEXT) > 64, CONCAT(LEFT(DIGEST_TEXT, 30), ' ... ', RIGHT(DIGEST_TEXT, 30)), DIGEST_TEXT) AS query,
       COUNT_STAR AS exec_count,
       SUM_CREATED_TMP_TABLES AS memory_tmp_tables,
       SUM_CREATED_TMP_DISK_TABLES AS disk_tmp_tables,
       ROUND(SUM_CREATED_TMP_TABLES / COUNT_STAR) AS avg_tmp_tables_per_query,
       ROUND((SUM_CREATED_TMP_DISK_TABLES / SUM_CREATED_TMP_TABLES) * 100) AS tmp_tables_to_disk_pct,
       DIGEST AS digest
  FROM performance_schema.events_statements_summary_by_digest
 WHERE SUM_CREATED_TMP_TABLES > 0
ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC, SUM_CREATED_TMP_TABLES DESC LIMIT 5;
 
+-------------------------------------------------------------------+------------+-------------------+-----------------+--------------------------+------------------------+
| query                                                             | exec_count | memory_tmp_tables | disk_tmp_tables | avg_tmp_tables_per_query | tmp_tables_to_disk_pct |
+-------------------------------------------------------------------+------------+-------------------+-----------------+--------------------------+------------------------+
| SELECT DISTINCTROW `hibalarm0_ ... testeval2_` . `alarm_id` = ... |          5 |                15 |               5 |                        3 |                     33 |
| SELECT DISTINCTROW `hibalarm0_ ... testeval2_` . `alarm_id` = ... |          2 |                 6 |               2 |                        3 |                     33 |
| SELECT * FROM latest_file_io                                      |          2 |                 4 |               2 |                        2 |                     50 |
| SELECT * FROM PROCESSLIST                                         |          2 |                 2 |               2 |                        1 |                    100 |
| SELECT SQL_CALC_FOUND_ROWS `st ...  , MIN ( `min_exec_time` ) ... |          1 |                 3 |               1 |                        3 |                     33 |
+-------------------------------------------------------------------+------------+-------------------+-----------------+--------------------------+------------------------+

query : 수행된 쿼리

full_scan : 쿼리 Full scan 여부

exec_count : 눅적 수행횟수

memory_tmp_tables : 명령문을 실행하는 동안 서버가 작성한 내부 임시 테이블수의 누적수.

disk_tmp_tables : 명령문을 실행하는 동안 서버가 작성한 내부 온 디스크 임시 테이블 수의 누적횟수. 횟수가 높으면 정렬 메모리 크기 증설을 고려

avg_tmp_tables_per_query : 쿼리당 평균 임시테이블이 생성된 횟수

tmp_tables_to_disk_pct : 최적 임시테이블 생성 비율. 낮을수록 좋음. 높을수록 정렬시 디스크에 임시테이블을 많이 생성해서 사용한다는 것이 되고, 그만큼 성능에 문제가 된다는 의미.

digest : 수행된 쿼리

 

• sort_merge_passes, sort_scans sort_rows 순으로 정렬된 명령문을 모두 나열합니다. 정렬 성능을 분석합니다.

SELECT IF(LENGTH(DIGEST_TEXT) > 64, CONCAT(LEFT(DIGEST_TEXT, 30), ' ... ', RIGHT(DIGEST_TEXT, 30)), DIGEST_TEXT) AS query,
       COUNT_STAR AS exec_count,
       SUM_SORT_MERGE_PASSES AS sort_merge_passes,
       ROUND(SUM_SORT_MERGE_PASSES / COUNT_STAR) AS avg_sort_merges,
       SUM_SORT_SCAN AS sorts_using_scans,
       SUM_SORT_RANGE AS sort_using_range,
       SUM_SORT_ROWS AS rows_sorted,
       ROUND(SUM_SORT_ROWS / COUNT_STAR) AS avg_rows_sorted,
       DIGEST AS digest
  FROM performance_schema.events_statements_summary_by_digest
 WHERE SUM_SORT_ROWS > 0
 ORDER BY SUM_SORT_MERGE_PASSES DESC, SUM_SORT_SCAN DESC, SUM_SORT_ROWS DESC LIMIT 5;
 
+-------------------------------------------------------------------+------------+-------------------+-----------------+-------------------+------------------+-------------+-----------------+----------------------------------+
| query                                                             | exec_count | sort_merge_passes | avg_sort_merges | sorts_using_scans | sort_using_range | rows_sorted | avg_rows_sorted | digest                           |
+-------------------------------------------------------------------+------------+-------------------+-----------------+-------------------+------------------+-------------+-----------------+----------------------------------+
| SELECT * FROM ps_helper . statements_with_sorting                 |          7 |                 0 |               0 |                 7 |                0 |          31 |               4 | 635d19e3e652972b3267ada0bf9c7b36 |
| SELECT * FROM statement_analysis                                  |          4 |                 0 |               0 |                 4 |                0 |          89 |              22 | 10f918a1a410f4fa0fc2602cff02deb7 |
| SELECT table_schema , SUM ( da ... tables GROUP BY table_schema   |          2 |                 0 |               0 |                 2 |                0 |          24 |              12 | 27fecd44f0bf5c0fc4e46f547083a09d |
| SELECT * FROM statements_with_sorting                             |          2 |                 0 |               0 |                 2 |                0 |           3 |               2 | dc117dd0eb81394322e3d4144a997ffc |
+-------------------------------------------------------------------+------------+-------------------+-----------------+-------------------+------------------+-------------+-----------------+----------------------------------+

query : 수행된 쿼리

exec_count : 수행횟수

sort_merge_passes : 정렬 알고리즘이 수행해야하는 병합 패스 수의 누적 횟수

avg_sort_merges : SUM_SORT_MERGE_PASSES / COUNT_STAR(누적 수행횟수) 평균.

sorts_using_scans : 테이블을 스캔하여 수행 한 정렬 수의 누적횟수

sort_using_range : 범위를 사용하여 수행 된 정렬 수의 누적횟수

rows_sorted : 정렬 된 행 수의 누적수

avg_rows_sorted : SUM_SORT_ROWS / COUNT_STAR(누적 수행횟수)의 평균

digest : 수행된 쿼리

 

전체 테이블 스캔을 수행한 모든 명령문을 전체 스캔이 수행된 횟수의 백분율로 정렬한 다음 명령문이 실행된 횟수로 나열합니다.

인덱스 성능 분석시 사용합니다.

SELECT IF(LENGTH(DIGEST_TEXT) > 64, CONCAT(LEFT(DIGEST_TEXT, 30), ' ... ', RIGHT(DIGEST_TEXT, 30)), DIGEST_TEXT) AS query,
       COUNT_STAR AS exec_count,
       SUM_NO_INDEX_USED AS no_index_used_count,
       SUM_NO_GOOD_INDEX_USED AS no_good_index_used_count,
       ROUND((SUM_NO_INDEX_USED / COUNT_STAR) * 100) no_index_used_pct,
       DIGEST AS digest
  FROM performance_schema.events_statements_summary_by_digest
 WHERE SUM_NO_INDEX_USED > 0
    OR SUM_NO_GOOD_INDEX_USED > 0
ORDER BY no_index_used_pct DESC, exec_count DESC LIMIT 5;
 
+-------------------------------------------------------------------+------------+---------------------+--------------------------+-------------------+
| query                                                             | exec_count | no_index_used_count | no_good_index_used_count | no_index_used_pct |
+-------------------------------------------------------------------+------------+---------------------+--------------------------+-------------------+
| SELECT `hibadvisor0_` . `sched ... _` . `advisorClassId` IN (?)   |        679 |                 679 |                        0 |               100 |
| SELECT `hibalarm0_` . `alarm_i ... ` . `fixed_time` < ? LIMIT ?   |        365 |                 365 |                        0 |               100 |
| SELECT `id` , `millis_stamp` , ... s` ORDER BY `id` ASC LIMIT ?   |        353 |                 353 |                        0 |               100 |
| SELECT `agent0_` . `hid` AS `h ... ventory` . `Agent` `agent0_`   |        112 |                 112 |                        0 |               100 |
| SELECT COUNT ( * ) AS `col_0_0 ... entry0_` . `fetchedDate` > ?   |         18 |                  18 |                        0 |               100 |
+-------------------------------------------------------------------+------------+---------------------+--------------------------+-------------------+

query : 수행된 쿼리

exec_count : 수행횟수

no_index_used_count : 쿼리에서 어떤 인덱스도 사용하지 않은 횟수 누적값

no_good_index_used_count : 서버에 존재하는 기존 인덱스중 사용할 어떤 인덱스도 찾지 못한 횟수

no_index_used_pct : 인덱스를 사용하지 못한 비율. 높을수록 인덱스 점검 필요.

 

 

오류 또는 경고를 발생시킨 모든 명령문을 나열합니다.

SELECT IF(LENGTH(DIGEST_TEXT) > 64, CONCAT(LEFT(DIGEST_TEXT, 30), ' ... ', RIGHT(DIGEST_TEXT, 30)), DIGEST_TEXT) AS query,
       COUNT_STAR AS exec_count,
       SUM_ERRORS AS errors,
       (SUM_ERRORS / COUNT_STAR) * 100 as error_pct,
       SUM_WARNINGS AS warnings,
       (SUM_WARNINGS / COUNT_STAR) * 100 as warning_pct,
       DIGEST AS digest
  FROM performance_schema.events_statements_summary_by_digest
 WHERE SUM_ERRORS > 0
    OR SUM_WARNINGS > 0
ORDER BY SUM_ERRORS DESC, SUM_WARNINGS DESC;
 
+-------------------------------------------------------------------+------------+--------+-----------+----------+-------------+----------------------------------+
| query                                                             | exec_count | errors | error_pct | warnings | warning_pct | digest                           |
+-------------------------------------------------------------------+------------+--------+-----------+----------+-------------+----------------------------------+
| CREATE PROCEDURE currently_ena ... w_instruments BOOLEAN DEFAULT  |          2 |      2 |  100.0000 |        0 |      0.0000 | ad6024cfc2db562ae268b25e65ef27c0 |
| CREATE PROCEDURE currently_ena ... ents WHERE enabled = ? ; END   |          2 |      1 |   50.0000 |        0 |      0.0000 | 4aac3ab9521a432ff03313a69cfcc58f |
| CREATE PROCEDURE currently_enabled ( BOOLEAN show_instruments     |          1 |      1 |  100.0000 |        0 |      0.0000 | c6df6711da3d1a26bc136dc8b354f6eb |
| CREATE PROCEDURE disable_backg ... d = ? WHERE TYPE = ? ; END IF  |          1 |      1 |  100.0000 |        0 |      0.0000 | 12e0392402780424c736c9555bcc9703 |
| DROP PROCEDURE IF EXISTS currently_enabled                        |         12 |      0 |    0.0000 |        6 |     50.0000 | 44cc7e655d08f430e0dd8f3110ed816c |
| DROP PROCEDURE IF EXISTS disable_background_threads               |          3 |      0 |    0.0000 |        2 |     66.6667 | 0153b7158dae80672bda6181c73f172c |
| CREATE SCHEMA IF NOT EXISTS ps_helper                             |          2 |      0 |    0.0000 |        1 |     50.0000 | a12cabd32d1507c758c71478075f5290 |
+-------------------------------------------------------------------+------------+--------+-----------+----------+-------------+----------------------------------+

query : 수행된 쿼리

exec_count : 수행횟수

errors : 쿼리 에러 횟수 누적값

error_pct : 수행된 쿼리 대비 에러 비율

warnings : 쿼리 경고 누적값

warning_pct : 수행된 쿼리 대비 경고 비율

digest : 수행 쿼리

 

 

※도움이 되셨다면 광고클릭 한번 부탁드립니다.※

 

Designed by JB FACTORY