Instant DDL (Aurora MySQL version 3)Fast DDL (Aurora MySQL version 2)

Modification de tables dans Amazon Aurora à l'aide de Fast DDL

Amazon Aurora inclut des optimisations pour exécuter une opération ALTER TABLE en place, presque instantanément. L'opération s'effectue sans nécessiter la copie de la table et sans impact matériel sur les autres instructions DML. Puisque l'opération ne consomme pas de stockage temporaire pour une copie de table, les instructions DDL sont pratiques même pour des tables volumineuses sur des classes d'instance Small.

Aurora MySQL version 3 est compatible avec la fonction MySQL 8.0 appelée Instant DDL. Aurora MySQL version 2 utilise une implémentation différente appelée Fast DDL.

Instant DDL (Aurora MySQL version 3)

L'optimisation effectuée par Aurora MySQL version 3 pour améliorer l'efficacité de certaines opérations DDL est appelée DDL Instant DDL.

Aurora MySQL version 3 est compatible avec la fonction Instant DDL de MySQL 8.0 version communautaire. Vous effectuez une opération Instant DDL à l'aide de la clause ALGORITHM=INSTANT avec l'instruction ALTER TABLE. Pour plus de détails sur la syntaxe et l'utilisation d'Instant DDL, veuillez consulter ALTER TABLE et Online DDL Operations dans la documentation MySQL.

Les exemples suivants illustrent la fonction Instant DDL. Les instructions ALTER TABLE ajoutent des colonnes et modifient les valeurs par défaut des colonnes. Les exemples incluent des colonnes régulières et virtuelles, ainsi que des tables régulières et partitionnées. À chaque étape, vous pouvez consulter les résultats en émettant les instructions SHOW CREATE TABLE et 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)

Fast DDL (Aurora MySQL version 2)

Dans MySQL, de nombreuses opérations de langage de définition de données (DDL) ont un impact important sur les performances.

Par exemple, supposons que vous utilisiez une opération ALTER TABLE pour ajouter une colonne à une table. En fonction de l'algorithme spécifié pour l'opération, cette dernière peut impliquer :

la création d'une copie intégrale de la table,
la création d'une table temporaire pour traiter les opérations DML (Data Manipulation Language) simultanées,
la reconstruction de tous les index pour la table,
l'application de verrous de table lors de l'application de modifications DML simultanées,
le ralentissement du débit DML simultané.

L'optimisation effectuée par Aurora MySQL version 2 pour améliorer l'efficacité de certaines opérations DDL est appelée Fast DDL.

Dans Aurora MySQL version 3, Aurora utilise la fonction MySQL 8.0 appelée Instant DDL. Aurora MySQL version 2 utilise une implémentation différente appelée Fast DDL.

Important

Actuellement, le mode Lab d'Aurora doit être activé pour utiliser Fast DDL pour Aurora MySQL. Nous déconseillons l'utilisation de Fast DDL pour des clusters de bases de données de production. Pour obtenir des informations sur l'activation du mode lab d'Aurora, consultez Amazon Aurora Mon mode SQL laboratoire.

Limitations FAST DDL

Fast DDL présente actuellement les limitations suivantes :

FAST DLL prend uniquement en charge l'ajout de colonnes acceptant la valeur null, sans valeurs par défaut, à la fin d'une table existante.
Fast DDL ne fonctionne pas pour les tables partitionnées.
Fast DDL ne fonctionne pas pour des tables InnoDB qui utilisent le format de ligne REDUNDANT.
Fast DDL ne fonctionne pas pour les tables avec des index de recherche en texte intégral.
Si la taille maximale d'enregistrement possible pour l'opération DDL est trop importante, Fast DDL n'est pas utilisé. Une taille d'enregistrement est trop importante si elle est supérieure à la moitié de la taille de la page. La taille maximale d'un enregistrement est calculée en ajoutant les tailles maximales de toutes les colonnes. Pour les colonnes de taille variable, conformément aux normes InnoDB, les octets externes ne sont pas compris dans le calcul.

Syntaxe FAST DDL


ALTER TABLE tbl_name ADD COLUMN col_name column_definition

Cette instruction accepte les options suivantes :

tbl_name — Nom de la table à modifier.
col_name — Nom de la colonne à ajouter.
col_definition — Définition de la colonne à ajouter.

Note
Vous devez spécifier une définition de colonne acceptant la valeur null sans valeur par défaut. Sinon, Fast DLL n'est pas utilisé.

Exemples FAST DDL

Les exemples suivants illustrent l'accélération due aux opérations Fast DDL. Le premier exemple SQL exécute des instructions ALTER TABLE sur une grande table sans utiliser Fast DDL. Cette opération prend beaucoup de temps. Un exemple d'interface CLI montre comment activer Fast DDL pour le cluster. Ensuite, un autre exemple SQL exécute les mêmes instructions ALTER TABLE sur une table identique. Avec Fast DDL activé, l'opération est très rapide.

Cet exemple utilise la table ORDERS du benchmark TPC-H, qui contient 150 millions de lignes. Ce cluster utilise volontairement une classe d'instance relativement petite afin de montrer combien de temps les instructions ALTER TABLE peuvent prendre lorsque vous ne pouvez pas utiliser Fast DDL. L'exemple crée un clone de la table d'origine contenant des données identiques. La vérification du paramètre aurora_lab_mode confirme que le cluster ne peut pas utiliser Fast DDL, car le mode Lab n'est pas activé. Ensuite, les instructions ALTER TABLE ADD COLUMN prennent beaucoup de temps pour ajouter des colonnes à la fin de la table.


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)

Cet exemple effectue la même préparation d'une grande table que l'exemple précédent. Cependant, vous ne pouvez pas simplement activer le mode Lab dans une séance SQL interactive. Ce paramètre doit être activé dans un groupe de paramètres personnalisé. Pour ce faire, il faut sortir de la session mysql et exécuter quelques commandes de la CLI AWS ou utiliser la 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

L'activation du mode Lab pour le cluster nécessite d'utiliser un groupe de paramètres. Cet exemple d'AWS CLI utilise un groupe de paramètres de cluster afin de garantir que toutes les instances de base de données dans le cluster utilisent la même valeur pour le paramètre de mode Lab.


$ 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_mode
aurora_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 text
lab-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_mode
aurora_lab_mode 1

L'exemple suivant montre les étapes restantes une fois que les modifications du groupe de paramètres ont pris effet. Il teste le paramètre aurora_lab_mode pour s'assurer que le cluster peut utiliser Fast DDL. Ensuite, il exécute des instructions ALTER TABLE pour ajouter des colonnes à la fin d'une autre grande table. Cette fois, les instructions se terminent très rapidement.


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)

Avertissement JavaScript est désactivé ou n'est pas disponible dans votre navigateur.

Pour que vous puissiez utiliser la documentation AWS, Javascript doit être activé. Vous trouverez des instructions sur les pages d'aide de votre navigateur.

Conventions de rédaction

Test d'Amazon Aurora My à SQL l'aide de requêtes d'injection d'erreurs

Affichage du statut du volume pour un cluster de base de données Aurora