Slow Query 분석 체계: pt-query-digest와 digest 기준 분류

Slow Query Log를 수집하는 것만으로는 성능 분석 체계가 완성되지 않는다. 로그 한 줄은 특정 시점에 특정 literal로 실행된 개별 사건이다. 운영자가 실제로 관리해야 하는 대상은 수백만 개의 사건이 아니라, 구조가 같은 SQL을 정규화한 query class와 그 class가 소비한 전체 시간이다.

예를 들어 4초가 걸린 SQL이 하루에 한 번 실행되고, 80ms가 걸린 SQL이 하루에 10만 번 실행되었다면 후자가 더 큰 자원을 소비하고 사용자 요청의 더 넓은 구간에 영향을 줄 수 있다. 개별 최댓값만 보면 전자를 선택하지만, Query_time:sum 기준으로 묶으면 후자가 우선순위에 올라온다. 반대로 총시간만 보면 호출 횟수가 적지만 배치 마감 시간을 결정하는 극단값이나 lock 대기 사건을 놓칠 수 있다. 따라서 Slow Query 분석은 분류, 집계, 우선순위화, 원문 재현, 개선 검증의 순환 구조로 설계해야 한다.

이 글은 MySQL 8.0 이상을 기준으로 다음 두 관측면을 함께 사용한다.

pt-query-digest: Slow Query Log 같은 외부 사건 기록을 fingerprint로 묶어 분석한다.
performance_schema.events_statements_summary_by_digest: 서버가 살아 있는 동안 전체 statement를 digest 기준으로 누적 집계한다.

두 도구는 비슷한 정규화 개념을 사용하지만 같은 저장소도, 같은 식별자 체계도 아니다. 서로 대체하려 하기보다 수집 범위와 보존 기간의 차이를 이해하고 교차 검증해야 한다.

1. 분석 단위를 사건에서 query class로 바꾼다

다음 두 문장은 literal만 다르고 실행 구조는 같다.

SELECT account_id, balance FROM account WHERE account_id = 101;
SELECT account_id, balance FROM account WHERE account_id = 9072;

fingerprint 또는 digest는 literal을 placeholder로 치환하고 공백·대소문자 같은 표현 차이를 정규화하여 대략 다음과 같은 대표 형태를 만든다.

select account_id, balance from account where account_id = ?

이 대표 형태를 기준으로 개별 실행 사건을 모으면 다음 질문에 답할 수 있다.

이 SQL class는 분석 기간에 몇 번 호출되었는가?
전체 DB 응답 시간 중 몇 퍼센트를 소비했는가?
평균은 낮지만 최댓값이나 상위 percentile이 큰가?
반환 행보다 조사 행이 지나치게 많은가?
lock time, 내부 임시 테이블, filesort 같은 비용 신호가 증가했는가?
특정 배포 이후 호출 횟수나 실행 시간이 변했는가?

flowchart LR
    A[Slow Query 사건들<br/>literal과 실행시간이 서로 다름] --> B[parser와 정규화]
    B --> C[fingerprint / digest]
    C --> D[query class별 집계]
    D --> E1[총 실행시간]
    D --> E2[호출 횟수]
    D --> E3[평균·최대·p95]
    D --> E4[Rows examined·Lock time]
    E1 --> F[개선 후보 우선순위]
    E2 --> F
    E3 --> F
    E4 --> F
    F --> G[대표 SQL과 실행 계획 검증]
    G --> H[개선 후 같은 기준으로 비교]

정규화는 SQL 의미를 완전히 이해하는 semantic equivalence 판정이 아니다. literal과 표현 차이를 줄여 운영 가능한 집계 단위를 만드는 절차다. 같은 fingerprint 안에서도 parameter 값에 따라 선택도, partition pruning, 반환 행 수, lock 경합이 달라질 수 있다. query class 집계에서 이상 신호를 찾은 뒤에는 반드시 대표 사건과 값 분포로 다시 내려가야 한다.

2. pt-query-digest가 처리하는 경로

pt-query-digest는 Percona Toolkit에 포함된 분석 도구다. Slow Query Log, general log, text로 변환한 binary log, processlist, tcpdump 입력을 다룰 수 있지만, 지속적인 운영 분석에서는 보통 회전·보존된 Slow Query Log 파일을 입력으로 사용한다.

기본 처리 경로는 다음과 같다.

입력 파일에서 각 query event와 Query_time, Lock_time, Rows_sent, Rows_examined 같은 attribute를 읽는다.
SQL 원문을 fingerprint로 정규화한다.
기본적으로 fingerprint가 같은 사건을 하나의 class로 묶는다.
class별 count, sum, min, max, avg, 근사 percentile, 표준편차 등을 계산한다.
기본적으로 총 Query_time이 큰 순서로 response-time profile과 상세 보고서를 출력한다.
각 class에는 대표 sample을 붙인다. 일반적으로 지정한 정렬 attribute 기준으로 가장 나쁜 사건이 sample이 된다.

기본 분석 명령은 단순하다.

pt-query-digest \
  --type slowlog \
  --group-by fingerprint \
  --order-by Query_time:sum \
  --limit 20 \
  /var/log/mysql/slow-analysis.log \
  > slow-digest-report.txt

