Mysql 무중단 Index 설정하기 (feat, pt-online-schema-change)

서비스를 개발하고 운영하다보면, 성능 개선 이슈에 부딪히게 된다.

어플리케이션 코드는 동일하지만, 데이터 양이 많아질 수록 성능이 현저히 떨어지게 되는 경우가 발생하게 되는데

대부분 DB에 Index 처리가 되어있지 않아서다.

물론, JPA 와 같은 ORM 특성을 잘못 써서 생기는 경우도 있지만.. 이 경우는 추후 정리를 해보는 것으로 하고..

 

보통은 Index 를 설정한다고 하면, "ALTER TABLE ... ADD INDEX ... " 를 생각할텐데

실제 라이브중인 데이터베이스에서는 그리 쉬운 일이 아니다.

 

나 역시도 이번에 정리할 툴을 알기 전 까지 DB Lock 을 감수하며 Index 설정을 하곤 했으나..

Online DDL 이란 것을 알게 된 이후 삶의 질(?)이 올라간 느낌이었다.

 

Online DDL 에 대해 설명하기보단, 실제 어떻게 사용하는지를 중점적으로 하여 기록을 남겨놓으려 한다.

 

설치하기

maxOS Mojave 기준

brew install percona-toolkit

설치 화면

 

설치 후 버전 확인

pt-online-schema-change --version

버전 확인

 

사용 예시

- 테스트를 위해 테이블을 하나 생성 하였다.

 

- 예시를 작성하기 위해 name 컬럼에 Index 를 설정해 본다.

pt-online-schema-change --alter "ADD INDEX INDEX_1 (name)" D=example_db,t=member --chunk-size=1000 --host=127.0.0.1 --port=3306 --user=hoon --password="58998972" --max-load="Threads_running=100" --alter-foreign-keys-method=auto --chunk-index=PRIMARY --sleep=0.003 --no-drop-old-table --execute

명렁어 실행

설정 확인

 

위와 같이 name 컬럼에 INDEX_1 이 설정된 것을 볼 수 있습니다.

추가로 '_member_old' 테이블이 보이는데 pt-online-schema-change 원리를 아주 간략히 설명 하자면 아래와 같습니다.

 1) 기존 테이블과 동일한 '_테이블명_new' 테이블을 생성 및 trigger 설정
 2) '_테이블명_new' 에서 명령어 진행 (ex, ADD COLUMN, ADD INDEX 등..)
 3) 작업이 모두 완료되면 '테이블명' -> '_테이블명_old' 로 변경,  '_테이블명_new' -> '테이블명' 으로 변경작업.

원본 테이블에서 바로 작업하는 것이 아닌, 복사본 테이블을 만들어 작업 후 스위칭 하는 형태 입니다.

따라서 DB Lock 을 최소화 하고, 서비스 운영중에도 보다 안전하게 진행이 가능합니다.

 

 

명령어 예시

# 컬럼 추가
pt-online-schema-change --alter "ADD COLUMN address VARCHAR(50) DEFAULT NULL COMMENT '주소' AFTER name" D=데이터베이스명,t=테이블명 --host=호스트 --port=포트 --user=DB계정 --password="DB비밀번호" --charset=UTF8 --chunk-size=1000 --max-load="Threads_running=100" --alter-foreign-keys-method=auto --chunk-index=PRIMARY --sleep=0.003 --no-drop-old-table --execute


# 컬럼 변경 (ex. contents VARCHAR(50) 을 LONGTEXT 로)
pt-online-schema-change --alter "MODIFY contents LONGTEXT" D=데이터베이스명,t=테이블명 --host=호스트 --port=포트 --user=DB계정 --password="DB비밀번호" --charset=UTF8 --chunk-size=1000 --max-load="Threads_running=100" --alter-foreign-keys-method=auto --chunk-index=PRIMARY --sleep=0.003 --no-drop-old-table --execute


# 컬럼 제거
pt-online-schema-change --alter "DROP COLUMN 컬럼1" D=데이터베이스명,t=테이블명 --host=호스트 --port=포트 --user=DB계정 --password="DB비밀번호" --charset=UTF8 --chunk-size=1000 --max-load="Threads_running=100" --alter-foreign-keys-method=auto --chunk-index=PRIMARY --sleep=0.003 --no-drop-old-table --execute


# Index 추가
pt-online-schema-change --alter "ADD INDEX INDEX_1 (컬럼1, 컬럼2, 컬럼3)" D=데이터베이스명,t=테이블명 --host=호스트 --port=포트 --user=DB계정 --password="DB비밀번호" --charset=UTF8 --chunk-size=1000 --max-load="Threads_running=100" --alter-foreign-keys-method=auto --chunk-index=PRIMARY --sleep=0.003 --no-drop-old-table --execute


# Unique Key 추가
pt-online-schema-change --alter "ADD CONSTRAINT UNIQUE_1 UNIQUE(컬럼2, 컬럼2, 컬럼3)" D=데이터베이스명,t=테이블명 --host=호스트 --port=포트 --user=DB계정 --password="DB비밀번호" --charset=UTF8 --chunk-size=1000 --max-load="Threads_running=100" --alter-foreign-keys-method=auto --chunk-index=PRIMARY --sleep=0.003 --no-drop-old-table --execute


# Index, Unique Key 제거
pt-online-schema-change --alter "DROP INDEX 인덱스명 또는 유니크키명" D=데이터베이스명,t=테이블명 --host=호스트 --port=포트 --user=DB계정 --password="DB비밀번호" --charset=UTF8 --chunk-size=1000 --max-load="Threads_running=100" --alter-foreign-keys-method=auto --chunk-index=PRIMARY --sleep=0.003 --no-drop-old-table --execute



※ --alter-foreign-keys-method=auto
: Index 를 추가 또는 변경하려는 DB에 FK 가 걸려 있을 경우 에러발생 => 툴에 위임 (auto 권장)

 

명령어는 실제 현업에서 사용한 내용을 바탕으로 정리한 예시 입니다.