chocomae/doc/plan/db/260915-improve-production/00-overview.md

# 운영 서버 DB 성능 개선 (260915-improve-production)

> 스테이징에서 성공한 인덱스 추가를 운영 서버에 적용합니다.

---

## 📋 개요

| 항목 | 내용 |
|---|---|
| **목표** | 복합 인덱스 추가로 랭킹·기록 조회 쿼리의 검사 행 수와 실행 시간 감소 |
| **대상 DB** | chocomae.jinaju.com (AWS EC2 Docker, MariaDB 10.11.13) |
| **작업 방식** | 온라인 DDL (ALGORITHM=INPLACE, LOCK=NONE) |
| **예상 시간** | 약 1시간 (백업·측정·검증 포함, 인덱스 생성은 여유 있게 30분) |
| **위험도** | 인덱스 추가: 낮음 (스테이징 검증 완료) / 중복 정리: 중간 (운영 데이터 삭제, 백업 후 진행) |
| **영향 범위** | 인덱스: 쿼리 응답 시간만 개선 (서비스 무중단) / 중복 정리: `app_highest_record`의 중복 행 삭제 |

---

## 📏 운영 사전 측정 결과 (01단계, 2026-09-15)

| 항목 | 값 | 비고 |
|---|---|---|
| best_record | 약 115만 행, data 72.5MB / index 72.1MB | 월별 합계 1,210,482행 |
| typing_exam_record | 약 13만 행, data 10.3MB / index 10.9MB | |
| 월 적재량 | 최근 3개월(6~8월) 평균 57,968행/월 | 전년 같은 기간(27,049행)의 약 2.1배 |
| 일간 랭킹 EXPLAIN | `index_merge` (MaestroID ∩ AppID), rows **10,100** | 전체 스캔(ALL)이 아님 |
| app_highest_record 중복 | **4,823조합, 삭제 대상 8,795행** (약 3.8%) | 03-0에서 점수 기준으로 정리 |
| typing_exam_highest_record 중복 | 0건 | |
| jisangs 계정 권한 | `SELECT, ALTER` + 임시 부여(SUPER, 최고기록 테이블 2개 INSERT·DELETE, `mysql.slow_log` SELECT) | 05단계에서 회수 |
| Slow query log | ON, long_query_time=3, log_output=FILE | 05단계 복원 기준값 |

> 운영 DB에는 `MaestroID`, `AppID`, `PlayerID` 단일 인덱스가 이미 있어 스테이징(type=ALL)과 기준값이 다릅니다. 개선율은 스테이징 수치가 아니라 **운영에서 측정한 02(베이스라인)와 04(검증) 결과**로 계산합니다.

상세: [result/premeasure_summary.txt](result/premeasure_summary.txt)

---

## 🎯 기대 효과

| 항목 | 스테이징 결과 | 운영 예상 |
|---|---|---|
| **EXPLAIN cost** | 8.63 → 0.004 (**2,158배 ⬇️**) | MariaDB 10.11 EXPLAIN JSON에는 cost가 없어 rows·실행 시간으로 비교 |
| **EXPLAIN rows** | 5,804 → 1 (**5,800배 ⬇️**) | 10,100 → 해당 시간·날짜의 실제 기록 수 수준 (범위 조건 쿼리) |
| **접근 방식** | ALL → range | index_merge → range |
| **실행 시간** | ~2초 (충분) | 02·04에서 측정 |
| **느린 쿼리 제거** | 0.5초 이상 → 없음 | 04-5에서 확인 |

---

## 📊 스테이징 결과 요약

### ✅ 완료한 작업
1. 베이스라인 측정: 전체 스캔 (type=ALL)
2. 중복 데이터 정리: app_highest_record, typing_exam_highest_record
3. 인덱스 5개 + UNIQUE 키 2개 추가 (총 7개)
4. **쿼리 조건 수정**: 7개 파일, 8+ 함수 (DATE()/HOUR() → 범위 조건)
5. 개선 효과 검증: 2,000배 이상 향상 확인 (인덱스 + 쿼리 수정)
6. Slow query log: 개선 쿼리 0.5초 이하

### 📈 성능 개선 상세 (Stage 검증)
- **인덱스만 적용:** cost 8.63 → 1.0 (8배), rows 5,804 → 200 (29배)
- **인덱스 + 쿼리 수정:** cost 8.63 → 0.004 (2,158배), rows 5,804 → 1 (5,800배)

