운영 중 스키마 변경 실전: Online DDL + Expand/Contract 패턴

ALTER TABLE orders ADD COLUMN channel VARCHAR(50);

MySQL 5.6 이전:
  ① 전체 테이블 복사 (새 구조로)
  ② 복사 중 DML 차단 (Table Lock)
  ③ 원본 ↔ 복사본 교체
  → 8억 건 테이블: 수 시간 동안 읽기/쓰기 차단

MySQL 8 Online DDL (INPLACE):
  ① 메타데이터 락 획득 (짧은 시간)
  ② 백그라운드에서 재구성 (DML 허용)
  ③ 메타데이터 락 재획득 → 교체
  → DML은 허용되지만, ①③에서 짧은 차단 발생 가능

MySQL 8 INSTANT:
  ① 메타데이터만 변경 (데이터 파일 안 건드림)
  → 즉시 완료, 테이블 크기 무관

-- ✅ INSTANT: 즉시 완료, 8억 건 테이블도 밀리초 단위
ALTER TABLE orders ADD COLUMN channel VARCHAR(50) DEFAULT NULL, ALGORITHM=INSTANT;

-- 확인: INSTANT가 가능한지 먼저 체크
ALTER TABLE orders ADD COLUMN channel VARCHAR(50), ALGORITHM=INSTANT;
-- ERROR 0A000: ALGORITHM=INSTANT is not supported → 다른 방법 필요

-- INPLACE로 폴백 (DML 허용, 시간 소요)
ALTER TABLE orders ADD INDEX idx_channel (channel), ALGORITHM=INPLACE, LOCK=NONE;

변경이 INSTANT로 가능한가?
├── Yes → INSTANT 사용 (끝)
│
└── No → INPLACE로 가능한가?
         ├── Yes → 테이블 크기와 트래픽 확인
         │         ├── 소규모 (< 1000만 건, 낮은 트래픽) → INPLACE 사용
         │         └── 대규모 (> 1000만 건, 높은 트래픽) → gh-ost/pt-osc 고려
         │
         └── No → 반드시 gh-ost/pt-osc 또는 서비스 레벨 마이그레이션

-- 배포 1: Expand
ALTER TABLE orders ADD COLUMN channel VARCHAR(50) DEFAULT NULL, ALGORITHM=INSTANT;
-- 기존 코드는 channel 컬럼을 모르지만, 영향 없음 (nullable)

// 앱 코드: 신규 컬럼은 아직 Optional
@Column(name = "channel")
private String channel;  // null 허용 → 기존 코드 호환

// 배포 2: 이중 쓰기 (새 주문은 channel 값 포함)
@Transactional
public Order createOrder(OrderRequest request) {
    Order order = Order.builder()
            .userId(request.getUserId())
            .amount(request.getAmount())
            .channel(request.getChannel())  // ✅ 신규 필드 쓰기 시작
            .build();
    return orderRepository.save(order);
}

@Component
@RequiredArgsConstructor
public class ChannelBackfillJob {

    private final JdbcTemplate jdbcTemplate;

    // Spring Batch Step 또는 @Scheduled로 실행
    public void backfill() {
        int batchSize = 5000;
        int totalUpdated = 0;

        while (true) {
            // ✅ 범위 기반 배치 (테이블 스캔 방지)
            int updated = jdbcTemplate.update("""
                UPDATE orders
                SET channel = COALESCE(
                    (SELECT source FROM order_sources WHERE order_sources.order_id = orders.id),
                    'UNKNOWN'
                )
                WHERE channel IS NULL
                AND id BETWEEN ? AND ?
                LIMIT ?
                """,
                getNextBatchStart(), getNextBatchEnd(), batchSize
            );

            totalUpdated += updated;
            log.info("Backfill progress: {} rows updated (total: {})", updated, totalUpdated);

            if (updated == 0) break;

            // ✅ DB 부하 제어: 배치 간 대기
            sleep(Duration.ofMillis(200));
        }

        log.info("Backfill complete: {} total rows updated", totalUpdated);
    }
}

