DevelopMySQL

MySQL - Top N 쿼리 확인 방법

MySQL에서 가장 많이 실행되거나 느린 쿼리를 확인하는 방법을 정리합니다.

Log 파일 기반 Top N 쿼리 확인

5. Slow Query Log 설정

-- Slow Query Log 활성화
SET GLOBAL slow_query_log = 'ON';

-- Slow Query 기준 시간 설정 (초 단위)
SET GLOBAL long_query_time = 2;

-- Slow Query Log 파일 위치 확인
SHOW VARIABLES LIKE 'slow_query_log_file';

-- 인덱스를 사용하지 않는 쿼리도 로깅
SET GLOBAL log_queries_not_using_indexes = 'ON';
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'slow_query_log_file';

6. Slow Query Log 분석 (mysqldumpslow)

# 가장 느린 쿼리 10개
mysqldumpslow -s t -t 10 /path/to/slow-query.log

# 가장 많이 실행된 쿼리 10개
mysqldumpslow -s c -t 10 /path/to/slow-query.log

# 평균 실행 시간이 긴 쿼리 10개
mysqldumpslow -s at -t 10 /path/to/slow-query.log

Performance Schema를 이용한 Top N 쿼리 확인

1. Performance Schema 활성화 확인

SHOW VARIABLES LIKE 'performance_schema';

2. 실행 횟수가 많은 Top N 쿼리

SELECT
    DIGEST_TEXT AS query,
    COUNT_STAR AS exec_count,
    AVG_TIMER_WAIT / 1000000000 AS avg_time_ms,
    SUM_TIMER_WAIT / 1000000000 AS total_time_ms
FROM
    performance_schema.events_statements_summary_by_digest
ORDER BY
    COUNT_STAR DESC
LIMIT 10;

3. 평균 실행 시간이 긴 Top N 쿼리

SELECT
    DIGEST_TEXT AS query,
    COUNT_STAR AS exec_count,
    AVG_TIMER_WAIT / 1000000000 AS avg_time_ms,
    MIN_TIMER_WAIT / 1000000000 AS min_time_ms,
    MAX_TIMER_WAIT / 1000000000 AS max_time_ms
FROM
    performance_schema.events_statements_summary_by_digest
WHERE
    DIGEST_TEXT IS NOT NULL
ORDER BY
    AVG_TIMER_WAIT DESC
LIMIT 10;

4. 총 실행 시간이 긴 Top N 쿼리

SELECT
    DIGEST_TEXT AS query,
    COUNT_STAR AS exec_count,
    SUM_TIMER_WAIT / 1000000000 AS total_time_ms,
    AVG_TIMER_WAIT / 1000000000 AS avg_time_ms,
    SUM_ROWS_EXAMINED AS total_rows_examined,
    SUM_ROWS_SENT AS total_rows_sent
FROM
    performance_schema.events_statements_summary_by_digest
WHERE
    DIGEST_TEXT IS NOT NULL
ORDER BY
    SUM_TIMER_WAIT DESC
LIMIT 10;

7. 현재 실행중인 쿼리 확인

-- 현재 실행중인 프로세스 확인
SHOW PROCESSLIST;

-- 또는 더 상세한 정보
SELECT
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME,
    STATE,
    INFO
FROM
    information_schema.PROCESSLIST
WHERE
    COMMAND != 'Sleep'
ORDER BY
    TIME DESC;

8. Performance Schema 통계 초기화

-- 특정 테이블 통계 초기화
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;

-- 전체 Performance Schema 초기화
CALL sys.ps_truncate_all_tables(FALSE);

주요 지표 설명

  • COUNT_STAR: 쿼리 실행 횟수
  • AVG_TIMER_WAIT: 평균 실행 시간 (나노초 단위, 1000000000으로 나누면 밀리초)
  • SUM_TIMER_WAIT: 총 실행 시간
  • SUM_ROWS_EXAMINED: 검사한 총 행 수
  • SUM_ROWS_SENT: 반환한 총 행 수

참고사항

  • Performance Schema는 MySQL 5.6 이상에서 사용 가능합니다
  • Performance Schema는 약간의 성능 오버헤드가 있을 수 있습니다
  • 프로덕션 환경에서는 주의해서 사용하세요
Share