시스템 관리_40 슬로우 쿼리 원인 분석 인덱스 누락 풀스캔 대 스토리지 디스크 I/O 지연 식별 및 튜닝 가이드

# 1. OS 수준 종합 점검: CPU가 연산 중인지 디스크 I/O 대기(%wa)에 갇혀 있는지 확인 (1초 간격 3회)
$ vmstat 1 3
# procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
#  r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
#  1  4      0 1254300  45210 18542000    0    0 45200   120 1204 3500 12  4 10 74  0
# -> b(Blocked 프로세스 수)가 증가하고 wa(iowait)가 70%를 상회하면 디스크 I/O 지연 상태임

# 2. 스토리지 계층 정밀 진단: 특정 디스크의 응답 대기 시간(await) 및 가동률(%util) 측정
$ iostat -xz 1 3
# Device            r/s     w/s     rkB/s     wkB/s   rrqm/s   wrqm/s  r_await  w_await   aqu-sz  %util
# sdb1          4512.00   12.00 144384.00    120.00     0.00     0.00    28.50     1.20    15.40  99.80
# -> 디스크 가동률(%util)이 100%에 근접하고 읽기 지연(r_await)이 20ms 이상으로 높다면 물리 디스크 병목 상태임

# 3. DB 프로세스별 디스크 I/O 전용 점검 (pidstat)
$ sudo pidstat -d 1 3
# 14:10:02      UID       PID   kB_rd/s   kB_wr/s kB_ccwr/s iodelay  Command
# 14:10:03      999     14502 142100.00     10.00      0.00      85  postgres
# -> 특정 DB 프로세스가 초당 수백 MB씩 디스크에서 블록을 읽고 있음 확인

# 4. 데이터베이스 내부 확인 (PostgreSQL 예시): EXPLAIN ANALYZE를 통한 인덱스 누락 검증
$ psql -U postgres -d mydb -c "
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM orders WHERE customer_id = 984512 AND order_status = 'SHIPPED';
"
# [출력 예시]
# Seq Scan on orders  (cost=0.00..845120.00 rows=15 width=128) (actual time=1240.12..4812.30 rows=2 loops=1)
#   Filter: ((customer_id = 984512) AND (order_status = 'SHIPPED'::text))
#   Rows Removed by Filter: 15420000
#   Buffers: shared hit=1200 read=645000
# Execution Time: 4815.120 ms
# -> Seq Scan(풀스캔)으로 1,500만 건을 디스크 블록(read=645,000)에서 직접 읽어 지연 발생 (인덱스 부재 확정)

#!/bin/bash
# diagnose_db_slowdown_cause.sh - Distinguish Missing Index (Full Scan) vs High Disk Latency

echo "=========================================="
echo " [슬로우 쿼리 원인 진단: 인덱스 누락 vs 디스크 지연]"
echo "=========================================="

# 1. 필수 도구 점검
for CMD in iostat vmstat pidstat; do
    if ! command -v "$CMD" &>/dev/null; then
        echo "  [오류] $CMD 명령어가 설치되어 있지 않습니다. (sysstat 패키지 필요)"
        exit 1
    fi
done

# 2. CPU iowait 분석
echo -e "\n[1] CPU I/O 대기율(iowait) 점검 중 (3초 샘플링)..."
VM_DATA=$(vmstat 1 3 | tail -n 1)
CPU_US=$(echo "$VM_DATA" | awk '{print $13}')
CPU_SY=$(echo "$VM_DATA" | awk '{print $14}')
CPU_ID=$(echo "$VM_DATA" | awk '{print $15}')
CPU_WA=$(echo "$VM_DATA" | awk '{print $16}')

echo "  - User CPU  : ${CPU_US}%"
echo "  - System CPU: ${CPU_SY}%"
echo "  - Idle CPU  : ${CPU_ID}%"
echo "  - I/O Wait  : ${CPU_WA}%"

# 3. 디스크별 Latency(await) 및 Util 점검
echo -e "\n[2] 스토리지 디스크 응답 지연(Latency) 및 포화도 점검:"
IOSTAT_OUT=$(iostat -xz 1 2 | awk '/Device/ {found=1; next} found {print}' | tail -n +2)

