chocomae/doc/plan/db/260915-improve-production/02-baseline.md

02단계: 개선 전 성능 측정 (베이스라인)

인덱스 추가 전 EXPLAIN과 실행 시간을 측정해 개선 효과의 기준점을 만듭니다. 04단계에서 같은 스크립트, 같은 쿼리, 같은 시각으로 다시 측정해 비교합니다.

모든 명령은 result/ 디렉터리에서 실행합니다.

cd doc/plan/db/260915-improve-production/result

---

측정 방식

원칙	이유
02와 04가 같은 쿼리 파일(queries/*.sql)을 실행	쿼리가 다르면 실행 시간을 비교할 수 없음
NOW() 대신 지난달의 시각을 고정 (params.sql)	새벽 작업 시간에는 현재 시각의 기록이 거의 없음. 지난달 데이터는 02와 04 사이에 바뀌지 않아 결과 행 수까지 비교 가능
같은 조건을 함수형과 범위형 두 가지로 측정	인덱스만의 효과(함수형)와 인덱스 + 쿼리 수정 효과(범위형)를 나눠 확인
쿼리 묶음을 2회 실행해 2회차 값 사용	1회차에는 디스크에서 페이지를 읽는 시간이 섞임
SQL_NO_CACHE	쿼리 캐시 결과로 시간이 왜곡되지 않게 함

쿼리 파일	조건	형태
q1_hour_func.sql	특정 1시간 기록	DATE(), HOUR() 함수 (수정 전 코드 형태)
q2_hour_range.sql	특정 1시간 기록	범위 조건 (Stage에서 수정한 코드 형태)
q3_day_func.sql	일간 랭킹	YEAR(), MONTH(), DAYOFMONTH() 함수
q4_day_range.sql	일간 랭킹	범위 조건
q5_month_func.sql	월간 랭킹	YEAR(), MONTH() 함수
q6_month_range.sql	월간 랭킹	범위 조건

---

2-0. 측정 기준 시각 정하기

MaestroID 181 · AppID 21의 지난달 기록 중 가장 많은 시간대를 찾습니다.

아래 명령어를 터미널에 복사-붙여넣기해서 실행하세요:

mariadb -h chocomae.jinaju.com -u jisangs -p chocomae -e "
SELECT DATE(RecordDateTime) AS day, HOUR(RecordDateTime) AS hour, COUNT(*) AS cnt
FROM best_record
WHERE MaestroID = 181 AND AppID = 21
  AND RecordDateTime >= CAST(DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01') AS DATETIME)
  AND RecordDateTime <  CAST(DATE_FORMAT(NOW(), '%Y-%m-01') AS DATETIME)
GROUP BY day, hour
ORDER BY cnt DESC
LIMIT 5;" > baseline_pick_time.txt

cat baseline_pick_time.txt

첫 번째 행의 day와 hour로 params.sql을 만듭니다. 아래 값은 예시이니 반드시 바꾸세요.

cat > params.sql << 'EOF'
SET @maestro = 181, @app = 21, @day = '2026-08-20', @hour = 14;
EOF

cat params.sql

04단계가 끝날 때까지 params.sql을 바꾸지 마세요.

---

2-1. Slow Query Log 켜기 (SUPER 권한)

SET GLOBAL은 SUPER 권한이 필요합니다. 2026-09-15에 jisangs 계정에 임시 부여했으므로 jisangs로 실행하면 됩니다 (mariadb -h chocomae.jinaju.com -u jisangs -p chocomae로 접속 후 아래 SQL 실행). 운영의 원래 값은 slow_query_log=ON, long_query_time=3, log_output=FILE이며(01 1-4), 05단계에서 이 값으로 복원합니다.

SET GLOBAL log_output = 'FILE,TABLE';
SET GLOBAL long_query_time = 0.5;
SET GLOBAL slow_query_log = 'ON';

SHOW GLOBAL VARIABLES WHERE Variable_name IN
  ('slow_query_log', 'long_query_time', 'log_output', 'log_queries_not_using_indexes');

• log_output = 'FILE,TABLE': 기존 파일 로그(/var/log/mysql/mariadb-slow.log)는 유지하면서 04단계에서 mysql.slow_log 테이블로 조회할 수 있게 합니다.
• long_query_time은 새 연결부터 적용됩니다. PHP는 요청마다 새로 연결하므로 바로 반영됩니다.
• ⚠️ log_queries_not_using_indexes는 켜지 마세요. 인덱스를 쓰지 않는 모든 쿼리가 실행 시간과 상관없이 기록되어 mysql.slow_log가 빠르게 커집니다.

---

2-2. 측정 파일 만들기

측정 쿼리 6개

쿼리 파일은 반드시 SELECT로 시작해야 합니다. 스크립트가 앞에 EXPLAIN FORMAT=JSON 을 붙여 실행하므로 주석을 넣지 마세요.

아래 명령어를 터미널에 복사-붙여넣기해서 실행하세요:

mkdir -p queries

cat > queries/q1_hour_func.sql << 'EOF'
SELECT SQL_NO_CACHE PlayerID, BestRecord, RecordDateTime
FROM best_record
WHERE MaestroID = @maestro AND AppID = @app
  AND DATE(RecordDateTime) = @day
  AND HOUR(RecordDateTime) = @hour
ORDER BY RecordDateTime, PlayerID;
EOF

cat > queries/q2_hour_range.sql << 'EOF'
SELECT SQL_NO_CACHE PlayerID, BestRecord, RecordDateTime
FROM best_record
WHERE MaestroID = @maestro AND AppID = @app
  AND RecordDateTime >= CAST(@day AS DATETIME) + INTERVAL @hour HOUR
  AND RecordDateTime <  CAST(@day AS DATETIME) + INTERVAL (@hour + 1) HOUR
ORDER BY RecordDateTime, PlayerID;
EOF

cat > queries/q3_day_func.sql << 'EOF'
SELECT SQL_NO_CACHE PlayerID, MAX(BestRecord) AS HighScore
FROM best_record
WHERE MaestroID = @maestro AND AppID = @app
  AND YEAR(RecordDateTime) = YEAR(@day)
  AND MONTH(RecordDateTime) = MONTH(@day)
  AND DAYOFMONTH(RecordDateTime) = DAYOFMONTH(@day)
GROUP BY PlayerID
ORDER BY HighScore DESC, PlayerID;
EOF

cat > queries/q4_day_range.sql << 'EOF'
SELECT SQL_NO_CACHE PlayerID, MAX(BestRecord) AS HighScore
FROM best_record
WHERE MaestroID = @maestro AND AppID = @app
  AND RecordDateTime >= CAST(@day AS DATETIME)
  AND RecordDateTime <  CAST(@day AS DATETIME) + INTERVAL 1 DAY
GROUP BY PlayerID
ORDER BY HighScore DESC, PlayerID;
EOF

cat > queries/q5_month_func.sql << 'EOF'
SELECT SQL_NO_CACHE PlayerID, MAX(BestRecord) AS HighScore
FROM best_record
WHERE MaestroID = @maestro AND AppID = @app
  AND YEAR(RecordDateTime) = YEAR(@day)
  AND MONTH(RecordDateTime) = MONTH(@day)
GROUP BY PlayerID
ORDER BY HighScore DESC, PlayerID;
EOF

cat > queries/q6_month_range.sql << 'EOF'
SELECT SQL_NO_CACHE PlayerID, MAX(BestRecord) AS HighScore
FROM best_record
WHERE MaestroID = @maestro AND AppID = @app
  AND RecordDateTime >= CAST(DATE_FORMAT(@day, '%Y-%m-01') AS DATETIME)
  AND RecordDateTime <  CAST(DATE_FORMAT(@day, '%Y-%m-01') AS DATETIME) + INTERVAL 1 MONTH
GROUP BY PlayerID
ORDER BY HighScore DESC, PlayerID;
EOF

ls queries/

측정 스크립트

비밀번호를 한 번만 입력받아 EXPLAIN 6개와 실행 시간을 모두 측정합니다. 비밀번호는 파일이나 명령 인자로 남지 않습니다.

아래 명령어를 터미널에 복사-붙여넣기해서 실행하세요:

cat > run_measure.sh << 'EOF'
#!/bin/zsh
# 02·04단계 공통 측정 스크립트
# 사용법: zsh run_measure.sh baseline   (02단계, 인덱스 추가 전)
#         zsh run_measure.sh after      (04단계, 인덱스 추가 후)

PREFIX=$1
if [[ $PREFIX != baseline && $PREFIX != after ]]; then
  echo "사용법: zsh run_measure.sh baseline|after" >&2; exit 1
fi
cd "${0:A:h}" || exit 1

printf "Enter password: "; read -rs DBPW; echo

db() {
  mariadb --defaults-extra-file=<(printf '[client]\npassword="%s"\n' "$DBPW") \
    -h chocomae.jinaju.com -u jisangs chocomae "$@"
}

# 1) EXPLAIN: 쿼리마다 JSON 파일 1개
for q in queries/*.sql; do
  out="${PREFIX}_explain_${q:t:r}.json"
  { cat params.sql; printf 'EXPLAIN FORMAT=JSON '; cat "$q"; } | db -N -r > "$out" \
    || { echo "EXPLAIN 실패: $q" >&2; exit 1; }
  echo "저장: $out"
done

# 2) 실행 시간: 같은 쿼리 묶음을 2회 실행 (2회차 값 사용)
for round in 1 2; do
  { cat params.sql
    for q in queries/*.sql; do
      echo "SELECT '=== round ${round}: ${q:t:r} ===' AS msg;"
      cat "$q"
    done
  } | db -vvv || { echo "실행 시간 측정 실패" >&2; exit 1; }
done > "${PREFIX}_perf.txt"
echo "저장: ${PREFIX}_perf.txt"

# 3) 요약: 쿼리 이름 / 결과 행 수 / 실행 시간 (2회차)
echo
grep -E '^\| === |rows? in set|Empty set' "${PREFIX}_perf.txt" | paste - - - | grep 'round 2' \
  | sed -E 's/\| === round 2: (.*) === \|/\1/; s/\t1 row in set \([0-9.]+ sec\)//'
EOF

EXPLAIN 요약 스크립트

EXPLAIN JSON에서 access_type, 사용 인덱스, rows만 뽑아 한 줄씩 보여줍니다.

아래 명령어를 터미널에 복사-붙여넣기해서 실행하세요:

cat > summarize_explain.py << 'EOF'
# 사용법: python3 summarize_explain.py baseline [after]
import glob
import json
import sys


def find_table(node):
    if isinstance(node, dict):
        if isinstance(node.get("table"), dict):
            return node["table"]
        children = node.values()
    elif isinstance(node, list):
        children = node
    else:
        return None
    for child in children:
        found = find_table(child)
        if found:
            return found
    return None


def keys_of(node):
    if isinstance(node, dict):
        for k, v in node.items():
            if k == "key":
                yield v
            else:
                yield from keys_of(v)
    elif isinstance(node, list):
        for v in node:
            yield from keys_of(v)


for prefix in sys.argv[1:]:
    for path in sorted(glob.glob(f"{prefix}_explain_*.json")):
        with open(path, encoding="utf-8") as f:
            table = find_table(json.load(f))
        key = table.get("key") or " ∩ ".join(keys_of(table.get("index_merge", {}))) or "-"
        print(f"{path:40} access_type={str(table.get('access_type')):12} key={key:28} rows={table.get('rows')}")
EOF

---

2-3. 베이스라인 측정 실행

아래 명령어를 터미널에 복사-붙여넣기해서 실행하세요:

zsh run_measure.sh baseline

출력 예 (숫자는 예시):

Enter password:
저장: baseline_explain_q1_hour_func.json
...
저장: baseline_explain_q6_month_range.json
저장: baseline_perf.txt

q1_hour_func	38 rows in set (0.052 sec)
q2_hour_range	38 rows in set (0.049 sec)
q3_day_func	21 rows in set (0.061 sec)
q4_day_range	21 rows in set (0.058 sec)
q5_month_func	96 rows in set (0.410 sec)
q6_month_range	96 rows in set (0.395 sec)

확인할 점:

• 함수형과 범위형 짝(q1↔q2, q3↔q4, q5↔q6)의 결과 행 수가 같아야 합니다. 다르면 범위 조건이 잘못된 것이니 03단계로 가지 말고 원인을 확인하세요.
• Empty set이면 그 시각에 기록이 없는 것이니 2-0에서 다른 시각을 고르세요.
• 비밀번호를 틀리면 Access denied와 함께 멈춥니다. 다시 실행하세요.

---

2-4. EXPLAIN 요약

아래 명령어를 터미널에 복사-붙여넣기해서 실행하세요:

python3 summarize_explain.py baseline | tee baseline_explain_summary.txt

운영 예상: 01단계 사전 측정처럼 index_merge(MaestroID ∩ AppID)나 단일 인덱스 접근이 나오고, rows는 수천~1만 행 수준입니다. 전체 스캔(ALL)이 아닐 수 있으니 나온 값을 그대로 기준값으로 기록하세요.

---

2-4-1. 서버 측 실행 시간 (ANALYZE)

2-3의 실행 시간은 Mac ↔ AWS 네트워크 왕복이 포함된 클라이언트 시간입니다. 서버가 쿼리를 처리한 시간(r_total_time_ms)을 따로 기록해 두면 네트워크 영향 없이 비교할 수 있습니다.

운영 측정 (2026-09-15): 클라이언트 시간 약 90ms 중 서버 처리 시간이 약 75ms였습니다. 6개 쿼리 모두 index_merge로 1만여 행을 읽기 때문입니다. ANALYZE는 쿼리를 실제로 실행하지만 조회(SELECT)만 하므로 데이터는 바뀌지 않습니다.

요약 스크립트 만들기:

cat > summarize_analyze.py << 'EOF'
# 사용법: python3 summarize_analyze.py baseline [after]
import json
import sys

decoder = json.JSONDecoder()
for prefix in sys.argv[1:]:
    text = open(f"{prefix}_analyze.txt", encoding="utf-8").read()
    names = [line.split("=== ")[1].split(" ===")[0] for line in text.splitlines() if line.startswith("=== ")]
    docs, pos = [], 0
    while True:
        start = text.find("{", pos)
        if start < 0:
            break
        doc, pos = decoder.raw_decode(text, start)
        docs.append(doc)
    for name, doc in zip(names, docs):
        block = doc["query_block"]
        print(f"{prefix:8} {name:16} server_time_ms={block.get('r_total_time_ms')}")
EOF

측정 (비밀번호 한 번 입력):

{ cat params.sql
  for q in queries/*.sql; do
    echo "SELECT '=== ${q:t:r} ===';"
    printf 'ANALYZE FORMAT=JSON '
    cat "$q"
  done
} | mariadb -h chocomae.jinaju.com -u jisangs -p chocomae -N -r > baseline_analyze.txt

python3 summarize_analyze.py baseline | tee baseline_analyze_summary.txt

---

2-5. 결과 기록

파일로 저장된 결과:

params.sql, queries/*.sql            ← 04단계에서 그대로 재사용 (수정 금지)
run_measure.sh, summarize_explain.py ← 04단계에서 그대로 재사용
baseline_pick_time.txt               ← 측정 기준 시각 선정 근거
baseline_explain_q*.json (6개)       ← EXPLAIN 결과
baseline_explain_summary.txt         ← EXPLAIN 요약
baseline_perf.txt                    ← 실행 시간 (-vvv 전체 출력, 네트워크 포함)
summarize_analyze.py                 ← 04단계에서 그대로 재사용
baseline_analyze.txt                 ← ANALYZE 결과 (서버 측 실행 통계)
baseline_analyze_summary.txt         ← 서버 측 실행 시간 요약

2-3 요약과 2-4 결과는 04단계 4-4의 comparison.txt에 옮겨 적습니다.

---

다음 단계

✅ 베이스라인 측정 완료 → 03-add-indexes.md로 이동