[DB] mysql 부하 확인 _show processlist/slow_log

[MySQL/MariaDB] DB CPU 70% 폭주 원인 분석과 Slow Query 트러블슈팅

웹사이트 지연 장애 발생 시 SHOW PROCESSLIST 및 slow_log(BLOB 변환)를 활용한 실무 해결 가이드

웹서비스 운영 중 अचानक 사이트 접속이 느려지거나 500 에러가 간헐적으로 발생한다는 제보를 받았습니다. DB 모니터링을 즉시 확인해보니 DB Server의 CPU 점유율이 70% 이상까지 급증한 상태였습니다.

실제 장애 현장에서 어떤 순서로 원인을 추적하고 해결했는지 단계별로 정리해 둡니다.

1. 현재 실행 중인 스레드 확인 (SHOW PROCESSLIST)

가장 먼저 DB에서 병목을 일으키는 쿼리가 무엇인지 확인하기 위해 현재 실행 중인 스레드 목록을 조회했습니다.

SHOW PROCESSLIST;

출력 주요 컬럼: ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO

• 조회 결과, TIME이 100초 이상 지속되며 실행 중인 스레드가 다수 발견되었습니다.
• COMMAND가 Query인 항목의 INFO 컬럼을 보니 특정 SELECT 문이 문제였습니다.

💡 TIP: 긴 쿼리가 중간에 잘려서 보일 때
기본 SHOW PROCESSLIST;는 INFO 컬럼의 긴 쿼리를 일부만 보여줍니다. 이때 SHOW FULL PROCESSLIST;를 사용하면 잘림 없이 전체 쿼리문 확인이 가능합니다.

⚠️ 주의: KILL {ID} 명령어 사용 시 참고할 점
부하를 급하게 줄이기 위해 문제 스레드를 강제 종료(KILL ID;)할 수 있습니다. 단, INSERT, UPDATE, DELETE 같은 CUD 작업은 강제 종료 시 트랜잭션 롤백 지연이나 데이터 불일치 위험이 있으므로, 단순 조회(SELECT)문인지 반드시 확인 후 신중하게 작업해야 합니다.

2. Slow Log 조회 및 BLOB 쿼리 변환

실행 중인 쿼리의 원본을 정확히 확보하고, 얼마나 많은 레코드를 스캔했는지(rows_examined) 확인하기 위해 mysql.slow_log 테이블을 조회했습니다.

SELECT * FROM mysql.slow_log;

하지만 slow_log 테이블의 sql_text 컬럼이 BLOB 타입으로 지정되어 있어 일반적인 조회로는 쿼리 본문을 바로 알아보기 어렵습니다.

이런 경우 CAST 함수를 사용해 문자열로 인코딩 변환을 해주면 명확하게 확인할 수 있습니다.

SELECT 
    start_time,
    user_host,
    query_time,
    lock_time,
    rows_sent,
    rows_examined,
    db,
    CAST(sql_text AS CHAR(10000) CHARACTER SET utf8) AS sql_text_str
FROM mysql.slow_log
ORDER BY start_time DESC;

3. 원인 분석 및 인덱스 조치

Slow Log 조회를 통해 확보한 SELECT 쿼리를 분석한 결과 다음과 같은 원인이 파악되었습니다.

1. Full Table Scan 발생: 인덱스(Index)가 타지 않아 rows_examined(스캔한 행 수)가 폭발적으로 증가.
2. 스레드 병목 현상: 실행 시간이 100초 이상 걸리는 쿼리가 동시다발적으로 유입되며 DB CPU 사용률이 70%까지 상승.

🛠️ 조치 사항:

• EXPLAIN 명령어로 쿼리 실행 계획을 점검한 뒤 적절한 인덱스 추가
• 조건절 불필요 스캔 방지를 위한 쿼리 리팩토링 진행
• 적용 후 DB CPU 점유율이 정상 수치로 회복되었고 사이트 응답 속도도 정상화 완료

📌 요약 & Lesson Learned

• 장애 인지: 서비스 지연 시 DB CPU 점유율 및 스레드 체류 시간(TIME)부터 체크하자.
• SHOW FULL PROCESSLIST: 잘린 쿼리는 FULL 옵션으로 확인한다.
• Slow Log BLOB 변환: CAST(sql_text AS CHAR(10000) CHARACTER SET utf8)로 변환하여 본문 확인.
• 인덱스 최적화: 장시간 실행되는 SELECT문은 인덱스 누락이나 Full Table Scan 여부를 반드시 점검한다.