### 📁 저장된 파일
```
260915-improve-stage/result/
├── baseline_query1_explain.json
├── baseline_query2_explain.json
├── baseline_results.txt
├── after_query1_explain.json
├── after_query2_improved_explain.json
├── verify_results.txt
└── add_indexes.sql
```

---

## 🔐 운영 DB 접속 정보

```
# 운영 DB (AWS EC2 Docker)
Host: chocomae.jinaju.com
Port: 3306
Database: chocomae
User: jisangs (임시 계정 — 2026-09-15 작업 후 삭제)
Password: 문서에 적지 않음 (별도로 전달받은 값 사용)
출발지 IP: 182.217.174.221

# 부여된 권한 (2026-09-15 SHOW GRANTS로 확인)
GRANT SELECT, ALTER ON chocomae.* TO jisangs@'182.217.174.221'

# 작업 기간 임시 권한 (2026-09-15 부여 → 같은 날 회수 완료, result/grants_after_revoke.txt)
GRANT SUPER ON *.* TO jisangs@'182.217.174.221'
GRANT INSERT, DELETE ON chocomae.app_highest_record TO jisangs@'182.217.174.221'
GRANT INSERT, DELETE ON chocomae.typing_exam_highest_record TO jisangs@'182.217.174.221'
GRANT SELECT ON mysql.slow_log TO jisangs@'182.217.174.221'
```

> ✅ 임시 계정 `jisangs`는 작업 후 삭제했습니다 (2026-09-15).
>
> ⚠️ 같은 비밀번호가 예전 커밋 기록과 메일(Gmail SMTP)·Python 배치(DB root) 소스 코드에도 평문으로 남아 있습니다. 계정 삭제와 별개로 그 비밀번호들은 바꿔야 합니다.

### 단계별 필요 권한

| 단계 | 작업 | 필요 권한 | jisangs 계정으로 가능? |
|---|---|---|---|
| 01, 02, 04 | 조회, EXPLAIN | SELECT | ✅ |
| 03-0 | 삭제 대상 행 백업 (`mariadb-dump`) | SELECT | ✅ |
| 03-0 | 중복 행 삭제 | `app_highest_record` DELETE | ✅ 임시 부여 |
| 03-2 | 인덱스·UNIQUE 키 추가 | ALTER | ✅ |
| 02-1, 05 | Slow query log 켜기·복원 (`SET GLOBAL`) | SUPER | ✅ 임시 부여 |
| 04-5, 07 | `mysql.slow_log` 조회 | `mysql.slow_log` SELECT | ✅ 임시 부여 |
| 06 | 삭제 행 복원 | 최고기록 테이블 INSERT | ✅ 임시 부여 |
| 06 | `ANALYZE TABLE best_record`, 복제 상태 확인 | `best_record` INSERT, REPLICA MONITOR 등 | ❌ 관리자 계정 |

2026-09-15에 관리자 계정으로 아래 권한을 임시 부여했습니다 (확인: `result/premeasure_grants_after.txt`). ❌ 작업은 문제가 생겼을 때만 필요하며 관리자 계정으로 실행합니다. 임시 권한은 05단계 사후 작업에서 회수합니다.

```sql
-- 관리자 계정으로 실행함 (2026-09-15)
GRANT DELETE, INSERT ON chocomae.app_highest_record TO 'jisangs'@'182.217.174.221';
GRANT DELETE, INSERT ON chocomae.typing_exam_highest_record TO 'jisangs'@'182.217.174.221';
GRANT SELECT ON mysql.slow_log TO 'jisangs'@'182.217.174.221';
GRANT SUPER ON *.* TO 'jisangs'@'182.217.174.221';
```

> `SUPER`는 서버 전체 권한(다른 연결의 쿼리 중단, 모든 전역 설정 변경, read_only 우회)이므로 작업이 끝나면 바로 회수하세요.

---

## ⚠️ 주의사항

1. **운영 DB 백업 필수**
   - EC2 EBS 스냅샷 생성 (MariaDB 데이터 볼륨 포함)
   - 또는 서버에서 `mariadb-dump`로 전체 DB 백업

2. **온라인 DDL 사용**
   - ALGORITHM=INPLACE (테이블 잠금 최소화)
   - LOCK=NONE (동시 읽기/쓰기 가능)

