RDS for MySQL의 충돌 복구 시간 개선을 위한 InnoDB 테이블스페이스 작업

MySQL의 모든 테이블은 테이블 정의, 데이터 및 인덱스로 구성되어 있습니다. InnoDB는 MySQL 스토리지 엔진으로서 테이블 데이터와 인덱스를 테이블스페이스에 저장하는 역할을 합니다. 이 스토리지 엔진은 전역적 공유 테이블스페이스를 생성하여 데이터 사전을 비롯한 기타 관련 메타데이터, 그리고 테이블 데이터와 인덱스도 저장합니다. 또한 테이블 및 파티션마다 별도의 테이블스페이스를 생성할 수도 있습니다. 이렇게 별도로 생성된 테이블스페이스는 확장자가 .ibd인 파일에 저장되며, 각 테이블스페이스 헤더에는 식별할 수 있도록 고유 번호가 포함됩니다.

Amazon RDS는 MySQL 파라미터 그룹을 통해 innodb_file_per_table이라고 하는 파라미터를 하나 제공합니다. 이 파라미터는 InnoDB가 파라미터 값을 0으로 설정하여 새 테이블 데이터와 인덱스를 공유 테이블스페이스에 추가할지 또는 파라미터 값을 1로 설정하여 개별 테이블스페이스에 추가할지 제어합니다. Amazon RDS는 innodb_file_per_table 파라미터의 기본값을 1로 설정하여 개별 InnoDB 테이블을 삭제하고 해당 테이블에서 DB 인스턴스에 사용하는 스토리지를 회수할 수 있습니다. 대부분 사용 사례에서 innodb_file_per_table 파라미터는 1로 설정하는 것이 바람직합니다.

하지만 표준 스토리지(마그네틱)나 일반 SSD 스토리지를 사용하여 테이블 수가 1,000개를 넘거나, 혹은 프로비저닝된 IOPS 스토리지를 사용하여 테이블 수가 10,000개를 넘는 등 테이블 수가 많을 때는 innodb_file_per_table 파라미터를 0으로 설정해야 합니다. 이 파라미터를 0으로 설정하면 테이블스페이스가 개별적으로 생성되지 않기 때문에 데이터베이스 충돌 복구에 걸리는 시간을 개선할 수 있습니다.

MySQL은 충돌 복구 주기에서 테이블스페이스가 저장된 메타데이터 파일을 각각 처리합니다. MySQL이 공유 테이블스페이스에 저장된 메타데이터 정보를 처리하는 데 걸리는 시간은 다수의 테이블스페이스로 인해 수천 개의 테이블스페이스 파일을 처리하는 데 걸리는 시간에 비하면 무시해도 될 정도입니다. 테이블스페이스 번호는 각 파일의 헤더에 저장되기 때문에 모든 테이블스페이스 파일을 읽으려면 최대 몇 시간까지 걸릴 수 있습니다. 예를 들어 표준 스토리지에 InnoDB 테이블스페이스가 수백만 개 저장되어 있다면 충돌 복구 주기에서 처리하는 데만 5~8시간이 소요됩니다. 경우에 따라 충돌 복구 주기가 끝나더라도 InnoDB가 추가 정리가 필요하다고 판단할 경우에는 또 다른 충돌 복구 주기가 시작되면서 복구 시간이 연장됩니다. 또 한 가지, 충돌 복구 주기는 테이블스페이스 정보 처리 외에도 롤링백 트랜잭션, 손상된 페이지 복구, 그리고 그 밖의 작업까지 수반한다는 점도 잊어서는 안 됩니다.

innodb_file_per_table 파라미터는 파라미터 그룹에 속하기 때문에 DB 인스턴스에 사용되는 파라미터 그룹만 편집하면 파라미터 값이 변경됩니다. 따라서 DB 인스턴스를 재부팅할 필요가 없습니다. 예를 들어 설정을 1(개별 테이블 생성)에서 0(공유 테이블스페이스 사용)으로 변경하면 새로운 InnoDB 테이블이 공유 테이블스페이스에 추가되는 반면 기존 테이블은 개별 테이블스페이스를 그대로 유지합니다. InnoDB 테이블을 공유 테이블스페이스로 이동하려면 ALTER TABLE 명령을 사용해야 합니다.

