DDL istantaneo (Aurora MySQL versione 3)Fast DDL (Aurora MySQL versione 2)

Alterazione delle tabelle in Amazon Aurora mediante Fast DDL

Amazon Aurora include ottimizzazioni per eseguire un’operazione ALTER TABLE in atto, quasi istantaneamente. L'operazione viene eseguita senza la copia della tabella e senza impatto materiale sulle altre istruzioni DML. Poiché l'operazione non consuma storage temporaneo per la copia della tabella, rende le istruzioni DDL pratiche anche nel caso di tabelle di grandi dimensioni su tipi di classi small.

Aurora MySQL versione 3 è compatibile con la funzionalità MySQL 8.0 chiamata Instant DDL. Aurora MySQL versione 2 utilizza un'implementazione diversa chiamata Fast DDL.

DDL istantaneo (Aurora MySQL versione 3)

L'ottimizzazione eseguita da Aurora MySQL versione 3 per migliorare l'efficienza di alcune operazioni DDL è chiamata DDL istantaneo.

Aurora MySQL versione 3 è compatibile con il DDL istantaneo della community MySQL 8.0. Si esegue un'operazione DDL istantanea utilizzando la clausola ALGORITHM=INSTANT con l’istruzione ALTER TABLE. Per informazioni sulla sintassi e sull'utilizzo del DDL istantaneo, vedere ALTER TABLE e Online DDL Operations (Operazioni DDL online) nella documentazione MySQL.

I seguenti esempi dimostrano la funzionalità DDL immediata. Le istruzioni ALTER TABLE aggiungono colonne e modificano i valori di default delle colonne. Gli esempi includono colonne regolari e virtuali e tabelle regolari e partizionate. In ogni fase, è possibile visualizzare i risultati emettendo SHOW CREATE TABLE e istruzioni 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 versione 2)

In MySQL, molte operazioni DDL (Data Definition Language) hanno un impatto considerevole sulle prestazioni.

Ad esempio, supponiamo che utilizzi un'operazione ALTER TABLE per aggiungere una colonna a una tabella. A seconda dell'algoritmo specificato per l'operazione, questa può comportare quanto segue:

Creazione di una copia completa della tabella
Creazione di una tabella temporanea per elaborare operazioni DML (Data Manipulation Language) simultanee
Ricostruzione di tutti gli indici per la tabella
Applicazione dei blocchi di tabelle durante l'applicazione di modifiche DML simultanee
Rallentamento del throughput DML simultaneo

L'ottimizzazione eseguita da Aurora MySQL versione 2 per migliorare l'efficienza di alcune operazioni DDL è chiamata Fast DDL.

In Aurora MySQL versione 3, Aurora utilizza la funzione MySQL 8.0 chiamata DDL istantaneo. Aurora MySQL versione 2 utilizza un'implementazione diversa chiamata Fast DDL.

Importante

Attualmente, la modalità lab di Aurora deve essere abilitata per utilizzare Fast DDL per Aurora MySQL. Si sconsiglia di utilizzare Fast DDL per cluster di database di produzione. Per ulteriori informazioni su come abilitare la modalità di laboratorio per Aurora, consulta Modalità di laboratorio per Amazon Aurora MySQL.

Limiti di Fast DDL

Fast DDL presenta attualmente le seguenti limitazioni:

Supporta solo l'aggiunta di una colonna nullable, senza valori predefiniti, alla fine di una tabella esistente.
La DDL veloce non funziona per le tabelle partizionate.
DDL rapida non funziona per le tabella InnoDB che utilizzano il formato riga REDUNDANT.
La DDL veloce non funziona per le tabelle con indici di ricerca full-text.
Se la dimensione di record massima possibile per l'operazione DDL è troppo grande, Fast DDL non viene utilizzato. Una dimensione di record è troppo importante se è più grande della metà della dimensione della pagina. La dimensione massima di un record viene calcolata sommando la dimensione massima di ogni colonna. Per le colonne di dimensione variabile, conformemente agli standard InnoDB, i byte extern non sono inclusi nel calcolo.

Sintassi di Fast DDL


ALTER TABLE tbl_name ADD COLUMN col_name column_definition

Questa istruzione accetta le seguenti opzioni:

tbl_name — il nome della tabella da modificare.
col_name — il nome della colonna da aggiungere.
col_definition — la definizione della colonna da aggiungere.

Nota
Devi specificare una definizione di colonna nullable senza un valore predefinito. In caso contrario, Fast DDL non viene utilizzato.

Esempi di Fast DDL

Negli esempi seguenti viene illustrata la velocità delle operazioni Fast DDL. Il primo esempio SQL esegue istruzioni ALTER TABLE su una tabella di grandi dimensioni senza utilizzare Fast DDL. Questa operazione richiede molto tempo. Un esempio della CLI mostra come abilitare Fast DDL per il cluster. Quindi un altro esempio SQL esegue le stesse istruzioni ALTER TABLE su una tabella identica. Con Fast DDL abilitato, l'operazione è molto veloce.

In questo esempio viene utilizzata la tabella ORDERS del benchmark TPC-H, contenente 150 milioni di righe. Questo cluster utilizza intenzionalmente una classe di istanza relativamente piccola, per dimostrare quanto tempo possano richiedere le istruzioni ALTER TABLE quando non è possibile utilizzare Fast DDL. Nell'esempio viene creato un clone della tabella originale contenente dati identici. Il controllo dell'impostazione aurora_lab_mode conferma che il cluster non può utilizzare Fast DDL poiché la modalità lab non è abilitata. Quindi le istruzioni ALTER TABLE ADD COLUMN richiedono molto tempo per aggiungere nuove colonne alla fine della tabella.


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)

Questo esempio esegue la stessa preparazione di una tabella di grandi dimensioni dell'esempio precedente. Tuttavia, non è possibile abilitare semplicemente la modalità lab all'interno di una sessione SQL interattiva. Tale impostazione deve essere abilitata in un gruppo di parametri personalizzato. Ciò richiede l'uscita dalla sessione mysql e l'esecuzione di alcuni comandi della CLI AWS o l'utilizzo della 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

Per abilitare la modalità lab per il cluster è necessario lavorare con un gruppo di parametri. In questo esempio della CLI AWS viene utilizzato un gruppo di parametri del cluster per garantire che tutte le istanze database nel cluster utilizzino lo stesso valore per l'impostazione della modalità 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

Nell'esempio seguente vengono illustrati i passaggi rimanenti dopo che la modifica del gruppo di parametri ha effetto. Si verifica l'impostazione aurora_lab_mode per assicurarsi che il cluster possa utilizzare Fast DDL. Quindi viene eseguita l'istruzione ALTER TABLE per aggiungere colonne alla fine di un'altra tabella di grandi dimensioni. Questa volta, le istruzioni vengono completate molto rapidamente.


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)

Avvertimento JavaScript è disabilitato o non è disponibile nel tuo browser.

Per usare la documentazione AWS, JavaScript deve essere abilitato. Consulta le pagine della guida del browser per le istruzioni.

Convenzioni dei documenti

Test di Amazon Aurora MySQL mediante query Fault Injection

Visualizzazione dello stato del volume per un cluster di database Aurora