3. **중복 정리는 데이터 삭제**
   - 03-0에서 삭제 대상 행을 먼저 파일로 백업한 뒤 삭제
   - 인덱스와 달리 DROP INDEX로 되돌릴 수 없음 → 06-rollback.md의 "중복 정리 되돌리기" 참고

4. **모니터링 필수**
   - 인덱스 추가 중 CPU/메모리 모니터링
   - SHOW PROCESSLIST로 진행 상황 확인

5. **Slow query log 부하 주의**
   - `log_output`에 `TABLE`이 있으면 `mysql.slow_log`에 계속 쌓이고 자동으로 비워지지 않음
   - `log_queries_not_using_indexes`는 켜지 않음 (인덱스를 안 쓰는 모든 쿼리가 기록되어 로그가 급증)
   - 작업 후 01단계 1-4에서 저장한 원래 값으로 복원

6. **롤백 계획**
   - DROP INDEX 명령어 준비 (06-rollback.md)
   - 문제 발생 시 즉시 실행 가능

---

## 📅 작업 일정

01단계 사전 측정은 2026-09-15에 완료했습니다.

### 권장: 새벽 2시~3시 (트래픽 최소 시간)

| 시간 | 작업 | 예상 소요 |
|---|---|---|
| 02:00 | DB 백업 (EBS 스냅샷 또는 전체 덤프) | 5분 |
| 02:05 | 성능 기준선 측정 (02-baseline.md) | 10분 |
| 02:15 | 중복 정리 + 인덱스 추가 (03-add-indexes.md) | 30분 |
| 02:45 | 인덱스 생성 확인 | 5분 |
| 02:50 | 성능 개선 검증 (04-verify.md) | 10분 |
| 03:00 | 최종 체크리스트, slow log 복원, 임시 권한 회수 (05-checklist.md) | 10분 |

---

## 📂 디렉토리 구조

```
260915-improve-production/
├── 00-overview.md                    ← 이 파일
├── 01-premeasure.md                  ← 운영 DB 현황 측정
├── 02-baseline.md                    ← 성능 기준선 측정
├── 03-add-indexes.md                 ← 중복 정리 + 인덱스 추가
├── 04-verify.md                      ← 개선 효과 검증
├── 05-checklist.md                   ← 최종 체크리스트
├── 06-rollback.md                    ← 롤백 계획
├── 07-post-analysis.md               ← 사후 분석
└── result/                           ← 결과 파일 저장
    ├── premeasure_*.txt, premeasure_*.json        ← 01 사전 측정
    ├── premeasure_summary.txt                     ← 01 요약
    ├── params.sql, queries/*.sql                  ← 02·04 공통 측정 쿼리
    ├── run_measure.sh, summarize_explain.py       ← 02·04 공통 측정 도구
    ├── baseline_explain_*.json, baseline_perf.txt ← 02
    ├── backup_app_highest_dup_rows.sql            ← 03-0 삭제 행 백업
    ├── add_indexes.sql, add_indexes_result.txt    ← 03
    ├── after_explain_*.json, after_perf.txt       ← 04
    └── comparison.txt                             ← 04 비교
```

---

## 🚀 다음 단계

**01-premeasure.md** — 모든 측정 완료 (2026-09-15).

필요한 권한은 jisangs 계정에 임시 부여했습니다 (2026-09-15). **02-baseline.md**로 이동합니다.

---

## 📞 문제 발생 시

| 상황 | 대응 |
|---|---|
| **인덱스 추가 실패 (`Duplicate entry`)** | 3-0 다시 실행 후 `add_indexes.sql` 재실행 (이미 만든 인덱스는 건너뜀) |
| **인덱스 추가 실패 (기타)** | 에러 확인 후 필요하면 DROP INDEX (06-rollback.md) |
| **서비스 영향** | KILL QUERY + 인덱스 삭제 (06-rollback.md) |
| **최고기록 값 이상** | 삭제 행 백업으로 되돌리기 (06-rollback.md) |
| **성능 악화** | 인덱스 삭제 → 그래도 안 되면 백업에서 복원 |
| **기타 문제** | 07-post-analysis.md 참고 |

---

**01단계 완료, 작업 권한 준비 완료. 02단계부터 진행합니다.**