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 : 수행 쿼리
※도움이 되셨다면 광고클릭 한번 부탁드립니다.※
'Databases > MySQL' 카테고리의 다른 글
| [MySQL]카탈로그를 이용하여 각종 정보 조회 (0) | 2021.07.18 |
|---|---|
| [MySQL] Memory 모니터링 (1) | 2021.07.18 |
| [MySQL] Ubuntu에서 데이터 디렉토리 변경 (2) | 2021.07.03 |
| [MySQL] 페이지 압축과 테이블 압축의 비교 (0) | 2021.07.03 |
| [MySQL] Network 및 Replication 파라미터(MySQL 5.6기준) (0) | 2021.06.20 |