서비스를 개발하고 운영하다보면, 성능 개선 이슈에 부딪히게 된다.
어플리케이션 코드는 동일하지만, 데이터 양이 많아질 수록 성능이 현저히 떨어지게 되는 경우가 발생하게 되는데
대부분 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 권장)
명령어는 실제 현업에서 사용한 내용을 바탕으로 정리한 예시 입니다.