실제 운영에서는 MySQL이 쓰고 있는 활성 로그를 분석 서버에서 반복해서 직접 읽지 않는다. 먼저 안전하게 회전한 파일을 분석 영역으로 복사하고, 원본 파일명·server identifier·수집 시작/종료 시각·시간대·long_query_time을 함께 보존한다. 입력 경계가 불명확하면 전일 보고서와 금일 보고서에 같은 사건이 중복되거나, 로그 회전 사이의 사건이 누락될 수 있다.

2.1 response-time profile 읽는 순서

보고서의 첫 번째 핵심은 class별 response-time profile이다. 다음 열을 중심으로 읽는다.

항목	의미	운영 판단
`Rank`	정렬 기준에 따른 순위	순위 자체보다 어떤 `--order-by`를 썼는지 먼저 확인한다.
`Query ID`	fingerprint checksum의 16진수 표현	동일한 pt-query-digest 처리 계열에서 class를 추적하는 표식으로 사용한다.
`Response time`	class의 `Query_time` 합계와 전체 비율	workload 전체에 대한 누적 기여도를 본다.
`Calls`	실행 횟수	평균이 작아도 호출 폭증으로 총비용이 큰 SQL을 찾는다.
`R/Call`	호출당 평균 응답 시간	한 번의 사용자 체감 지연을 가늠하되 분포와 함께 본다.
`V/M`	variance-to-mean 비율	값이 크면 parameter·cache·lock·계획 변화에 따른 편차를 의심한다.
`Item`	축약된 query class	대표 sample과 schema 정보를 연결하는 출발점이다.

Response time 비율은 DB 서버가 분석 기간에 소비한 wall-clock의 완전한 비율이 아니다. Slow Query Log에 기록된 사건들의 Query_time 합계 안에서의 비율이다. long_query_time=1이라면 900ms 문장은 아무리 자주 실행되어도 표본에 들어오지 않는다. 보고서 제목과 대시보드에 수집 임계값을 반드시 표시해야 하는 이유다.

2.2 평균 하나로 판단하지 않는다

상세 class 보고서는 count, total, min, max, avg, 95%, stddev, median과 실행 시간 histogram을 제공한다. percentile과 median은 메모리 사용을 제한하기 위한 bucket 기반 근삿값이므로 정밀한 SLO 판정값으로 과도하게 해석하지 않는다. 그 대신 다음과 같은 분포 형태를 찾는다.

평균과 p95가 함께 높다: class 자체가 지속적으로 비싸다.
평균은 낮고 max·p95만 높다: 특정 parameter, cold cache, lock wait, 계획 변화 가능성이 있다.
호출 수가 급증하고 R/Call은 비슷하다: 애플리케이션 호출 패턴이나 N+1 query를 의심한다.
Rows_examined가 Rows_sent보다 매우 크다: filtering 효율, index와 predicate의 정렬을 점검한다.
Lock_time 비율이 크다: 실행 계획 개선만이 아니라 transaction 경계와 동시성 관계를 조사한다.

3. fingerprint의 경계와 주의점

fingerprint는 literal 제거, 공백 축약, 소문자화, comment 제거 같은 변환을 수행한다. IN()의 literal 목록이나 multi-row VALUES()도 축약한다. 이 덕분에 값 개수가 다른 유사 SQL을 한 class로 관리할 수 있지만, 분석 과정에서 정보가 사라진다는 뜻이기도 하다.

3.1 같은 class 안에서 성능이 달라지는 이유

다음 조건은 같은 구조로 정규화될 수 있어도 비용이 크게 다를 수 있다.

특정 tenant만 데이터가 매우 많다.
날짜 범위가 1시간일 때와 1년일 때 조사 행 수가 다르다.
IN() 목록 길이에 따라 range 수와 optimizer 선택이 바뀐다.
문자열 prefix 또는 skew가 심한 값에 따라 index 선택도가 달라진다.
한 실행은 buffer pool hit이고 다른 실행은 storage I/O를 동반한다.
일부 실행만 transaction lock에 대기한다.
bind 값의 데이터 타입이 컬럼 타입과 맞지 않아 변환 비용이나 계획이 달라진다.

따라서 상위 class를 발견하면 상세 보고서의 worst sample 하나만 보지 말고, 원본 로그에서 같은 Query ID에 해당하는 사건을 다시 추출하여 Query_time, Rows_examined, 시간대, database, user/host attribute 분포를 비교한다. worst sample은 중요한 시작점이지만 전체 class의 전형적인 실행이라고 보장되지 않는다.

3.2 식별자를 도구 사이에서 직접 조인하지 않는다

pt-query-digest의 Query ID와 MySQL Performance Schema의 DIGEST는 모두 정규화 SQL을 대표하지만 생성 주체와 알고리즘, 버전 수명주기가 다르다. 두 값을 같은 hash로 가정하여 직접 조인하면 안 된다. 다음 정보를 함께 보관하여 논리적으로 연결한다.

정규화된 fingerprint 또는 DIGEST_TEXT
원문 sample SQL
default schema
접근 table과 operation 종류
수집 기간과 server identifier
애플리케이션 route, trace 또는 SQL comment에서 별도로 보존한 출처 정보

