라이브 DB DDL 작업을 하게 될때는 항상 주의를 해야 한다. 보통 회사들의 경우에는 master -> slave 구조를 채택하고 있는데 이때 복제 지연이 생길 수 있기 떄문에 항상 주의를 기울여야 한다.
1. 복제 지연의 원인
DDL 작업(특히 `ALTER TABLE`을 통한 컬럼 추가)은 데이터베이스 복제 환경에서 지연을 유발할 수 있습니다. `NOT NULL` 또는 `DEFAULT` 값을 추가할 때 복제 지연이 발생하는 주요 원인은 다음과 같습니다:
테이블 재구성(Table Rewrite): MySQL과 같은 일부 데이터베이스에서는 `NOT NULL` 또는 `DEFAULT` 값을 가진 컬럼을 추가할 때 테이블을 재구성해야 합니다. 이 과정에서 기존 데이터에 대해 새로운 컬럼 값을 채우거나 업데이트하는 작업이 필요하며, 이로 인해 대량의 데이터 복사 및 디스크 I/O가 발생합니다. 복제 노드에서도 동일한 작업이 반복되므로 지연이 발생할 수 있습니다.
잠금(Locking): `NOT NULL` 제약 조건을 추가하거나 `DEFAULT` 값을 설정하면 테이블에 잠금이 걸릴 수 있습니다. 이는 주로 MySQL의 InnoDB와 같은 엔진에서 테이블 전체를 스캔하거나 업데이트할 때 발생하며, 잠금으로 인해 복제 노드에서 트랜잭션이 지연될 수 있습니다.
메타데이터 동기화: PostgreSQL 11 이전 버전이나 Oracle 11g 이전 버전에서는 `NOT NULL`과 `DEFAULT` 값을 추가할 때 모든 로우에 대해 값을 삽입하거나 업데이트해야 했습니다. 이 과정은 복제 노드에서도 동일하게 수행되며, 대규모 테이블에서는 시간이 오래 걸립니다.
복제 방식에 따른 지연: 비동기 복제 환경(예: MySQL의 기본 복제)에서는 마스터에서 실행된 DDL이 슬레이브에 순차적으로 적용됩니다. 대량의 데이터가 포함된 테이블에서 DDL 작업이 느리게 처리되면 복제 지연이 발생합니다.
2. 데이터베이스별 동작 및 개선 사항
MySQL
문제: MySQL에서 `NOT NULL` 또는 `DEFAULT` 값을 가진 컬럼을 추가하면 테이블 재구성이 필요하며, 이는 테이블 크기에 따라 시간이 오래 걸립니다. Online DDL(예: `ALGORITHM=INPLACE`)를 사용하더라도 `NOT NULL` 제약 조건 추가는 잠금을 유발할 수 있습니다.
해결 방안:
Online DDL 활용: MySQL 5.6 이상에서는 `ALGORITHM=INPLACE`와 `LOCK=NONE`을 사용해 잠금을 최소화할 수 있습니다.
ALTER TABLE table_name ADD COLUMN new_column VARCHAR(50) NOT NULL DEFAULT 'value', ALGORITHM=INPLACE, LOCK=NONE;
두 단계로 나누기: 컬럼을 먼저 `NULL` 허용으로 추가한 뒤, `DEFAULT` 값을 설정하고 마지막에 `NOT NULL` 제약 조건을 추가하면 잠금 시간을 줄일 수 있습니다.
ALTER TABLE table_name ADD COLUMN new_column VARCHAR(50);
ALTER TABLE table_name MODIFY COLUMN new_column VARCHAR(50) DEFAULT 'value';
ALTER TABLE table_name MODIFY COLUMN new_column VARCHAR(50) NOT NULL;
pt-online-schema-change 사용: Percona Toolkit의 `pt-online-schema-change`를 사용하면 테이블을 복사해 DDL을 적용하고, 데이터를 점진적으로 동기화해 복제 지연을 최소화할 수 있습니다.
PostgreSQL
문제: PostgreSQL 11 이전에서는 `NOT NULL`과 `DEFAULT` 값을 가진 컬럼 추가 시 모든 로우를 업데이트해야 했습니다. 이는 대규모 테이블에서 복제 지연을 유발합니다.
개선 사항: PostgreSQL 11부터는 `NOT NULL`과 `DEFAULT` 값을 추가할 때 메타데이터만 업데이트하고 실제 데이터는 필요 시 점진적으로 채워집니다. 이는 복제 지연을 크게 줄입니다.
ALTER TABLE table_name ADD COLUMN new_column VARCHAR(50) NOT NULL DEFAULT 'value';
이 작업은 메타데이터에 기본값을 저장하고, 로우 액세스 시 기본값을 동적으로 적용합니다.
해결 방안:
- PostgreSQL 11 이상을 사용해 메타데이터 기반 컬럼 추가를 활용.
- 대규모 테이블에서는 트랜잭션을 작은 단위로 나누거나, 백그라운드 작업으로 데이터를 채우는 스크립트를 작성.
Oracle
문제: Oracle 11g 이전에서는 `NOT NULL`과 `DEFAULT` 값을 가진 컬럼 추가 시 모든 로우에 대해 업데이트가 발생해 복제 지연이 발생했습니다.
개선 사항: Oracle 11g부터는 `NOT NULL` 컬럼 추가 시 메타데이터에 기본값을 저장하고, 실제 데이터는 로우가 업데이트/삽입될 때 적용됩니다. 이는 복제 지연을 줄입니다.
해결 방안:
- Oracle 11g 이상을 사용.
- 컬럼 추가와 `DEFAULT` 값 설정을 별도로 수행해 잠금을 최소화:
ALTER TABLE table_name ADD (new_column VARCHAR2(50));
ALTER TABLE table_name MODIFY (new_column DEFAULT 'value');
ALTER TABLE table_name MODIFY (new_column NOT NULL);
3. 복제 지연 최소화 전략
1. 사전 테스트: 개발 환경에서 동일한 데이터 크기와 복제 설정으로 DDL 작업을 테스트해 예상 소요 시간을 확인합니다.
2. 점진적 적용:
- 컬럼을 `NULL` 허용으로 추가한 뒤, 배치 작업으로 기존 데이터를 채우고 마지막에 `NOT NULL` 제약 조건을 추가합니다.
- 예: MySQL에서 배치 업데이트 스크립트:
UPDATE table_name SET new_column = 'default_value' WHERE new_column IS NULL LIMIT 1000;
이를 반복 실행해 부하를 분산.
3. 복제 지연 모니터링: MySQL의 `SHOW SLAVE STATUS` 또는 PostgreSQL의 `pg_stat_replication`을 사용해 복제 지연을 실시간으로 모니터링합니다.
4. 유지보수 시간대 활용: 트래픽이 낮은 시간대에 DDL 작업을 수행해 복제 지연의 영향을 최소화합니다.
5. 도구 활용:
- MySQL: `pt-online-schema-change` 또는 `gh-ost`를 사용.
- PostgreSQL: 최신 버전(11 이상) 사용.
- Oracle: 메타데이터 기반 DDL 활용.
6. 복제 방식 변경: 가능하면 비동기 복제 대신 반동기(semi-synchronous) 복제나 동기 복제를 고려해 지연을 줄입니다.
4. 주의사항
기존 데이터 확인: `NOT NULL` 제약 조건을 추가하기 전에 기존 데이터가 모두 유효한 값으로 채워져 있는지 확인합니다. 그렇지 않으면 에러가 발생할 수 있습니다.
트랜잭션 로그 크기: 대규모 테이블에서 DDL 작업은 트랜잭션 로그를 많이 사용할 수 있으므로, 디스크 공간과 로그 설정을 확인합니다.
복제 환경 테스트: 복제 지연은 복제 노드의 하드웨어 성능, 네트워크 대역폭, 복제 설정에 따라 달라질 수 있으므로 실제 환경에서 테스트가 중요합니다.
5. 예제: MySQL에서 복제 지연 최소화
1. 컬럼 추가 (NULL 허용)
ALTER TABLE table_name ADD COLUMN new_column VARCHAR(50), ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE table_name ADD COLUMN new_column VARCHAR(50), ALGORITHM=INPLACE, LOCK=NONE;
2. 기존 데이터에 기본값 채우기 (배치로 실행)
UPDATE table_name SET new_column = 'default_value' WHERE new_column IS NULL LIMIT 1000;
3. DEFAULT 값 설정
ALTER TABLE table_name MODIFY COLUMN new_column VARCHAR(50) DEFAULT 'default_value', ALGORITHM=INPLACE, LOCK=NONE;
4. NOT NULL 제약 조건 추가
ALTER TABLE table_name MODIFY COLUMN new_column VARCHAR(50) NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;
6. 결론
`NOT NULL` 또는 `DEFAULT` 값을 포함한 컬럼 추가는 데이터베이스 종류와 버전에 따라 복제 지연에 미치는 영향이 다릅니다. MySQL에서는 Online DDL과 단계적 적용, PostgreSQL 11 이상과 Oracle 11g 이상에서는 메타데이터 기반 작업을 활용해 지연을 최소화할 수 있습니다. 작업 전 충분한 테스트와 모니터링, 트래픽이 낮은 시간대를 활용하는 것이 중요합니다.
'코드 > dev' 카테고리의 다른 글
도커(Docker) 명령어 (0) | 2025.04.22 |
---|---|
도커(Docker) 컨테이너(Container) (1) | 2025.04.21 |
Bastion (0) | 2025.04.13 |
처리율 제한 알고리즘 (0) | 2025.04.04 |
Nginx (0) | 2025.04.04 |