반응형

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;

반응형

+ Recent posts