SQL comment는 fingerprint 처리 중 제거될 수 있다. comment에 route 정보를 넣었다면 원본 로그나 별도 tracing 저장소에서 먼저 attribute로 분리해야 한다.

4. Performance Schema digest로 현재 workload를 본다

Slow Query Log는 임계값을 넘은 사건을 오래 보존하는 데 유리하다. 반면 performance_schema.events_statements_summary_by_digest는 statements_digest consumer가 활성화된 동안 완료된 statement를 digest별로 집계한다. 임계값 아래의 짧고 빈번한 SQL도 볼 수 있으므로 Slow Query Log의 관측 사각지대를 보완한다.

다음 재현 예제는 literal이 다른 두 SELECT가 하나의 digest로 집계되는지 확인한다. TRUNCATE는 digest 요약을 초기화하므로 폐기 가능한 검증 환경에서만 실행한다. 운영 서버에서는 기준 시점의 값을 별도 저장한 뒤 delta를 계산하는 방식을 사용한다.

TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
DROP TABLE IF EXISTS digest_account;
CREATE TABLE digest_account (
    account_id BIGINT NOT NULL,
    balance DECIMAL(12,2) NOT NULL,
    status VARCHAR(16) NOT NULL,
    PRIMARY KEY (account_id),
    KEY ix_status (status)
) ENGINE=InnoDB;

INSERT INTO digest_account (account_id, balance, status) VALUES
    (101, 1200.00, 'ACTIVE'),
    (202, 3400.00, 'ACTIVE'),
    (303,  800.00, 'HOLD'),
    (404, 5100.00, 'ACTIVE');

SELECT account_id, balance
FROM digest_account
WHERE account_id = 101;

SELECT account_id, balance
FROM digest_account
WHERE account_id = 202;

SELECT DIGEST_TEXT,
       COUNT_STAR,
       SUM_ROWS_EXAMINED,
       SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = DATABASE()
  AND DIGEST_TEXT LIKE 'SELECT `account_id` , `balance` FROM `digest_account`%'
ORDER BY COUNT_STAR DESC;

실행 결과(MySQL 8.0.x):

준비 DDL/DML과 핵심 조회 결과만 발췌했다.

mysql> CREATE TABLE digest_account (...);

Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO digest_account (account_id, balance, status) VALUES (...);

Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT account_id, balance
    -> FROM digest_account
    -> WHERE account_id = 101;

+------------+---------+
| account_id | balance |
+------------+---------+
|        101 | 1200.00 |
+------------+---------+
1 row in set (0.00 sec)

mysql> SELECT DIGEST_TEXT, COUNT_STAR,
    ->        SUM_ROWS_EXAMINED, SUM_ROWS_SENT
    -> FROM performance_schema.events_statements_summary_by_digest
    -> WHERE SCHEMA_NAME = DATABASE()
    ->   AND DIGEST_TEXT LIKE 'SELECT `account_id` , `balance` FROM `digest_account`%'
    -> ORDER BY COUNT_STAR DESC;

+------------------------------------------------------------------------------+------------+-------------------+---------------+
| DIGEST_TEXT                                                                  | COUNT_STAR | SUM_ROWS_EXAMINED | SUM_ROWS_SENT |
+------------------------------------------------------------------------------+------------+-------------------+---------------+
| SELECT `account_id` , `balance` FROM `digest_account` WHERE `account_id` = ? |          2 |                 2 |             2 |
+------------------------------------------------------------------------------+------------+-------------------+---------------+
1 row in set (0.00 sec)

결과에서 COUNT_STAR=2인 한 행이 보이면 literal 101과 202가 같은 digest class에 집계된 것이다. DIGEST_TEXT의 식별자 quote와 공백 형식은 서버가 만든 정규화 표현이므로 애플리케이션이 임의로 문자열을 조립해 일치시키기보다 DIGEST 값과 실제 표의 text를 함께 저장하는 편이 안전하다.

4.1 총시간과 평균·최대 시간을 함께 조회한다

TIMER_WAIT 계열은 picosecond 단위다. 사람이 읽을 report에서는 초 또는 millisecond로 환산하되, 저장 단계에서는 원본 정수를 유지하면 반올림 손실을 줄일 수 있다. 다음 쿼리는 현재 schema의 digest를 총 실행 시간 기준으로 정렬한다.

SELECT COALESCE(SCHEMA_NAME, '<no schema>') AS schema_name,
       DIGEST,
       LEFT(DIGEST_TEXT, 100) AS digest_text,
       COUNT_STAR AS calls,
       ROUND(SUM_TIMER_WAIT / 1000000000000, 6) AS total_seconds,
       ROUND(AVG_TIMER_WAIT / 1000000000, 3) AS avg_ms,
       ROUND(MAX_TIMER_WAIT / 1000000000, 3) AS max_ms,
       SUM_ROWS_EXAMINED AS rows_examined,
       SUM_ROWS_SENT AS rows_sent,
       SUM_NO_INDEX_USED AS no_index_count,
       FIRST_SEEN,
       LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = DATABASE()
  AND DIGEST IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