-- 전체 진행률
SELECT
    COUNT(*) AS total_rows,
    SUM(CASE WHEN channel IS NOT NULL THEN 1 ELSE 0 END) AS migrated,
    SUM(CASE WHEN channel IS NULL THEN 1 ELSE 0 END) AS remaining,
    ROUND(SUM(CASE WHEN channel IS NOT NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS progress_pct
FROM orders;

-- 시간별 백필 속도
SELECT
    DATE_FORMAT(updated_at, '%Y-%m-%d %H:00') AS hour,
    COUNT(*) AS rows_updated
FROM orders
WHERE channel IS NOT NULL
  AND updated_at >= NOW() - INTERVAL 24 HOUR
GROUP BY 1
ORDER BY 1;

-- 배포 3: Contract (별도 릴리스, 백필 100% 확인 후)
-- ✅ 먼저 NOT NULL 제약 추가
ALTER TABLE orders MODIFY COLUMN channel VARCHAR(50) NOT NULL DEFAULT 'UNKNOWN';

-- ✅ 불필요한 구 컬럼/인덱스 삭제 (INSTANT 가능: 8.0.29+)
ALTER TABLE orders DROP COLUMN old_source, ALGORITHM=INSTANT;

# 기본 실행 (dry-run 먼저!)
gh-ost \
  --host=db-primary.internal \
  --port=3306 \
  --user=ghost_user \
  --password='$GHOST_PASSWORD' \
  --database=myapp \
  --table=orders \
  --alter="ADD COLUMN channel VARCHAR(50) DEFAULT NULL" \
  --chunk-size=1000 \
  --max-load='Threads_running=25' \
  --critical-load='Threads_running=50' \
  --nice-ratio=0.5 \
  --initially-drop-ghost-table \
  --initially-drop-old-table \
  --execute  # 없으면 dry-run

# 주요 옵션 설명:
# --chunk-size=1000         : 한 번에 복사할 행 수
# --max-load                : 이 부하 초과 시 속도 자동 조절
# --critical-load           : 이 부하 초과 시 즉시 중단
# --nice-ratio=0.5          : 작업 1초 당 0.5초 대기 (부하 제어)
# --postpone-cut-over-flag  : 이 파일 존재 시 컷오버 보류

# 진행 상황 확인
echo "status" | nc -U /tmp/gh-ost.myapp.orders.sock

# 속도 조절 (런타임에 변경 가능!)
echo "chunk-size=500" | nc -U /tmp/gh-ost.myapp.orders.sock
echo "nice-ratio=1.0" | nc -U /tmp/gh-ost.myapp.orders.sock  # 더 느리게

# 일시 중지
echo "throttle" | nc -U /tmp/gh-ost.myapp.orders.sock

# 재개
echo "no-throttle" | nc -U /tmp/gh-ost.myapp.orders.sock

# 컷오버 보류 (안전장치)
touch /tmp/gh-ost.postpone.orders

# 컷오버 실행 (파일 삭제)
rm /tmp/gh-ost.postpone.orders

# dry-run (변경 없이 시뮬레이션)
pt-online-schema-change \
  --host=db-primary.internal \
  --port=3306 \
  --user=ptosc_user \
  --password='$PTOSC_PASSWORD' \
  D=myapp,t=orders \
  --alter="ADD COLUMN channel VARCHAR(50) DEFAULT NULL" \
  --chunk-size=1000 \
  --max-load="Threads_running:25" \
  --critical-load="Threads_running:50" \
  --chunk-time=0.5 \
  --dry-run

# 실제 실행
pt-online-schema-change \
  D=myapp,t=orders \
  --alter="ADD COLUMN channel VARCHAR(50) DEFAULT NULL" \
  --chunk-size=1000 \
  --max-load="Threads_running:25" \
  --critical-load="Threads_running:50" \
  --chunk-time=0.5 \
  --set-vars="innodb_lock_wait_timeout=5" \
  --no-drop-old-table \
  --execute

# 주요 옵션 설명:
# --chunk-time=0.5          : 각 청크 목표 실행 시간 (초)
# --no-drop-old-table       : 원본 테이블 보존 (롤백 대비)
# --max-lag=1               : 복제 지연 1초 초과 시 대기
# --check-interval=5        : 부하/복제 체크 간격 (초)

배포 1: Expand
  ALTER TABLE orders ADD COLUMN account_status VARCHAR(50);
  → 기존 user_status 유지, 새 account_status 추가

배포 2: 이중 쓰기
  앱 코드: user_status와 account_status 둘 다 쓰기
  INSERT INTO orders (..., user_status, account_status)
  VALUES (..., 'ACTIVE', 'ACTIVE');

배포 3: 읽기 전환 + 백필
  백필: UPDATE orders SET account_status = user_status WHERE account_status IS NULL
  앱 코드: 읽기를 account_status로 변경 (user_status는 쓰기만)

배포 4: Contract
  앱 코드: user_status 참조 제거
  ALTER TABLE orders DROP COLUMN user_status;

// 배포 2: 이중 쓰기 코드
@Entity
@Table(name = "orders")
public class Order {

    @Column(name = "user_status")
    private String userStatus;

    @Column(name = "account_status")
    private String accountStatus;

    // setter에서 양쪽 모두 업데이트
    public void setAccountStatus(String status) {
        this.accountStatus = status;
        this.userStatus = status;  // 이중 쓰기
    }
}

-- 1. 메타데이터 락 대기 확인 (변경 중 가장 흔한 문제)
SELECT
    waiting.THREAD_ID AS waiting_thread,
    waiting.EVENT_NAME AS waiting_event,
    blocking.THREAD_ID AS blocking_thread,
    blocking.EVENT_NAME AS blocking_event,
    waiting.TIMER_WAIT / 1000000000 AS wait_seconds
FROM performance_schema.metadata_locks AS ml
JOIN performance_schema.events_waits_current AS waiting
    ON ml.OWNER_THREAD_ID = waiting.THREAD_ID
JOIN performance_schema.events_waits_current AS blocking
    ON blocking.THREAD_ID != waiting.THREAD_ID
WHERE waiting.EVENT_NAME LIKE 'wait/lock/metadata%';

-- 2. ALTER TABLE 진행 상황 (MySQL 8)
SELECT
    EVENT_NAME,
    WORK_COMPLETED,
    WORK_ESTIMATED,
    ROUND(WORK_COMPLETED * 100.0 / WORK_ESTIMATED, 1) AS pct_done
FROM performance_schema.events_stages_current
WHERE EVENT_NAME LIKE 'stage/innodb/alter%';

-- 3. 슬로우 쿼리 급증 감지
SELECT
    DIGEST_TEXT,
    COUNT_STAR AS exec_count,
    ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_ms,
    ROUND(MAX_TIMER_WAIT / 1000000000, 2) AS max_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'myapp'
  AND AVG_TIMER_WAIT > 1000000000  -- 1초 초과
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

-- 4. InnoDB 행 락 대기
SELECT
    r.trx_id AS waiting_trx,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query,
    TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_seconds
FROM information_schema.innodb_lock_waits AS w
JOIN information_schema.innodb_trx AS r ON r.trx_id = w.requesting_trx_id
JOIN information_schema.innodb_trx AS b ON b.trx_id = w.blocking_trx_id;

-- 5. 복제 지연 확인 (gh-ost/pt-osc가 자동 체크하지만 수동 확인도 필요)
SHOW REPLICA STATUS\G
-- Seconds_Behind_Source 값 확인

groups:
  - name: schema_migration
    rules:
      - alert: HighMetadataLockWait
        expr: mysql_global_status_innodb_row_lock_current_waits > 10
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "메타데이터 락 대기 급증 — 스키마 변경 중단 검토"

      - alert: ReplicationLagDuringMigration
        expr: mysql_slave_status_seconds_behind_master > 5
        for: 2m
        labels:
          severity: warning
        annotations:
          summary: "복제 지연 {{ $value }}초 — 마이그레이션 속도 조절 필요"

      - alert: SlowQuerySpikeDuringDDL
        expr: rate(mysql_global_status_slow_queries[5m]) > 10
        for: 3m
        labels:
          severity: warning
        annotations:
          summary: "슬로우 쿼리 급증 — DDL 영향 확인"

-- V001__expand_add_channel.sql (Expand)
ALTER TABLE orders ADD COLUMN channel VARCHAR(50) DEFAULT NULL, ALGORITHM=INSTANT;

-- V002__contract_drop_old_source.sql (Contract — 별도 릴리스)
-- ⚠️ 이 마이그레이션은 백필 완료 + 앱 코드 전환 후에만 실행
ALTER TABLE orders DROP COLUMN old_source, ALGORITHM=INSTANT;