본 문서는 DB 성능 저하(CPU 급증, 락 대기 등) 발생 시, 원인이 되는 쿼리를 식별하고 안전하게 격리(Kill)하는 표준 운영 절차를 기술한다.

1. 현상 파악 및 접근 전략 (CPU vs Lock)

가장 먼저 장애의 성격이 “CPU 부하”인지 “Lock 대기”인지 구분해야 한다.

• CPU 급증 (Computation Bound):
  • 대규모 Full Table Scan, Filesort(디스크 정렬), Temporary Table 생성, 인덱스를 타지 않는 비효율 조인(Block Nested Loop 등)이 주원인이다.
  • 대응: 해당 악성 쿼리 식별 후 종료.
• Active Session 급증 (Lock/Wait Bound):
  • 특정 세션이 Row/Table Lock을 점유하고 놓아주지 않아, 후속 트랜잭션들이 Waiting 상태로 적체되는 현상.
  • 대응: Waiting 상태인 쿼리(피해자)가 아니라, **Lock을 점유 중인 원인 세션(가해자/Blocker)**을 찾아 종료해야 한다.

운영 원칙: Lock 대기 상황에서 무작정 Waiting 세션을 종료하는 것은 근본 해결책이 아니다. 반드시 Blocker를 식별한다.

2. 정밀한 Slow Query 식별 (Processlist)

SHOW PROCESSLIST 대신 필터링이 가능한 INFORMATION_SCHEMA를 활용한다. 부하를 최소화하기 위해 필요한 컬럼만 조회하며, 조건절을 정교하게 제어한다.

2-1. 기본 식별 쿼리 (안전 모드)

일반적인 악성 쿼리(Query 상태)를 식별한다.

SELECT 
    ID, USER, HOST, DB, COMMAND, TIME, STATE, 
    LEFT(INFO, 100) AS INFO_SHORT
FROM 
    INFORMATION_SCHEMA.PROCESSLIST 
WHERE 
    COMMAND = 'Query'              -- 기본적으로 Query 상태 필터링
    AND USER NOT IN ('system user') -- 시스템 스레드 제외
    AND ID <> CONNECTION_ID()       -- 자기 자신(Current Session) 제외
    AND TIME > 10                   -- 10초 초과 (상황에 따라 3~5초 조정)
    AND INFO IS NOT NULL            -- 쿼리 텍스트가 있는 경우만 (아래 주의사항 참조)
ORDER BY 
    TIME DESC;

2-2. 정밀 분석 시 주의사항 (Empty Info & Execute)

위 쿼리로 잡히지 않는 경우, 다음 환경적 변수를 고려하여 조건을 완화해야 한다.

1. Prepared Statement / Binary Protocol: 실행 중이나 SQL 텍스트(INFO)가 NULL로 보이는 경우가 있다. 쿼리가 안 보인다고 부하가 없는 것이 아니므로, INFO IS NOT NULL 조건을 빼고 TIME과 STATE만으로 판단해야 할 수 있다.
2. COMMAND 상태 값: 일부 드라이버나 프록시 환경에서는 Execute 상태로 표시될 수 있다. 필요 시 COMMAND IN ('Query', 'Execute')으로 확장한다.

3. 단계적 조치 (Kill Strategy)

애플리케이션의 커넥션 풀 에러를 방지하고 트랜잭션 안전성을 확보하기 위해 “쿼리 중단 → 연결 종료” 순서로 단계를 격상(Escalation)한다.

Step 1: KILL QUERY (쿼리만 중단)

[권장 1순위] 연결은 유지하되 실행 중인 SQL만 멈춘다. 애플리케이션은 쿼리 실패로 인지하고 로직에 따라 재시도하거나 에러 처리를 할 수 있다.

KILL QUERY [ID];

Step 2: KILL (연결 강제 종료)

Step 1이 작동하지 않거나(드라이버 특성 등), 좀비 세션이거나, 쿼리가 반복 재실행되는 경우 사용한다. MySQL/MariaDB 범용 문법인 KILL [ID]를 사용한다.

