Sofortige DDL (Aurora MySQL Version 3)Fast DDL (Aurora MySQL Version 2)

Ändern von Tabellen in Amazon Aurora mithilfe von Fast DDL

Amazon Aurora umfasst Optimierungen, um einen ALTER TABLE-Vorgang nahezu sofort auszuführen. Der Vorgang schließt ab, ohne dass ein Kopieren der Tabelle erforderlich ist und ohne eine materielle Auswirkung auf andere DML-Statements zu haben. Da kein temporärer Speicher für eine Tabellenkopie benötigt wird, haben DDL-Statements praktische Vorteile, sogar für große Tabellen auf kleinen Instance-Klassen.

Aurora MySQL Version 3 ist mit der MySQL 8.0-Funktion namens Instant DDL kompatibel. Aurora MySQL Version 2 verwendet eine andere Implementierung namens Fast DDL.

Sofortige DDL (Aurora MySQL Version 3)

Die von Aurora MySQL Version 3 durchgeführte Optimierung zur Verbesserung der Effizienz einiger DDL-Vorgänge wird als Instant DDL bezeichnet.

Aurora MySQL Version 3 ist mit der Instant DDL von Community MySQL 8.0 kompatibel. Sie führen einen sofortigen DDL-Vorgang durch, indem Sie die Klausel ALGORITHM=INSTANT mit der ALTER TABLE-Anweisung verwenden. Weitere Informationen zu Syntax und Verwendung von Instant DDL finden Sie unter ALTER TABLE und Online DDL Operations in der MySQL-Dokumentation.

Die folgenden Beispiele veranschaulichen die Instant-DDL-Funktion. Die ALTER TABLE-Anweisungen fügen Spalten hinzu und ändern die Standardspaltenwerte. Die Beispiele umfassen sowohl reguläre als auch virtuelle Spalten sowie reguläre und partitionierte Tabellen. Bei jedem Schritt können Sie die Ergebnisse durch die Ausgabe von SHOW CREATE TABLE- und DESCRIBE-Anweisungen anzeigen.


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)

In MySQL wirken sich viele Data Definition Language (DDL)-Vorgänge signifikant auf die Leistungsfähigkeit aus.

Nehmen wir beispielsweise an, dass Sie eine ALTER TABLE-Operation verwenden, um eine Spalte zu einer Tabelle hinzuzufügen. Abhängig vom angegebenen Algorithmus für den Vorgang kann der Vorgang Folgendes beinhalten:

Erstellen einer vollen Kopie für die Tabelle
Erstellen einer temporären Tabelle für die Verarbeitung simultaner Data Manipulation Language (DML)-Vorgänge
Erneutes Aufbauen aller Indizes für die Tabelle
Anwenden von Tabellensperren beim Vornehmen von simultanen DML-Änderungen
Verlangsamen des simultanen DML-Durchsatzes

Die von Aurora MySQL Version 2 durchgeführte Optimierung zur Verbesserung der Effizienz einiger DDL-Vorgänge wird als Fast DDL bezeichnet.

In Aurora MySQL Version 3 verwendet Aurora die MySQL 8.0-Funktion namens Instant DDL. Aurora MySQL Version 2 verwendet eine andere Implementierung namens Fast DDL.

Wichtig

Zurzeit muss der Aurora-Labormodus aktiviert werden, um Fast DDL für Aurora MySQL verwenden zu können. Die Verwendung von Fast DDL für Produktions-DB-Cluster wird nicht empfohlen. Weitere Informationen zum Aktivieren des Aurora-Labormodus finden Sie unter Amazon Aurora MySQL-Labor-Modus.

Schnelle DDL-Beschränkungen

Aktuell hat Fast DDL folgende Einschränkungen:

Sie unterstützt nur das Hinzufügen von löschbaren Spalten, ohne Standardwerte, bis zum Ende einer bestehenden Tabelle.
Schnelle DDL funktioniert nicht für partitionierte Tabellen.
Schnelle DDL funktioniert nicht für InnoDB-Tabellen, die das Zeilenformat REDUNDANT verwenden.
Schnelle DDL funktioniert nicht für Tabellen mit Volltextsuchindizes.
Wenn die maximal mögliche Datensatzgröße für die DDL-Operation zu groß ist, wird Fast DDL nicht verwendet. Eine Datensatzgröße ist zu groß, wenn sie größer als die Hälfte der Seitengröße ist. Die maximale Größe eines Datensatzes wird berechnet, indem die maximale Größe aller Spalten addiert wird. Bei Spalten variabler Größe werden nach InnoDB-Standards externe Bytes für die Berechnung nicht berücksichtigt.