실행 결과(MySQL 8.0.x):

검증 환경에서 생성한 class 가운데 설명 대상 행을 골라 핵심 열만 발췌했다. 시간값은 한 번의 임시 환경 실행 결과이며 운영 기준값이 아니다.

mysql> SELECT COALESCE(SCHEMA_NAME, '<no schema>') AS schema_name,
    ->        DIGEST, LEFT(DIGEST_TEXT, 100) AS digest_text,
    ->        COUNT_STAR AS calls, ...
    -> FROM performance_schema.events_statements_summary_by_digest
    -> WHERE SCHEMA_NAME = DATABASE() AND DIGEST IS NOT NULL
    -> ORDER BY SUM_TIMER_WAIT DESC
    -> LIMIT 10;

+-----------------+--------------------------------------------------------------------------------+-------+---------------+--------+--------+---------------+-----------+
| schema_name     | digest_text                                                                    | calls | total_seconds | avg_ms | max_ms | rows_examined | rows_sent |
+-----------------+--------------------------------------------------------------------------------+-------+---------------+--------+--------+---------------+-----------+
| mysql_tech_note | SELECT `account_id` , `balance` FROM `digest_account` WHERE `account_id` = ?   |     2 |        0.0003 |  0.140 |  0.202 |             2 |         2 |
+-----------------+--------------------------------------------------------------------------------+-------+---------------+--------+--------+---------------+-----------+

이 결과는 누적 counter다. 두 시점의 snapshot을 같은 key인 (SCHEMA_NAME, DIGEST)로 비교하여 COUNT_STAR, SUM_TIMER_WAIT, SUM_ROWS_EXAMINED의 delta를 계산해야 5분 또는 1시간 구간의 workload를 얻을 수 있다. 서버 재시작, table truncate, digest row eviction이 있으면 단순 뺄셈이 음수가 되거나 class가 사라질 수 있으므로 snapshot 수집기는 server uptime과 수집 epoch도 기록해야 한다.

4.2 percentile과 sample은 조사 방향을 정하는 단서다

MySQL 8.0의 digest summary에는 QUANTILE_95, QUANTILE_99, QUANTILE_999와 sample 관련 열이 있다. percentile은 statement histogram에서 계산한 상한 추정치다. sample은 해당 digest를 만든 실제 문장 하나를 제공하지만 “가장 느린 문장”이나 “가장 최근 문장”이라고 가정하지 않는다.

SELECT LEFT(DIGEST_TEXT, 80) AS digest_text,
       COUNT_STAR AS calls,
       ROUND(QUANTILE_95 / 1000000000, 3) AS p95_ms,
       ROUND(QUANTILE_99 / 1000000000, 3) AS p99_ms,
       LEFT(QUERY_SAMPLE_TEXT, 120) AS sample_text,
       QUERY_SAMPLE_SEEN
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = DATABASE()
  AND DIGEST_TEXT LIKE 'SELECT `account_id` , `balance` FROM `digest_account`%'
LIMIT 1;

실행 결과(MySQL 8.0.x):

검증 결과에서 긴 sample SQL을 한 줄로 줄여 표시했다.

mysql> SELECT LEFT(DIGEST_TEXT, 80) AS digest_text, COUNT_STAR AS calls,
    ->        ROUND(QUANTILE_95 / 1000000000, 3) AS p95_ms,
    ->        ROUND(QUANTILE_99 / 1000000000, 3) AS p99_ms,
    ->        LEFT(QUERY_SAMPLE_TEXT, 120) AS sample_text, QUERY_SAMPLE_SEEN
    -> FROM performance_schema.events_statements_summary_by_digest
    -> WHERE SCHEMA_NAME = DATABASE()
    ->   AND DIGEST_TEXT LIKE 'SELECT `account_id` , `balance` FROM `digest_account`%'
    -> LIMIT 1;

+------------------------------------------------------------------------------+-------+--------+--------+-----------------------------------------------------------------------+
| digest_text                                                                  | calls | p95_ms | p99_ms | sample_text                                                           |
+------------------------------------------------------------------------------+-------+--------+--------+-----------------------------------------------------------------------+
| SELECT `account_id` , `balance` FROM `digest_account` WHERE `account_id` = ? |     2 |  0.209 |  0.209 | SELECT account_id, balance FROM digest_account WHERE account_id = 101 |
+------------------------------------------------------------------------------+-------+--------+--------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

QUERY_SAMPLE_TEXT에는 literal이나 업무 데이터가 포함될 수 있다. 운영 수집 시스템으로 외부 전송하기 전에 접근 제어, 보존 기간, masking 요구를 정해야 한다. digest text는 정규화되어도 sample text는 정규화 원문이 아니라는 점이 중요하다.

5. digest summary의 용량 한계를 감시한다

events_statements_summary_by_digest의 최대 행 수는 서버 시작 시 performance_schema_digests_size로 정해진다. 새 digest가 들어왔는데 table이 가득 찼으면 MySQL은 DIGEST=NULL인 catch-all 행에 집계한다. 이 행의 비율이 커지면 상위 digest 보고서가 workload를 충분히 대표하지 못한다.

