DDL instantáneo (Aurora MySQL versión 3)DDL rápida (Aurora MySQL versión 2)

Modificación de las tablas de Amazon Aurora con DDL rápido

Amazon Aurora incluye optimizaciones para ejecutar una operación ALTER TABLE en su lugar, casi instantáneamente. La operación se completa sin que sea necesario copiar la tabla y sin que haya un impacto perceptible en otras instrucciones DML. Como la operación no consume almacenamiento temporal para una copia de la tabla, las instrucciones DDL resultan prácticas incluso para tablas grandes en clases de instancia pequeñas.

Aurora MySQL versión 3 es compatible con la característica de MySQL 8.0 llamada DDL instantáneo. La versión 2 de Aurora MySQL utiliza una implementación diferente denominada DDL rápido.

DDL instantáneo (Aurora MySQL versión 3)

La optimización realizada por Aurora MySQL versión 3 para mejorar la eficiencia de algunas operaciones de DDL se denomina DDL instantáneo.

Aurora MySQL versión 3 es compatible con el DDL instantáneo de la comunidad MySQL 8.0. Realice una operación DDL instantánea mediante la cláusula ALGORITHM=INSTANT con la instrucción ALTER TABLE. Para obtener información sobre la sintaxis y el uso de DDL instantáneo, consulte ALTER TABLE (Modificar tabla) y Online DDL Operations (Operaciones DDL en línea) en la documentación de MySQL.

En los siguientes ejemplos se muestra la característica DDL instantánea. Las instrucciones ALTER TABLE agregan columnas y cambian los valores predeterminados de columnas. Los ejemplos incluyen columnas regulares y virtuales, así como tablas regulares y particionadas. En cada paso, puede ver los resultados, para ello, emita las instrucciones SHOW CREATE TABLE y 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 rápida (Aurora MySQL versión 2)

El DDL rápido en Aurora MySQL es una optimización diseñada para mejorar el rendimiento de determinados cambios de esquema, como agregar o eliminar columnas, al reducir el tiempo de inactividad y el uso de recursos. Permite que estas operaciones se realicen de manera más eficiente en comparación con los métodos DDL tradicionales.

importante

Actualmente, debe habilitar el modo lab de Aurora para usar DDL rápida. Para obtener información sobre cómo habilitar el modo lab, consulte Modo lab de Amazon Aurora MySQL.

La optimización de DDL rápida se ha ingresado en el modo lab en la versión 2 de Aurora MySQL con el fin de mejorar la eficiencia de determinadas operaciones de DDL. En la versión 3 de Aurora MySQL, se ha interrumpido el modo lab y la DDL rápida se ha sustituido por la característica de MySQL 8.0 DDL instantáneo.

En MySQL, muchas operaciones de lenguaje de definición de datos (DDL) tienen un impacto considerable en el desempeño.

Por ejemplo, supongamos que usa una operación ALTER TABLE para agregar una columna a una tabla. En función del algoritmo especificado para la operación, esta puede incluir los siguientes pasos:

Crear una copia completa de la tabla
Crear una tabla temporal para procesar las operaciones simultáneas de lenguaje de manipulación de datos (DML)
Reconstruir todos los índices de la tabla
Aplicar bloqueos de tabla mientras se aplican los cambios simultáneos de DML
Ralentizar el rendimiento DML concurrente

Este impacto en el rendimiento puede resultar especialmente difícil en entornos con tablas grandes o volúmenes altos de transacciones. DDL rápida ayuda a mitigar estos desafíos al optimizar los cambios de esquema, lo que permite operaciones más rápidas y que requieren menos recursos.

Limitaciones rápidas de DDL

Actualmente, el DDL rápido tiene las siguientes limitaciones:

El DDL rápido solo admite la adición de columnas que puedan contener valores nulos, sin valores predeterminados, al final de una tabla existente.
El DDL rápido no funciona para tablas particionadas.
El DDL rápido no funciona para las tablas de InnoDB que usan el formato de fila REDUNDANT.
El DDL rápido no funciona para las tablas con índices de búsqueda de texto completo.
Si el tamaño máximo posible de registro para la operación DDL es demasiado grande, no se utiliza el DDL rápido. Un tamaño de registro es demasiado grande si es superior a la mitad del tamaño de la página. El tamaño máximo de un registro se computa sumando los tamaños máximos de todas las columnas. Para columnas de tamaño variable, según estándares de InnoDB, los bytes externos no se incluyen para computación.

Sintaxis DDL rápida


ALTER TABLE tbl_name ADD COLUMN col_name column_definition

Esta declaración puede usar las siguientes opciones:

tbl_name — el nombre de la tabla que se va a modificar.
col_name — el nombre de la columna que se va a añadir.
col_definition — la definición de la columna que se va a añadir.

nota
Debe especificar una definición de columna que pueda tener valores nulos sin un valor predeterminado. De lo contrario, no se usa el DDL rápido.

Ejemplos rápidos de DDL

Los siguientes ejemplos demuestran la aceleración de las operaciones con el DDL rápido. El primer ejemplo SQL ejecuta instrucciones ALTER TABLE en una tabla grande sin utilizar el DDL rápido. Esta operación lleva mucho tiempo. Un ejemplo de CLI muestra cómo habilitar el DDL rápido para el clúster. Luego, otro ejemplo SQL ejecuta las mismas instrucciones ALTER TABLE en una tabla idéntica. Con el DDL rápido habilitado, la operación es muy rápida.

En este ejemplo se utiliza la tabla ORDERS del punto de referencia TPC-H, que contiene 150 millones de filas. Este clúster utiliza intencionadamente una clase de instancia relativamente pequeña para demostrar cuánto tiempo pueden tardar las instrucciones ALTER TABLE cuando no se puede usar el DDL rápido. En el ejemplo se crea un clon de la tabla original que contiene datos idénticos. Al comprobar la configuración de aurora_lab_mode, se confirma que el clúster no puede usar el DDL rápido, ya que el modo lab no está habilitado. A continuación, las instrucciones ALTER TABLE ADD COLUMN tardan mucho tiempo en agregar nuevas columnas al final de la tabla.


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)

Este ejemplo realiza la misma preparación de una tabla grande que el ejemplo anterior. Sin embargo, no puede simplemente habilitar el modo de laboratorio dentro de una sesión SQL interactiva. Esa configuración debe estar habilitada en un grupo de parámetros personalizado. Para ello, es necesario salir de la mysql sesión y ejecutar algunos AWS comandos de la CLI o utilizar el 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

Habilitar el modo de laboratorio para el clúster requiere algo de trabajo con un grupo de parámetros. En este ejemplo de la CLI de AWS, se utiliza un grupo de parámetros de clúster a fin de asegurarse de que todas las instancias de base de datos del clúster utilicen el mismo valor para la configuración del modo de laboratorio.


$ 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

En el ejemplo siguiente se muestran los pasos restantes después de que surta efecto el cambio de grupo de parámetros. Prueba la configuración de aurora_lab_mode para asegurarse de que el clúster puede usar el DDL rápido. Luego ejecuta las instrucciones ALTER TABLE para agregar columnas al final de otra tabla grande. Esta vez, las instrucciones terminan muy rápidamente.


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)

Aviso JavaScript está desactivado o no está disponible en su navegador.

Para utilizar la documentación de AWS, debe estar habilitado JavaScript. Para obtener más información, consulte las páginas de ayuda de su navegador.

Convenciones del documento

Pruebas de Amazon Aurora MySQL por medio de consultas de inserción de errores

Visualización del estado del volumen para un clúster de base de datos de Aurora