Schnelle DDL-Syntax


ALTER TABLE tbl_name ADD COLUMN col_name column_definition

Dieses Statement verwendet die folgenden Optionen:

tbl_name — Der Name der Tabelle, die geändert werden soll.
col_name — Der Name der Spalte, die hinzugefügt werden soll.
col_definition — Die Definition der Spalte, die hinzugefügt werden soll.

Anmerkung
Sie müssen eine löschbare Spaltendefinition ohne einen Standardwert festlegen. Andernfalls kann Fast DDL nicht verwendet werden.

Fast DDL-Beispiele

Die folgenden Beispiele veranschaulichen die Beschleunigung von Fast-DDL-Operationen. Im ersten SQL-Beispiel werden ALTER TABLE-Anweisungen für eine große Tabelle ausgeführt, ohne Fast DDL zu verwenden. Diese Operation benötigt beträchtliche Zeit. Ein CLI-Beispiel zeigt, wie Fast DDL für den Cluster aktiviert wird. Dann führt ein anderes SQL-Beispiel dieselben ALTER TABLE Anweisungen für eine identische Tabelle aus. Mit aktiviertem Fast DDL ist der Betrieb sehr schnell.

In diesem Beispiel wird die ORDERS Tabelle aus dem TPC-H-Benchmark verwendet, die 150 Millionen Zeilen enthält. Dieser Cluster verwendet absichtlich eine relativ kleine Instance-Klasse, um zu demonstrieren, wie lange ALTER TABLE-Anweisungen dauern können, wenn Sie Fast DDL nicht verwenden können. Im Beispiel wird ein Klon der Originaltabelle erstellt, der identische Daten enthält. Eine Überprüfung der Einstellung für aurora_lab_mode bestätigt, dass der Cluster Fast DDL nicht verwenden kann, da der Labormodus nicht aktiviert ist. Dann brauchen ALTER TABLE ADD COLUMN Anweisungen beträchtliche Zeit, um am Ende der Tabelle neue Spalten hinzuzufügen.


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)

In diesem Beispiel wird die gleiche Vorbereitung einer großen Tabelle wie im vorherigen Beispiel durchgeführt. Sie können den Labor-Modus jedoch nicht einfach innerhalb einer interaktiven SQL-Sitzung aktivieren. Diese Einstellung muss in einer benutzerdefinierten Parametergruppe aktiviert sein. Dazu müssen Sie die mysql Sitzung verlassen und einige AWS CLI-Befehle ausführen oder die AWS Management Console verwenden.


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

Das Aktivieren des Labor-Modus für den Cluster erfordert einige Arbeiten an einer Parametergruppe. In diesem Beispiel für eine AWS CLI wird eine Clusterparametergruppe verwendet, um sicherzustellen, dass alle DB-Instances im Cluster den gleichen Wert für die Einstellung des Labor-Modus verwenden.


$ 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

Das folgende Beispiel zeigt die verbleibenden Schritte, nachdem die Änderung der Parametergruppe wirksam wird. Es testet die Einstellung für aurora_lab_mode, um sicherzustellen, dass der Cluster Fast DDL verwenden kann. Dann führt es ALTER TABLE Anweisungen aus, um Spalten am Ende einer anderen großen Tabelle hinzuzufügen. Dieses Mal werden die Anweisungen sehr schnell beendet.


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)

Warnung JavaScript ist in Ihrem Browser nicht verfügbar oder deaktiviert.

Zur Nutzung der AWS-Dokumentation muss JavaScript aktiviert sein. Weitere Informationen finden auf den Hilfe-Seiten Ihres Browsers.

Dokumentkonventionen

Testen von Amazon Aurora MySQL unter Verwendung von Fehlersimulationsabfragen

Anzeigen des Volume-Status für einen Aurora-DB-Cluster