高速 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 と互換性があります。インスタント DDL オペレーションを実行するには、ALTER TABLE ステートメントで ALGORITHM=INSTANT 句を使用します。インスタント 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 は、REDUNDANT 行形式を使用する InnoDB テーブルをサポートしていません。
-
Fast DDL は、フルテキスト検索インデックスを持つテーブルでは機能しません。
-
DDL オペレーションの最大可能レコードサイズが大きすぎる場合、高速 DDL は使用されません。ページサイズの半分を超えるレコードサイズは大きすぎます。レコードの最大サイズは、すべての列の最大サイズを追加して計算されます。サイズを変更可能な列の場合は、InnoDB スタンダードに基づき、extern byte は計算に含まれません。
高速 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 億 5000 万行を含む、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)
この例では、前の例と同様に大きなテーブルを準備します。ただし、Interactive 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)