HIGH_LATENCY_FOUND=0
HIGH_UTIL_FOUND=0

while read -r DEV R_S W_S RK_S WK_S RRQM WRQM R_AWAIT W_AWAIT AQU_SZ UTIL; do
    [ -z "$DEV" ] && continue
    echo "  - 디스크: $DEV | 읽기응답(r_await): ${R_AWAIT}ms | 쓰기응답(w_await): ${W_AWAIT}ms | 점유율(%util): ${UTIL}%"
    
    # NVMe/SSD 기준 10ms 초과 시 지연, HDD 기준 25ms 초과 시 지연 판정
    IS_LATENCY=$(echo "$R_AWAIT > 15.0 || $W_AWAIT > 15.0" | bc -l 2>/dev/null)
    IS_UTIL=$(echo "$UTIL > 90.0" | bc -l 2>/dev/null)
    
    [ "$IS_LATENCY" -eq 1 ] && HIGH_LATENCY_FOUND=1
    [ "$IS_UTIL" -eq 1 ] && HIGH_UTIL_FOUND=1
done <<< "$IOSTAT_OUT"

# 4. DB 프로세스별 I/O 점유 분석
echo -e "\n[3] 데이터베이스 프로세스 초당 읽기/쓰기량 추적:"
DB_IO=$(pidstat -d 1 1 | grep -E "postgres|mysqld|oracle|sqlservr" | head -n 3)
if [ -n "$DB_IO" ]; then
    echo "$DB_IO" | awk '{printf "  -> PID: %-6s | 명령: %-10s | 초당 읽기: %-10s KB/s | 초당 쓰기: %-10s KB/s\n", $4, $9, $5, $6}'
else
    echo "  - 활성화된 DB 프로세스 I/O가 미미합니다."
fi

# 5. 진단 판정 결과 도출
echo -e "\n=========================================="
echo " [진단 결과 요약]"
echo "=========================================="

if [ "$CPU_WA" -ge 30 ] && [ "$HIGH_LATENCY_FOUND" -eq 1 ]; then
    echo "  ★ 판정: [스토리지 디스크 지연 (High Disk Latency)]"
    echo "  - 사유: CPU의 iowait(${CPU_WA}%)가 높고 디스크 await 응답 시간이 15ms를 초과했습니다."
    echo "  - 조치: 스토리지 IOPS 용량 초과, RAID 컨트롤러 캐시 배터리 이상, SAN/NAS 네트워크 지연 점검."
elif [ "$CPU_US" -ge 60 ] && [ "$CPU_WA" -lt 15 ]; then
    echo "  ★ 판정: [인덱스 누락 및 풀 스캔 (Missing Index / In-Memory Scan)]"
    echo "  - 사유: 디스크 지연은 낮으나 CPU User 점유율(${CPU_US}%)이 비정상적으로 높습니다."
    echo "  - 설명: DB 메모리 버퍼(Buffer Cache) 안에서 인덱스 없이 수천만 건을 뒤지는 풀스캔이 발생 중입니다."
    echo "  - 조치: 슬로우 쿼리 로그 및 EXPLAIN 실행 계획을 확인하여 적절한 복합 인덱스를 생성하십시오."
elif [ "$CPU_WA" -ge 20 ] && [ "$HIGH_LATENCY_FOUND" -eq 0 ]; then
    echo "  ★ 판정: [인덱스 누락에 의한 디스크 물리 읽기 폭증]"
    echo "  - 사유: 스토리지는 빠르나 DB 버퍼 크기를 초과하는 대규모 테이블 풀스캔으로 물리 I/O가 과점되었습니다."
    echo "  - 조치: 쿼리 필터 컬럼 인덱스 추가 및 Buffer Cache(SGA/InnoDB Buffer Pool) 증설 검토."
else
    echo "  ★ 판정: [정상 또는 일시적 스파이크]"
    echo "  - 심각한 디스크 지연이나 CPU 병목 징후가 관측되지 않았습니다."
fi
echo "=========================================="