여러 테이블스페이스를 공유 테이블스페이스로 마이그레이션

InnoDB 테이블의 메타데이터를 자체 테이블스페이스에서 공유 테이블스페이스로 이동할 수 있습니다. 이렇게 하면 innodb_file_per_table 파라미터 설정에 따라 테이블 메타데이터가 다시 작성됩니다. 먼저 MySQL DB 인스턴스에 연결한 후 다음과 같이 해당 명령을 실행합니다. 자세한 내용은 MySQL DB 인스턴스에 연결 섹션을 참조하세요.

ALTER TABLE table_name ENGINE = InnoDB, ALGORITHM=COPY; 

예를 들어 다음 쿼리는 공유 테이블스페이스에 없는 모든 InnoDB 테이블에 대해 ALTER TABLE 문을 반환합니다.

MySQL 5.7 DB 인스턴스의 경우:

SELECT CONCAT('ALTER TABLE `', 
REPLACE(LEFT(NAME , INSTR((NAME), '/') - 1), '`', '``'), '`.`', 
REPLACE(SUBSTR(NAME FROM INSTR(NAME, '/') + 1), '`', '``'), '` ENGINE=InnoDB, ALGORITHM=COPY;') AS Query 
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES 
WHERE SPACE <> 0 AND LEFT(NAME, INSTR((NAME), '/') - 1) NOT IN ('mysql','');

MySQL 8.4 및 8.0 DB 인스턴스의 경우:

SELECT CONCAT('ALTER TABLE `', 
REPLACE(LEFT(NAME , INSTR((NAME), '/') - 1), '`', '``'), '`.`', 
REPLACE(SUBSTR(NAME FROM INSTR(NAME, '/') + 1), '`', '``'), '` ENGINE=InnoDB, ALGORITHM=COPY;') AS Query 
FROM INFORMATION_SCHEMA.INNODB_TABLES 
WHERE SPACE <> 0 AND LEFT(NAME, INSTR((NAME), '/') - 1) NOT IN ('mysql','');

MySQL 테이블을 리빌드하여 테이블의 메타데이터를 공유 테이블스페이스로 이동하려면 테이블을 리빌드할 수 있는 스토리지 공간이 임시로 필요합니다. DB 인스턴스에 여유 스토리지 공간이 있어야 하는 이유도 바로 이 때문입니다. 리빌드 단계에서는 테이블이 잠겨서 쿼리에 액세스하지 못합니다. 작은 용량의 테이블이나 자주 액세스하지 않는 테이블의 경우 이것이 문제가 되지는 않습니다. 하지만 대용량의 테이블이나 동시 접속자 수가 많은 환경에서 자주 액세스하는 테이블이라면 테이블을 읽기 전용 복제본에 다시 빌드할 수 있습니다.

읽기 전용 복제본을 생성한 후 테이블 메타데이터를 읽기 전용 복제본의 공유 테이블스페이스로 마이그레이션할 수 있습니다. ALTER TABLE 문이 읽기 전용 복제본에 대한 액세스를 차단하더라도 원본 DB 인스턴스는 영향을 받지 않습니다. 따라서 테이블 리빌딩 프로세스 중 읽기 전용 복제본이 지연되더라도 원본 DB 인스턴스는 계속해서 이진 로그를 생성합니다. 리빌딩 프로세스에는 스토리지 공간이 추가로 필요할 뿐만 아니라 재생 로그 파일이 커질 수도 있기 때문에 원본 DB 인스턴스보다 큰 용량의 스토리지를 할당하여 읽기 전용 복제본을 생성해야 합니다.

읽기 전용 복제본을 생성하여 InnoDB 테이블을 다시 빌드한 후 공유 테이블스페이스를 사용하려면 다음 단계를 따라야 합니다.