빠른 DDL을 이용하는 Amazon Aurora에서의 테이블 수정
Amazon Aurora에는 거의 즉각적으로 현재 위치에서 ALTER TABLE 작업을 실행할 최적화 기능이 포함되어 있습니다. 이 작업은 테이블을 복사하거나 다른 DML 명령문에 영향을 거의 주지 않고 완료됩니다. 이 작업은 테이블 복사를 위해 임시 스토리지를 사용하지 않으므로 스몰 인스턴스 클래스의 라지 테이블에 대해서도 DDL 문을 유용하게 만듭니다.
Aurora MySQL 버전 3은 인스턴트 DDL이라는 MySQL 8.0 기능과 호환됩니다. Aurora MySQL 버전 2는 빠른 DDL이라는 다른 구현을 사용합니다.
인스턴트 DDL(Aurora MySQL 버전 3)
일부 DDL 작업의 효율성을 향상시키기 위해 Aurora MySQL 버전 3에서 수행하는 최적화를 인스턴트 DDL이라고 합니다.
Aurora MySQL 버전 3은 커뮤니티 MySQL 8.0의 인스턴트 DDL과 호환됩니다. ALTER TABLE 문이 포함된 ALGORITHM=INSTANT 절을 사용하여 인스턴트 DDL 작업을 수행합니다. 인스턴트 DDL에 대한 구문 및 사용 세부 정보는 MySQL 설명서의 ALTER TABLE
다음 예에서는 인스턴트 DDL 기능을 설명합니다. ALTER TABLE 문은 열을 추가하고, 기본 열 값을 변경합니다. 예에는 일반 열과 가상 열, 그리고 일반 테이블 및 파티션된 테이블이 모두 포함됩니다. 각 단계에서 SHOW CREATE TABLE 및 DESCRIBE 문을 발행하여 결과를 확인할 수 있습니다.
mysql> CREATE TABLE t1 (a INT, b INT, KEY(b)) PARTITION BY KEY(b) PARTITIONS 6; Query OK, 0 rows affected (0.02 sec) mysql> ALTER TABLE t1 RENAME TO t2, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.01 sec) mysql> ALTER TABLE t2 ALTER COLUMN b SET DEFAULT 100, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE t2 ALTER COLUMN b DROP DEFAULT, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.01 sec) mysql> ALTER TABLE t2 ADD COLUMN c ENUM('a', 'b', 'c'), ALGORITHM = INSTANT; Query OK, 0 rows affected (0.01 sec) mysql> ALTER TABLE t2 MODIFY COLUMN c ENUM('a', 'b', 'c', 'd', 'e'), ALGORITHM = INSTANT; Query OK, 0 rows affected (0.01 sec) mysql> ALTER TABLE t2 ADD COLUMN (d INT GENERATED ALWAYS AS (a + 1) VIRTUAL), ALGORITHM = INSTANT; Query OK, 0 rows affected (0.02 sec) mysql> ALTER TABLE t2 ALTER COLUMN a SET DEFAULT 20, -> ALTER COLUMN b SET DEFAULT 200, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE t3 (a INT, b INT) PARTITION BY LIST(a)( -> PARTITION mypart1 VALUES IN (1,3,5), -> PARTITION MyPart2 VALUES IN (2,4,6) -> ); Query OK, 0 rows affected (0.03 sec) mysql> ALTER TABLE t3 ALTER COLUMN a SET DEFAULT 20, ALTER COLUMN b SET DEFAULT 200, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE t4 (a INT, b INT) PARTITION BY RANGE(a) -> (PARTITION p0 VALUES LESS THAN(100), PARTITION p1 VALUES LESS THAN(1000), -> PARTITION p2 VALUES LESS THAN MAXVALUE); Query OK, 0 rows affected (0.05 sec) mysql> ALTER TABLE t4 ALTER COLUMN a SET DEFAULT 20, -> ALTER COLUMN b SET DEFAULT 200, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.01 sec) /* Sub-partitioning example */ mysql> CREATE TABLE ts (id INT, purchased DATE, a INT, b INT) -> PARTITION BY RANGE( YEAR(purchased) ) -> SUBPARTITION BY HASH( TO_DAYS(purchased) ) -> SUBPARTITIONS 2 ( -> PARTITION p0 VALUES LESS THAN (1990), -> PARTITION p1 VALUES LESS THAN (2000), -> PARTITION p2 VALUES LESS THAN MAXVALUE -> ); Query OK, 0 rows affected (0.10 sec) mysql> ALTER TABLE ts ALTER COLUMN a SET DEFAULT 20, -> ALTER COLUMN b SET DEFAULT 200, ALGORITHM = INSTANT; Query OK, 0 rows affected (0.01 sec)
빠른 DDL(Aurora MySQL 버전 2)
MySQL에서 많은 데이터 정의 언어(DDL) 작업이 상당한 성능 임팩트를 가집니다.
예를 들어 ALTER TABLE 작업을 이용해 열을 테이블에 추가한다고 가정해 보겠습니다. 지정된 알고리즘에 따라 이 작업은 다음을 포함할 수 있습니다.
-
테이블의 전체 사본 생성
-
동시에 발생하는 데이터 조작 언어(DML) 작업을 처리하는 임시 테이블 생성
-
테이블용의 모든 인덱스 리빌드
-
동시에 발생하는 DML 변경 사항을 적용하면서 테이블 록 적용
-
동시에 발생하는 DML 처리량 표시
일부 DDL 작업의 효율성을 향상시키기 위해 Aurora MySQL 버전 2에서 수행하는 최적화를 빠른 DDL이라고 합니다.
Aurora MySQL 버전 3에서 Aurora는 인스턴트 DDL이라는 MySQL 8.0 기능을 사용합니다. Aurora MySQL 버전 2는 빠른 DDL이라는 다른 구현을 사용합니다.
중요
현재 Aurora MySQL에 대해 빠른 DDL을 사용하려면 Aurora 랩 모드를 활성화해야 합니다. 프로덕션 DB 클러스터에는 빠른 DDL을 사용하지 않는 것이 좋습니다. Aurora 랩 모드 활성화에 대한 내용은 Amazon Aurora MySQL 랩 모드 단원을 참조하십시오.
빠른 DDL 제한
현재 빠른 DDL에는 다음과 같은 제한 사항이 있습니다.
-
빠른 DDL은 기존 테이블 끝까지 기본값 없이 null이 허용된 열 추가만 지원합니다.
-
빠른 DDL은 분할된 테이블에서 작동하지 않습니다.
-
빠른 DDL은 중복 행 형식을 사용하는 InnoDB 테이블을 지원하지 않습니다.
-
전체 텍스트 검색 인덱스가 있는 테이블에는 빠른 DDL이 작동하지 않습니다.
-
DDL 작업에 대한 최대 가능한 레코드 크기가 너무 크면 빠른 DDL이 사용되지 않습니다. 레코드 크기가 페이지 크기의 절반보다 큰 경우 해당 레코드가 너무 큽니다. 레코드의 최대 크기는 모든 열의 최대 크기를 더하여 계산합니다. 가변 크기 열의 경우에는 InnoDB 표준에 따라 extern 바이트가 계산에 포함되지 않습니다.
빠른 DDL 구문
ALTER TABLEtbl_nameADD COLUMNcol_namecolumn_definition
이 명령문의 옵션은 다음과 같습니다.
-
tbl_name— 수정할 테이블 이름. -
col_name— 추가할 열 이름. -
col_definition— 추가할 열 정의.참고
기본값 없이 null이 허용된 열 정의를 지정해야 합니다. 그렇지 않으면 빠른 DDL을 사용할 수 없습니다.
빠른 DDL 예제
다음 예시에서는 빠른 DDL 작업의 속도 향상을 보여줍니다. 첫 번째 SQL 예시에서는 빠른 DDL을 사용하지 않고 큰 테이블에서 ALTER TABLE 선언을 실행합니다. 이 작업에는 상당한 시간이 걸립니다. CLI 예시는 클러스터용 빠른 DDL을 활성화하는 방법을 보여줍니다. 그리고 다른 SQL 예제는 동일한 테이블에서 동일한 ALTER TABLE 명령문을 실행합니다. 빠른 DDL을 활성화하면 작업이 매우 빨라집니다.
이 예제에서는 1억 5천만 개의 행을 포함하는 TPC-H 벤치마크의 ORDERS 테이블을 사용합니다. 이 클러스터는 의도적으로 비교적 작은 인스턴스 클래스를 사용하여 빠른 DDL을 사용할 수 없을 때 ALTER TABLE 선언이 얼마나 오래 걸릴 수 있는지 보여줍니다. 이 예제에서는 동일한 데이터를 포함하는 원본 테이블의 클론을 생성합니다. 이 aurora_lab_mode 설정을 확인하면 랩 모드를 사용할 수 없기 때문에 클러스터가 빠른 DDL을 사용할 수 없음을 확인합니다. 그리고 ALTER TABLE ADD COLUMN 선언은 테이블 끝에 새 열을 추가하는 데 상당한 시간이 소요됩니다.
mysql>create table orders_regular_ddl like orders;Query OK, 0 rows affected (0.06 sec)mysql>insert into orders_regular_ddl select * from orders;Query OK, 150000000 rows affected (1 hour 1 min 25.46 sec)mysql>select @@aurora_lab_mode;+-------------------+ | @@aurora_lab_mode | +-------------------+ | 0 | +-------------------+mysql>ALTER TABLE orders_regular_ddl ADD COLUMN o_refunded boolean;Query OK, 0 rows affected (40 min 31.41 sec)mysql>ALTER TABLE orders_regular_ddl ADD COLUMN o_coverletter varchar(512);Query OK, 0 rows affected (40 min 44.45 sec)
이 예제는 이전 예제와 동일한 대형 테이블을 준비합니다. 그러나 대화형 SQL 세션 내에서 랩 모드를 사용하도록 설정할 수는 없습니다. 사용자 지정 파라미터 그룹에서 해당 설정을 사용하도록 설정해야 합니다. 이렇게 하려면 mysql 세션에서 전환하고 일부 AWS CLI 명령을 실행하거나 AWS Management Console를 사용합니다.
mysql>create table orders_fast_ddl like orders;Query OK, 0 rows affected (0.02 sec)mysql>insert into orders_fast_ddl select * from orders;Query OK, 150000000 rows affected (58 min 3.25 sec)mysql>set aurora_lab_mode=1;ERROR 1238 (HY000): Variable 'aurora_lab_mode' is a read only variable
클러스터에 대해 랩 모드를 사용하려면 파라미터 그룹을 사용한 일부 작업이 필요합니다. 이 AWS CLI 예제에서는 클러스터 파라미터 그룹을 사용하여 클러스터의 모든 DB 인스턴스가 랩 모드 설정에 동일한 값을 사용하게 합니다.
$aws rds create-db-cluster-parameter-group \ --db-parameter-group-family aurora5.7 \ --db-cluster-parameter-group-name lab-mode-enabled-57 --description 'TBD'$aws rds describe-db-cluster-parameters \ --db-cluster-parameter-group-name lab-mode-enabled-57 \ --query '*[*].[ParameterName,ParameterValue]' \ --output text | grep aurora_lab_modeaurora_lab_mode 0$aws rds modify-db-cluster-parameter-group \ --db-cluster-parameter-group-name lab-mode-enabled-57 \ --parameters ParameterName=aurora_lab_mode,ParameterValue=1,ApplyMethod=pending-reboot{ "DBClusterParameterGroupName": "lab-mode-enabled-57" }# Assign the custom parameter group to the cluster that's going to use Fast DDL.$aws rds modify-db-cluster --db-cluster-identifier tpch100g \ --db-cluster-parameter-group-name lab-mode-enabled-57{ "DBClusterIdentifier": "tpch100g", "DBClusterParameterGroup": "lab-mode-enabled-57", "Engine": "aurora-mysql", "EngineVersion": "5.7.mysql_aurora.2.10.2", "Status": "available" }# Reboot the primary instance for the cluster tpch100g:$aws rds reboot-db-instance --db-instance-identifier instance-2020-12-22-5208{ "DBInstanceIdentifier": "instance-2020-12-22-5208", "DBInstanceStatus": "rebooting" }$aws rds describe-db-clusters --db-cluster-identifier tpch100g \ --query '*[].[DBClusterParameterGroup]' --output textlab-mode-enabled-57$aws rds describe-db-cluster-parameters \ --db-cluster-parameter-group-name lab-mode-enabled-57 \ --query '*[*].{ParameterName:ParameterName,ParameterValue:ParameterValue}' \ --output text | grep aurora_lab_modeaurora_lab_mode 1
다음 예제에서는 파라미터 그룹 변경 사항이 적용된 후 나머지 단계를 보여 줍니다. 클러스터가 빠른 DDL을 사용할 수 있는지 확인하기 위해 aurora_lab_mode 설정을 테스트합니다. 그런 다음 ALTER
TABLE 진술을 실행하여 다른 큰 테이블의 끝에 열을 추가합니다. 이번에는 진술이 매우 빨리 끝납니다.
mysql>select @@aurora_lab_mode;+-------------------+ | @@aurora_lab_mode | +-------------------+ | 1 | +-------------------+mysql>ALTER TABLE orders_fast_ddl ADD COLUMN o_refunded boolean;Query OK, 0 rows affected (1.51 sec)mysql>ALTER TABLE orders_fast_ddl ADD COLUMN o_coverletter varchar(512);Query OK, 0 rows affected (0.40 sec)