SELECT @@performance_schema_digests_size AS digest_capacity;

SELECT SUM(COUNT_STAR) AS total_statements,
       SUM(CASE WHEN DIGEST IS NULL THEN COUNT_STAR ELSE 0 END) AS unclassified_statements,
       ROUND(
           100 * SUM(CASE WHEN DIGEST IS NULL THEN COUNT_STAR ELSE 0 END)
           / NULLIF(SUM(COUNT_STAR), 0),
           2
       ) AS unclassified_pct
FROM performance_schema.events_statements_summary_by_digest;

실행 결과(MySQL 8.0.x):

mysql> SELECT @@performance_schema_digests_size AS digest_capacity;

+-----------------+
| digest_capacity |
+-----------------+
|           10000 |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT SUM(COUNT_STAR) AS total_statements,
    ->        SUM(CASE WHEN DIGEST IS NULL THEN COUNT_STAR ELSE 0 END) AS unclassified_statements,
    ->        ROUND(
    ->            100 * SUM(CASE WHEN DIGEST IS NULL THEN COUNT_STAR ELSE 0 END)
    ->            / NULLIF(SUM(COUNT_STAR), 0),
    ->            2
    ->        ) AS unclassified_pct
    -> FROM performance_schema.events_statements_summary_by_digest;

+------------------+-------------------------+------------------+
| total_statements | unclassified_statements | unclassified_pct |
+------------------+-------------------------+------------------+
|               16 |                       0 |             0.00 |
+------------------+-------------------------+------------------+
1 row in set (0.00 sec)

unclassified_pct가 지속적으로 의미 있는 비율을 차지하면 다음을 순서대로 점검한다.

SQL이 literal을 identifier나 comment에 삽입하여 digest 종류를 불필요하게 늘리는지 확인한다.
tenant별 물리 table처럼 동적 identifier가 폭증하는 schema 설계인지 확인한다.
server memory 예산 안에서 performance_schema_digests_size 증설을 검토한다.
재시작이 필요한 설정 변경 전에 실제 digest 개수와 catch-all 비율을 장기간 측정한다.
증설만으로 원인을 가리지 말고 애플리케이션의 SQL 생성 패턴을 바로잡는다.

capacity는 row 수 상한이며 query text 보존 길이는 performance_schema_max_digest_length, sample 표시 공간은 performance_schema_max_sql_text_length 같은 시작 옵션의 영향도 받는다. 긴 SQL이 같은 prefix처럼 잘려 보인다고 해서 반드시 같은 digest라고 단정하지 말고 DIGEST를 함께 확인한다.

6. pt-query-digest와 Performance Schema를 결합하는 방법

두 관측면은 수집 대상과 시간 성격이 다르다.

관점	pt-query-digest + Slow Query Log	Performance Schema digest summary
입력 범위	로그 판정 조건을 통과한 사건	instrumentation이 수집한 완료 statement
시간 범위	보존한 파일 기간	서버 시작·초기화 이후 누적
원문 사건	개별 로그 사건 보존 가능	집계와 제한된 sample 중심
임계값 아래 SQL	일반적으로 보이지 않음	짧고 빈번한 SQL도 집계 가능
분포 분석	class별 근사 통계와 histogram	digest histogram 기반 quantile 제공
장기 추세	파일 또는 `--history` 저장 설계 필요	주기적 snapshot과 delta 저장 필요
주요 위험	log sampling bias, 중복·누락, 민감 literal	digest capacity, reset, sample 민감정보

권장 운영 순서는 다음과 같다.

sequenceDiagram
    participant M as MySQL
    participant L as 로그 수집기
    participant P as pt-query-digest
    participant S as Digest Snapshot 저장소
    participant D as DBA

    M->>L: 회전된 Slow Query Log 전달
    M->>S: Performance Schema digest snapshot
    L->>P: 기간·서버별 불변 입력 파일
    P->>D: Query_time:sum 상위 class 보고서
    S->>D: 전체 workload의 count/time delta
    D->>D: normalized text·schema·시간대로 교차 확인
    D->>M: 대표 SQL의 EXPLAIN ANALYZE 및 schema 확인
    D->>S: 배포 전후 동일 digest 지표 비교

6.1 일간 batch와 상시 관측을 분리한다

5분~15분 snapshot: Performance Schema digest counter를 저장하고 delta를 계산한다. 호출 급증과 임계값 아래 SQL을 빠르게 감지한다.
일간 digest report: 회전된 Slow Query Log를 pt-query-digest로 분석한다. 총시간, tail, 조사 행, lock time을 기준으로 검토 queue를 만든다.
주간 trend: 같은 query class가 계속 상위에 있는지, 배포 이후 신규 class가 생겼는지, 개선한 class가 실제로 내려갔는지 검토한다.
사건 분석: 장애 시간 구간의 원본 slow log, trace, Performance Schema snapshot, system metric을 동일한 시간대로 정렬한다.

6.2 하나의 순위표만 만들지 않는다

다음 네 목록을 각각 만든 뒤 교집합과 차집합을 본다.