KILL [ID]; -- 해당 세션의 Connection을 끊음

Step 3: 일괄 종료 (Whitelist 방식)

다수의 세션을 정리할 때, “지우면 안 되는 것”을 빼는 방식(Blacklist)보다 **”확실한 타겟(애플리케이션 계정)”만 지정하는 방식(Whitelist)**이 운영 사고를 방지한다.

-- [안전] 특정 애플리케이션 계정(app_user)의 30초 이상 쿼리만 타겟팅
SELECT 
    CONCAT('KILL QUERY ', ID, ';') AS kill_cmd
FROM 
    INFORMATION_SCHEMA.PROCESSLIST 
WHERE 
    USER IN ('app_svc_user', 'web_api_user') -- [필수] 타겟 계정 화이트리스트
    AND COMMAND = 'Query'
    AND TIME > 30
    AND ID <> CONNECTION_ID();

4. Rollback 리스크 및 상태 추적

KILL 명령 수행 후 프로세스가 즉시 사라지지 않는 경우의 대부분은 Rollback 때문이다.

1. 현상: KILL을 수행했음에도 세션이 남아있고 COMMAND가 Killed 또는 Query 상태로 유지됨.
2. 원인: DML(Update, Insert, Delete) 작업 중 강제 종료 시, InnoDB는 데이터 정합성을 위해 변경분을 되돌리는(Undo) 작업을 수행한다.
3. 대응:
   • 추가적인 KILL 명령은 대부분 무의미하다. Rollback이 완료될 때까지 기다려야 한다.
   • 상태 확인 쿼리:
     SHOW ENGINE INNODB STATUS;
-- Output의 TRANSACTIONS 섹션에'ROLLING BACK' 상태 및 진행률 확인
   • 예외: KILL QUERY만 수행했는데 쿼리는 멈췄으나 세션이 살아있고, 해당 세션이 다른 쿼리를 수행하려 한다면 KILL [ID]로 격상하여 연결을 끊는다.

5. 근본 원인 분석 (Post-Mortem)

장애 해소 후 재발 방지를 위한 분석 단계다.

5-1. Blocker(가해자) 추적

Lock 대기 상황이었다면 Waiting 세션 정보는 무의미하다. 누가 잡고 있었는지 확인해야 한다.

• MySQL 8.0+: performance_schema의 락 테이블 조합이 표준이다.

SELECT
w.requesting_engine_transaction_id AS waiting_trx_id,
w.blocking_engine_transaction_id AS blocking_trx_id,
— w.blocking_thread_id 등을 이용해 processlist와 조인하여 유저/Host 식별
t.processlist_user, t.processlist_host
FROM
performance_schema.data_lock_waits w
JOIN
performance_schema.threads t ON w.blocking_thread_id = t.thread_id;

• (참고: sys.innodb_lock_waits 뷰가 설치된 환경이라면 더 쉽게 조회 가능)
• MySQL 5.7 / MariaDB: information_schema.innodb_lock_waits, innodb_trx 등을 조인하여 확인.

5-2. Slow Query Log 설정 (단기 트러블슈팅)

상시 설정보다는 분석 기간에만 한시적으로 적용하거나, 로그 폭주 방지 옵션을 반드시 병행한다.

# my.cnf (Dynamic 변경 가능 여부 확인 필요)
log_output = FILE                  # TABLE은 부하 유발 가능성 있음
slow_query_log = 1
long_query_time = 2                # 2초 이상
log_queries_not_using_indexes = 1  # [주의] 인덱스 미사용 쿼리 기록. 로그 양 폭증 가능.
min_examined_row_limit = 1000      # [필수] 1000행 이상 스캔한 경우만 기록하여 노이즈 제거

주의: log_queries_not_using_indexes는 디스크 I/O를 유발하여 장애를 악화시킬 수 있으므로, min_examined_row_limit과 함께 사용하거나 트러블슈팅 종료 후 반드시 OFF 해야 한다.

🛠 마지막 수정일: 2025.12.31