performance_schema를 활성화 시켰을 경우 SQL별 통계정보가 쌓이는 뷰를 이용하여 튜닝 대상 선정에 유용하다.
해당 뷰는 performance_schema_digests_size라는 파라미터 값의 사이즈에 영향을 받는다.
해당 파라미터는 events_statements_summary_by_digest 뷰에 저장될 수 있는 row 수를 제한한다. -1은 auto sizing.
해당 파라미터 값 만큼의 데이터가 다 차면 데이터가 NULL로 된다. truncate 시키면 이전 수집 데이터는 다 지워지고 새로 정보들이 쌓인다.
show global variables like 'performance_schema_digests_size' ;
performance_schema_digests_size = 1000
조회 쿼리 :
SELECT schema_name,
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,
SEC_TO_TIME(SUM_TIMER_WAIT/1000000000000) AS exec_time_total,
SEC_TO_TIME(MAX_TIMER_WAIT/1000000000000) AS exec_time_max,
SEC_TO_TIME(AVG_TIMER_WAIT/1000000000000) AS exec_time_avg,
SUM_ROWS_EXAMINED / COUNT_STAR AS rows_scanned_avg ,
SUM_ROWS_SENT AS rows_sent,
ROUND(SUM_ROWS_SENT / COUNT_STAR) AS rows_sent_avg,
SUM_ERRORS AS err_count,
SUM_WARNINGS AS warn_count,
DIGEST AS digest
FROM performance_schema.events_statements_summary_by_digest
WHERE SEC_TO_TIME(AVG_TIMER_WAIT/1000000000000) > 1
ORDER BY 8 DESC ;
Temp 테이블을 사용하는 쿼리 확인
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;
Sort 쿼리
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;
Full Table Scan
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;
Error or Warning 쿼리
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;