Query_time:sum 상위: 전체 DB 시간을 가장 많이 소비한 class
Query_time:max 또는 p95 상위: tail latency 위험이 큰 class
Rows_examined:sum 상위: CPU·buffer pool·I/O 낭비 후보
Lock_time:sum 상위: transaction 경합 후보

총시간 상위 20개만 반복해서 최적화하면 저빈도 tail 사건과 lock storm을 놓칠 수 있다. 반대로 max 한 번만 보고 index를 추가하면 일시적인 cache miss나 대기 사건에 과잉 대응할 수 있다.

7. 재현 가능한 일간 분석 runbook

7.1 입력 파일을 불변으로 만든다

활성 slow log가 회전된 뒤 분석 파일의 checksum과 byte 범위를 기록한다. 동일한 입력을 다시 처리해도 같은 보고서가 나와야 한다. 여러 server의 파일을 합칠 때는 server identifier와 timestamp timezone을 별도 metadata로 유지한다.

install -d -m 0750 /srv/mysql-analysis/2026-08-14
cp --preserve=timestamps \
  /srv/mysql-slow-archive/db-a/slow-2026-08-13.log \
  /srv/mysql-analysis/2026-08-14/
sha256sum /srv/mysql-analysis/2026-08-14/slow-2026-08-13.log \
  > /srv/mysql-analysis/2026-08-14/SHA256SUMS

이 경로와 파일명은 공개 예시다. 실제 운영에서는 로그 수집 agent가 소유권과 암호화, 보존 정책을 관리하게 한다. Slow Query Log에는 literal, comment, 사용자 이름 등 민감정보가 들어갈 수 있으므로 일반 문서 저장소나 ticket에 원본을 첨부하지 않는다.

7.2 동일 입력에서 여러 관점의 보고서를 만든다

input=/srv/mysql-analysis/2026-08-14/slow-2026-08-13.log
out=/srv/mysql-analysis/2026-08-14

pt-query-digest --type slowlog \
  --group-by fingerprint \
  --order-by Query_time:sum \
  --limit 20 "$input" > "$out/by-total-query-time.txt"

pt-query-digest --type slowlog \
  --group-by fingerprint \
  --order-by Query_time:max \
  --limit 20 "$input" > "$out/by-max-query-time.txt"

pt-query-digest --type slowlog \
  --group-by fingerprint \
  --order-by Rows_examined:sum \
  --limit 20 "$input" > "$out/by-rows-examined.txt"

pt-query-digest --type slowlog \
  --group-by fingerprint \
  --order-by Lock_time:sum \
  --limit 20 "$input" > "$out/by-lock-time.txt"

명령을 자동화할 때 사용한 pt-query-digest --version, command line, 입력 checksum, 실행 종료 상태를 manifest에 남긴다. Toolkit version이 바뀌면 fingerprint와 report 형식의 차이가 추세 비교에 영향을 줄 수 있으므로 version 변경일을 분석 시계열에 표시한다.

7.3 검토 대상에 상태를 부여한다

pt-query-digest --review와 --history는 query class와 지표를 MySQL table에 저장할 수 있다. 다만 분석 도구가 production primary에 쓰기 workload를 만들지 않도록 별도 관리 DB를 사용한다. 직접 만든 검토 저장소를 사용한다면 최소한 다음 필드를 둔다.

분석 날짜와 시간 범위
source server 또는 cluster identifier
toolkit version과 input checksum
pt Query ID, normalized text, representative sample의 보안 처리본
calls, total/avg/max/p95 query time
lock time, rows examined, rows sent
owner, review status, ticket, 다음 확인일
배포 전 baseline과 배포 후 결과

“상위 10개를 읽었다”가 아니라 신규 → 조사 중 → 개선 예정 → 배포됨 → 효과 확인 → 종료의 상태 전이를 관리해야 같은 SQL을 매일 처음부터 분석하는 낭비를 줄일 수 있다.

8. 우선순위 결정 기준

상위 class마다 다음 점수를 기계적으로 더하는 것보다, 네 축을 분리해 판단하는 편이 안전하다.

8.1 영향도

분석 기간 총 Query_time 비율
호출 횟수와 동시 실행 수준
사용자 요청, batch 마감, replication lag에 미치는 영향
primary뿐 아니라 read replica와 Aurora reader에 퍼지는 fan-out

8.2 개선 가능성

Rows_examined / Rows_sent 비율이 큰가?
missing/inefficient index, non-sargable predicate, 불필요한 sort·temporary table 신호가 있는가?
반환 데이터와 호출 횟수를 애플리케이션에서 줄일 수 있는가?
schema·통계·SQL rewrite 중 위험이 낮고 검증 가능한 방법이 있는가?

8.3 위험도

변경이 write amplification과 buffer pool 사용량을 늘리는가?
새 index가 DML, backup, replica apply, storage 비용에 미치는 영향은 무엇인가?
lock time이 큰 SQL을 단순히 index 문제로 오진하고 있지 않은가?
parameter 값에 따라 계획이 달라지는 class를 단일 sample로 판단하지 않았는가?

8.4 검증 가능성

