운영 중인 MariaDB에서 인덱스를 손보는 작업은 “쿼리는 빨라지지만, 그 과정에서 서비스가 멈출 수 있다”는 두려움 때문에 미루기 쉽습니다. 이 글은 Ubuntu 환경에서 인덱스 추가/변경을 최대한 online에 가깝게 진행하기 위한 점검 순서와 선택지를 정리한 실전 가이드입니다.
핵심은 ‘먼저 측정 → 어떤 DDL이 잠금을 유발하는지 확인 → 안전한 방식으로 적용 → 즉시 검증’입니다.
1) 먼저 “인덱스가 필요한 쿼리”를 정확히 잡기
인덱스 작업은 스키마 자체를 바꾸는 일이어서, 시작 전에 “어떤 쿼리를 어떤 수준으로 줄일지”가 명확해야 합니다. 운영 중 영향 최소화 관점에서는 특히 다음 2가지를 먼저 확보하는 게 좋습니다.
- 느린 쿼리 근거: slow query log 또는 애플리케이션 APM에서 상위 쿼리/빈도/평균 시간을 확인
- 실행 계획 근거: EXPLAIN(가능하면 EXPLAIN FORMAT=JSON)로 풀스캔/임시테이블/정렬 여부 확인
예를 들어 “ORDER BY 때문에 filesort가 과도하다”인지, “WHERE 조건이 인덱스를 못 타서 rows가 폭증한다”인지에 따라 인덱스 설계가 달라지고, 잘못 설계하면 인덱스만 늘고 쓰기 성능만 떨어질 수 있습니다.
2) 현재 인덱스/카디널리티 확인: SHOW INDEX와 히스토그램/통계
이미 비슷한 인덱스가 있는지, 복합 인덱스 순서가 맞는지부터 확인합니다.
- SHOW INDEX FROM 테이블명: Key_name, Seq_in_index, Cardinality를 보고 중복/유사 인덱스 여부 판단
- 통계 최신화: 대량 변경 후엔 옵티마이저가 잘못 선택할 수 있어 ANALYZE TABLE로 통계 갱신을 고려
운영 데이터가 계속 변하는 테이블이라면, 테스트 환경에서 잘 나오던 실행 계획이 운영에서 다르게 나올 수 있습니다. 인덱스 작업 전후로 같은 조건의 EXPLAIN과 실제 쿼리 시간을 같이 비교하는 습관이 안전합니다.
3) “온라인 DDL 가능 여부”부터 확인: ALGORITHM/LOCK로 실패를 빨리 드러내기
인덱스 추가/변경은 MariaDB 버전과 스토리지 엔진(InnoDB 등), 그리고 작업 종류에 따라 잠금 방식이 달라집니다. 운영 중에는 “걸어보고 멈추면 취소”가 가장 위험하므로, DDL 문에 ALGORITHM과 LOCK 옵션을 명시해 서버가 가능한지 즉시 판단하게 하는 편이 좋습니다.
- ALGORITHM=INPLACE를 시도해 테이블 복사(COPY)를 피할 수 있는지 확인
- LOCK=NONE 또는 최소 잠금 옵션을 명시해, 불가 시 에러로 바로 알 수 있게 함
- 옵션을 줬는데 “지원하지 않는다”는 에러가 나면, 그 작업은 해당 환경에서 온라인으로 못 한다고 보는 게 안전
실제로는 “완전한 무잠금”이 아니라도(예: 메타데이터 락이 짧게 발생), 트래픽이 많은 서비스에선 그 짧은 순간도 대기열을 만들 수 있습니다. 따라서 다음 섹션처럼 사전 대기/락 관측을 같이 준비해 두는 것이 운영 친화적입니다.
4) DDL 시작 전후로 잠금/대기 관측하기: processlist, InnoDB status, metadata lock
인덱스 DDL은 “작업 자체 시간”보다 “대기열이 쌓이는 시간”이 체감 장애를 만들 때가 많습니다. 다음 체크리스트로 시작 직전/진행 중/완료 직후를 관측하세요.
- SHOW FULL PROCESSLIST로 Waiting/Locked 상태 쿼리 증가 여부 확인
- SHOW ENGINE INNODB STATUS에서 lock wait, deadlock 메시지 여부 확인
- 메타데이터 락 징후: DDL이 “Waiting for table metadata lock”에 걸리면 오래 대기할 수 있음
- 트래픽 시간대: 가장 한가한 구간에 시작(가능하면 배치/정기 작업 시간 피하기)
여기서 중요한 포인트는 “DDL이 느린가?”가 아니라 “DDL 때문에 다른 쿼리가 막히는가?”입니다. 막히는 조짐이 보이면 즉시 중지/롤백(또는 pt-online-schema-change 같은 우회)을 선택할 수 있게 준비합니다.
5) 온라인 영향 최소화 선택지: (A) 네이티브 DDL vs (B) pt-online-schema-change
운영에서의 선택지는 보통 두 갈래입니다.
- (A) 네이티브 ALTER TABLE로 해결 가능: ALGORITHM/LOCK 옵션으로 요구사항을 만족하고, 작업 시간도 예측 가능할 때
- (B) pt-online-schema-change(pt-osc): 네이티브로는 잠금/대기 리스크가 크거나, “LOCK=NONE”이 불가능한 작업일 때
pt-online-schema-change는 새 테이블을 만들고 트리거로 변경을 동기화한 다음 스왑하는 방식이라, 일반적인 ALTER TABLE보다 운영 중 체감 영향이 줄어드는 경우가 많습니다. 다만 트리거 부하, 복제 환경(Replica)에서의 지연, 외래키 제약 등 주의할 점이 있으니 테스트가 필수입니다.
운영 절차 관점에서 추천 흐름은 다음과 같습니다.
- 1단계: 테스트/스테이징에서 동일 스키마와 유사 데이터로 네이티브 DDL 시도(옵션 포함)
- 2단계: 네이티브가 안전하지 않으면 pt-osc를 우선 후보로 검토(사전 리허설)
- 3단계: 운영 적용은 “짧은 관측 루프”로 진행(시작 후 1~3분 내 이상 징후 판단)
6) 적용 후 검증과 정리: 실행 계획/성능/중복 인덱스/롤백 포인트
인덱스는 “만들었다”에서 끝나지 않고, 적용 후 검증과 정리가 있어야 운영 리스크가 줄어듭니다.
- 실행 계획 비교: 변경 전후 EXPLAIN을 저장해 rows, key, Extra 변화를 확인
- 실측: 동일 조건 쿼리의 p95/p99 응답시간(또는 평균)과 CPU 사용량 변화를 비교
- 쓰기 부하 확인: INSERT/UPDATE가 많은 테이블은 인덱스 추가로 쓰기 비용이 늘 수 있어 모니터링 필요
- 중복 인덱스 정리: 새 복합 인덱스가 기존 단일 인덱스를 대체하는지 검토(무턱대고 삭제하지 말고 실제 쿼리 사용 여부 확인)
- 롤백 포인트: 문제가 생겼을 때 “인덱스 DROP”만으로 해결 가능한지, 애플리케이션 쿼리도 함께 수정했는지 체크
특히 운영 중에는 “인덱스 추가 후 쿼리가 빨라졌는데, 업데이트가 느려졌다” 같은 형태로 뒤늦게 부작용이 나타날 수 있습니다. 적용 후 24시간 정도는 주요 지표(쿼리 시간, 잠금 대기, CPU, I/O)를 평소보다 촘촘히 보는 게 안전합니다.
마무리
Ubuntu의 MariaDB에서 인덱스를 온라인에 가깝게 바꾸려면, 기술 자체(ALTER 옵션, pt-osc)도 중요하지만 “실패를 빨리 드러내고(옵션 명시), 짧은 관측 루프로 위험을 줄이는 운영 절차”가 더 큰 차이를 만듭니다.
가장 추천하는 시작점은 느린 쿼리 1~2개를 골라 EXPLAIN/실측으로 목표를 정하고, ALGORITHM/LOCK로 네이티브 가능 여부를 먼저 확인하는 것입니다.