production과 유사한 데이터 분포로 EXPLAIN ANALYZE를 실행할 수 있는가?
배포 전후 같은 digest의 calls·total time·rows examined delta를 비교할 수 있는가?
workload 변화와 개선 효과를 분리할 control 기간이 있는가?
rollback 기준과 관측 기간이 정해졌는가?

일반적으로 총시간이 크고, 조사 행 낭비가 뚜렷하며, 변경 위험이 낮고, 전후 검증이 쉬운 class부터 처리한다. 단, 결제·인증처럼 tail 한 번의 업무 영향이 큰 경로는 총시간 순위가 낮아도 별도 우선순위를 부여한다.

9. 흔한 실패와 오해

9.1 “가장 느린 한 줄”만 고른다

단일 max 사건은 lock wait, cold cache, backup I/O 같은 외부 요인의 결과일 수 있다. fingerprint class의 호출 수, 총시간, 분포와 원문 사건을 함께 본다.

9.2 Slow Query Log가 전체 workload라고 생각한다

로그는 long_query_time, min_examined_row_limit, 비인덱스 수집 설정과 throttle을 통과한 표본이다. 임계값 아래의 빈번한 SQL은 Performance Schema digest로 보완한다.

9.3 보고서 순위가 매일 흔들리는데 즉시 회귀로 판단한다

traffic 양, 수집 시간 길이, 임계값, 로그 누락, batch 유무가 다르면 raw total은 비교할 수 없다. calls당 지표와 동일 시간대 delta를 함께 사용한다.

9.4 fingerprint가 같으면 실행 계획도 같다고 가정한다

값 분포, range 크기, optimizer statistics와 runtime 상태에 따라 비용과 실제 행 수가 달라진다. 느린 구간의 representative parameter를 보안 통제 아래 확보하고 실제 schema에서 계획을 확인한다.

9.5 평균만 저장한다

평균은 tail을 숨긴다. 최소한 count, sum, avg, max, p95 또는 histogram, rows examined, lock time을 보관한다. percentile의 계산 방식과 근사 여부도 metadata에 남긴다.

9.6 digest table을 주기적으로 비우면서 epoch를 기록하지 않는다

TRUNCATE TABLE performance_schema.events_statements_summary_by_digest는 행을 제거하고 관련 histogram에도 영향을 준다. 수집기에서 reset을 감지하지 못하면 delta가 깨진다. 운영 초기화는 승인된 절차로만 수행하고 수집 epoch를 변경한다.

9.7 `DIGEST=NULL` 행을 무시한다

catch-all 비율이 크면 보고서 상위 class가 실제 workload의 작은 부분만 대표할 수 있다. capacity와 동적 SQL 생성 패턴을 함께 점검한다.

9.8 sample SQL을 평문으로 장기 저장한다

sample에는 개인정보나 업무 식별자가 포함될 수 있다. normalized text와 aggregate는 넓게 공유하더라도 sample 원문은 별도 권한·masking·보존 정책을 적용한다.

10. Aurora MySQL에서의 운영 해석

Aurora MySQL에서도 Slow Query Log와 Performance Schema digest 개념은 유효하지만 수집·저장·failover 경계를 다르게 봐야 한다.

Slow Query Log 관련 parameter는 DB cluster parameter group과 DB parameter group의 적용 범위, dynamic/static 여부를 확인한다.
로그를 CloudWatch Logs로 내보내면 중앙 보존과 검색이 쉬워지지만 ingestion 지연, 비용, multiline event 처리, 중복 수집 경계를 점검해야 한다.
writer failover 뒤에는 새 writer의 log stream과 instance identifier가 달라질 수 있다. 일간 분석 입력을 cluster 단위로 합치되 source instance와 role을 보존한다.
reader마다 workload와 cache 상태가 다르다. 모든 reader digest를 단순 합산하면 routing 불균형과 특정 instance hot spot이 가려질 수 있으므로 instance별 보고서와 cluster 합계를 함께 유지한다.
Performance Schema summary는 instance memory 안의 누적 상태다. restart나 failover를 넘는 장기 저장소가 아니므로 외부 snapshot 수집이 필요하다.
Performance Insights 또는 Database Insights의 Top SQL 식별자와 pt-query-digest Query ID, Performance Schema DIGEST가 동일하다고 가정하지 않는다. normalized SQL, 시간대, DB, 호출량을 사용해 교차 확인한다.
Aurora의 분산 storage 특성 때문에 Community MySQL과 I/O metric의 의미가 완전히 같지 않다. query class 개선 전후에는 DB load, wait category, buffer cache 관련 지표, reader lag와 storage 비용 신호를 함께 본다.

Aurora에서 pt-query-digest를 실행하는 위치는 DB instance 내부가 아니다. CloudWatch 또는 export pipeline에서 확보한 slow log를 격리된 분석 host로 가져와 처리한다. 분석 host에는 production 접속 credential이 없어도 파일 기반 보고서를 만들 수 있게 하는 편이 안전하다.

11. 운영 체크리스트

수집 경계

Slow Query Log의 long_query_time, min_examined_row_limit Slow Query Log의 `long_query_time`, `min_examined_row_limit`, 출력 대상과 throttle 값을 보고서에 기록했는가?
활성 파일이 아니라 회전 완료된 불변 파일을 분석하는가?
입력 checksum, server identifier, 시간대, 시작·종료 시각을 보존했는가?
로그 누락·중복과 Aurora failover에 따른 stream 변경을 감지하는가?

분류와 집계

기본 분석 단위가 개별 SQL이 아니라 fingerprint/digest class인가?
총시간, max 또는 p95, rows examined, lock time 관점을 각각 확인했는가?
pt Query ID와 Performance Schema DIGEST pt Query ID와 Performance Schema `DIGEST`를 같은 값으로 가정하지 않았는가?
fingerprint가 숨긴 parameter 분포와 원본 사건을 다시 확인했는가?

Performance Schema

statements_digest `statements_digest` consumer와 statement instrumentation이 필요한 범위로 활성화되어 있는가?
counter snapshot을 누적값 그대로 비교하지 않고 시간 구간 delta로 계산하는가?
restart·truncate·failover를 별도 epoch로 처리하는가?
DIGEST=NULL `DIGEST=NULL` catch-all의 statement 비율을 감시하는가?
digest capacity와 긴 SQL text 보존 한계를 확인했는가?

개선과 검증

대표 sample 하나가 class 전체를 대표한다고 가정하지 않았는가?
index 추가 전에 DML·storage·buffer pool·replica 영향도 평가했는가?
운영 데이터 분포와 가까운 환경에서 실행 계획과 실제 행 수를 검증했는가?
배포 전후 calls, total time, tail, rows examined를 같은 기준으로 비교했는가?
개선 후 workload 증가나 routing 변화와 순수한 최적화 효과를 구분했는가?

보안과 보존

Slow Query Log와 sample SQL을 민감 운영 데이터로 취급하는가?
literal과 comment에 포함된 개인정보·token·업무 식별자를 외부 공유 전에 제거하는가?
분석 보고서, 원본 로그, review DB의 접근 권한과 보존 기간이 분리되어 있는가?
분석 도구의 version과 실행 command를 재현 가능한 manifest로 남겼는가?

12. 결론

Slow Query 분석의 핵심은 느린 SQL 몇 줄을 눈으로 찾는 일이 아니라, 사건을 안정적인 query class로 정규화하고 여러 비용 축으로 집계한 뒤 개선 생명주기를 관리하는 것이다. pt-query-digest는 보존된 Slow Query Log에서 총시간과 분포, 대표 사건을 분석하는 데 강하고, Performance Schema digest summary는 임계값 아래의 짧고 빈번한 SQL까지 현재 workload 전체에서 찾는 데 강하다.

두 관측면을 함께 사용하면 “한 번 가장 느렸던 SQL”과 “계속해서 가장 많은 시간을 소비한 SQL”을 구분할 수 있다. 다만 fingerprint가 parameter 차이를 감추고, 로그 임계값이 표본을 편향시키며, digest table에는 용량과 reset 경계가 있다는 사실을 항상 반영해야 한다.

다음 단계는 상위 query class의 대표 SQL을 실행 계획, optimizer statistics, 실제 행 수와 연결하는 것이다. 이때도 보고서의 순위를 결론으로 사용하지 말고, 조사 대상을 고르는 출발점으로 사용해야 한다.

1. 분석 단위를 사건에서 query class로 바꾼다

2. pt-query-digest가 처리하는 경로

2.1 response-time profile 읽는 순서

2.2 평균 하나로 판단하지 않는다

3. fingerprint의 경계와 주의점

3.1 같은 class 안에서 성능이 달라지는 이유

3.2 식별자를 도구 사이에서 직접 조인하지 않는다

4. Performance Schema digest로 현재 workload를 본다

4.1 총시간과 평균·최대 시간을 함께 조회한다

4.2 percentile과 sample은 조사 방향을 정하는 단서다

5. digest summary의 용량 한계를 감시한다

6. pt-query-digest와 Performance Schema를 결합하는 방법

6.1 일간 batch와 상시 관측을 분리한다

6.2 하나의 순위표만 만들지 않는다

7. 재현 가능한 일간 분석 runbook

7.1 입력 파일을 불변으로 만든다

7.2 동일 입력에서 여러 관점의 보고서를 만든다

7.3 검토 대상에 상태를 부여한다

8. 우선순위 결정 기준

8.1 영향도

8.2 개선 가능성

8.3 위험도

8.4 검증 가능성

9. 흔한 실패와 오해

9.1 “가장 느린 한 줄”만 고른다

9.2 Slow Query Log가 전체 workload라고 생각한다

9.3 보고서 순위가 매일 흔들리는데 즉시 회귀로 판단한다

9.4 fingerprint가 같으면 실행 계획도 같다고 가정한다

9.5 평균만 저장한다

9.6 digest table을 주기적으로 비우면서 epoch를 기록하지 않는다

9.7 DIGEST=NULL 행을 무시한다

9.8 sample SQL을 평문으로 장기 저장한다

10. Aurora MySQL에서의 운영 해석

11. 운영 체크리스트

수집 경계

분류와 집계

Performance Schema

개선과 검증

보안과 보존

12. 결론

9.7 `DIGEST=NULL` 행을 무시한다