Nuevo comportamiento de tabla temporal en Aurora MySQL versión 3 - Amazon Aurora
Motor de almacenamiento para tablas temporales internas (implícitas)Limitación del tamaño de las tablas temporales internas en memoriaMitigación de los problemas de llenado de las tablas temporales internas en las réplicas de AuroraTablas temporales creadas por el usuario (explícitas) en las instancias de base de datos del lectorErrores de creación de tablas temporales y su mitigación

Nuevo comportamiento de tabla temporal en Aurora MySQL versión 3

La versión 3 de Aurora MySQL gestiona las tablas temporales de forma diferente a las versiones anteriores de Aurora MySQL. Este nuevo comportamiento se hereda de MySQL 8.0 Community Edition. Hay dos tipos de tablas temporales que se pueden crear con la versión 3 de Aurora MySQL:

  • Tablas temporales internas (o implícitas): las crea el motor de Aurora MySQL para gestionar algunas operaciones, como agregación de ordenación, tablas derivadas o expresiones comunes de tabla (CTE).

  • Tablas temporales creadas por el usuario (o explícitas): las crea el motor de Aurora MySQL cuando se utiliza la instrucción CREATE TEMPORARY TABLE.

Hay consideraciones adicionales para las tablas temporales, tanto internas como creadas por el usuario, en las instancias de base de datos del lector de Aurora. En las siguientes secciones se analizan estos cambios.

Motor de almacenamiento para tablas temporales internas (implícitas)

Al generar conjuntos de resultados intermedios, Aurora MySQL intenta inicialmente escribir en tablas temporales en memoria. Es posible que esto no se realice correctamente debido a tipos de datos incompatibles o a límites configurados. Si es así, la tabla temporal se convierte en una tabla temporal en el disco en lugar de mantenerse en la memoria. Puede encontrar más información al respecto en Internal Temporary Table Use in MySQL (Uso de tablas temporales internas en MySQL) de la documentación de MySQL.

En Aurora MySQL versión 3, la forma en que funcionan las tablas temporales internas es diferente de las versiones anteriores de Aurora MySQL. En lugar de elegir entre los motores de almacenamiento InnoDB y MyISAM para estas tablas temporales, ahora elige entre los motores de almacenamiento TempTable e InnoDB.

Con el motor de almacenamiento TempTable, puede tomar una opción adicional sobre cómo manejar ciertos datos. Los datos afectados desbordan el grupo de memoria que contiene todas las tablas temporales internas de la instancia de base de datos.

Estas opciones pueden influir en el rendimiento de las consultas que generan grandes volúmenes de datos temporales, por ejemplo, al realizar agregaciones tales como GROUP BY sobre tablas grandes.

sugerencia

Si la carga de trabajo incluye consultas que generan tablas temporales internas, confirme cómo funciona su aplicación con este cambio ejecutando puntos de referencia y monitoreando las métricas relacionadas con el rendimiento.

En algunos casos, la cantidad de datos temporales se ajusta al grupo de memoria TempTable o solo desborda el grupo de memoria en una pequeña cantidad. En estos casos, le recomendamos que utilice la configuración TempTable para tablas temporales internas y archivos asignados por memoria para contener los datos de desbordamiento. Esta configuración es la predeterminada.

El motor de almacenamiento TempTable es el predeterminado. TempTable utiliza un grupo de memoria común para todas las tablas temporales que utilizan este motor, en lugar de un límite máximo de memoria por tabla. El tamaño de este grupo de memoria se especifica mediante el parámetro temptable_max_ram. El valor predeterminado es 1 GiB en instancias de base de datos con 16 GiB o más de memoria y 16 MB en instancias de base de datos con menos de 16 GiB de memoria. El tamaño del grupo de memoria influye en el consumo de memoria a nivel de sesión.

En algunos casos, al utilizar el motor de almacenamiento TempTable, los datos temporales pueden superar el tamaño del grupo de memoria. Si es así, Aurora MySQL almacena los datos de desbordamiento mediante un mecanismo secundario.

Puede establecer el parámetro temptable_max_mmap para especificar si los datos se desbordan a archivos temporales asignados a memoria o a tablas temporales internas de InnoDB en disco. Los distintos formatos de datos y criterios de desbordamiento de estos mecanismos de desbordamiento pueden afectar al rendimiento de las consultas. Lo hacen al influir en la cantidad de datos escritos en el disco y en la demanda de rendimiento del almacenamiento en disco.

Aurora MySQL almacena los datos de desbordamiento de forma diferente según el destino de desbordamiento de datos que elija y si la consulta se ejecuta en una instancia de base de datos de escritor o lector:

  • En la instancia de escritor, los datos que se desbordan en las tablas temporales internas de InnoDB se almacenan en el volumen del clúster de Aurora.

  • En la instancia del escritor, los datos que se desbordan a archivos temporales asignados a memoria residen en el almacenamiento local de la instancia de Aurora MySQL versión 3.

  • En las instancias de lector, los datos de desbordamiento siempre residen en los archivos temporales asignados a memoria del almacenamiento local. Esto se debe a que las instancias de solo lectura no pueden almacenar datos en el volumen del clúster de Aurora.

Los parámetros de configuración relacionados con las tablas temporales internas se aplican de forma diferente a las instancias de escritor y lector del clúster:

  • En instancias de lector, Aurora MySQL siempre utiliza el motor de almacenamiento TempTable.

  • El tamaño de temptable_max_mmap tiene un valor predeterminado de 1 GiB, tanto para las instancias de escritor como de lector, independientemente del tamaño de la memoria de la instancia de base de datos. Puede ajustar este valor tanto en instancias de escritor como de lector.

  • Al configurar temptable_max_mmap en 0, se desactiva el uso de archivos temporales asignados a memoria en las instancias de escritor.

  • No puede establecer temptable_max_mmap en 0 en instancias de lector.

nota

No recomendamos utilizar el parámetro temptable_use_mmap. Ha quedado obsoleto y se espera que se elimine en una futura versión de MySQL.

Limitación del tamaño de las tablas temporales internas en memoria

Como se explica en Motor de almacenamiento para tablas temporales internas (implícitas), puede controlar los recursos de la tabla temporal a nivel global mediante las configuraciones temptable_max_ram y temptable_max_mmap.

También puede limitar el tamaño de cualquier tabla temporal interna individual en memoria mediante el parámetro de base de datos tmp_table_size. Este límite tiene por objeto evitar que las consultas individuales consuman una cantidad desmesurada de recursos de las tablas temporales globales, lo que puede afectar al rendimiento de las consultas simultáneas que requieren estos recursos.

El parámetro tmp_table_size define el tamaño máximo de las tablas temporales creadas por el motor de almacenamiento MEMORY en la versión 3 y versiones posteriores de Aurora MySQL.

En la versión 3.04 y versiones posteriores de Aurora MySQL, tmp_table_size también define el tamaño máximo de las tablas temporales creadas por el motor de almacenamiento TempTable cuando el  parámetro de base de datos aurora_tmptable_enable_per_table_limit está establecido en ON. Este comportamiento está deshabilitado de forma predeterminada (OFF), lo que es el mismo comportamiento que en la versión 3.03 y anteriores de Aurora MySQL.

  • Cuándo aurora_tmptable_enable_per_table_limit está establecido en OFF, tmp_table_size no se tiene en cuenta para las tablas temporales internas en memoria creadas por el motor de almacenamiento TempTable.

    Sin embargo, se sigue aplicando el límite de recursos global de TempTable. Aurora MySQL tiene el siguiente comportamiento cuando se ha alcanzado el límite de recursos global de TempTable:

    • Instancias de base de datos del escritor: Aurora MySQL convierte automáticamente la tabla temporal en memoria en una tabla temporal en disco de InnoDB.

    • Instancias de base de datos del lector: la consulta finaliza con un error.

      ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlxx_xxx' is full

  • Cuándo aurora_tmptable_enable_per_table_limit está establecido en ON, Aurora MySQL tiene el siguiente comportamiento cuando se alcanza el límite de tmp_table_size:

    • Instancias de base de datos del escritor: Aurora MySQL convierte automáticamente la tabla temporal en memoria en una tabla temporal en disco de InnoDB.

    • Instancias de base de datos del lector: la consulta finaliza con un error.

      ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlxx_xxx' is full

      En este caso se aplica tanto el límite de recursos global como el límite por tabla de TempTable.

nota

El parámetro  aurora_tmptable_enable_per_table_limit no tiene ningún efecto cuando internal_tmp_mem_storage_engine está configurado en MEMORY. En este caso, el tamaño máximo de una tabla temporal en memoria se define mediante el valor tmp_table_size o max_heap_table_size , el que sea menor.

Los siguientes ejemplos muestran el comportamiento del parámetro aurora_tmptable_enable_per_table_limit para instancias de base de datos del escritor y lector.

ejemplo Ejemplo de una instancia de base de datos del escritor con aurora_tmptable_enable_per_table_limit establecido en OFF

La tabla temporal en memoria no se convierte en una tabla temporal en disco de InnoDB.

mysql> set aurora_tmptable_enable_per_table_limit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@temptable_max_ram,@@temptable_max_mmap;
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@temptable_max_ram | @@temptable_max_mmap |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
|                  0 | 3.04.0           |                                        0 |          1073741824 |           1073741824 |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
1 row in set (0.00 sec)

mysql> show status like '%created_tmp_disk%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.00 sec)

mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 60000000) SELECT max(n) FROM cte;
+----------+
| max(n)   |
+----------+
| 60000000 |
+----------+
1 row in set (13.99 sec)

mysql> show status like '%created_tmp_disk%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)
ejemplo Ejemplo de una instancia de base de datos del escritor con aurora_tmptable_enable_per_table_limit establecido en ON

La tabla temporal en memoria se convierte en una tabla temporal en disco de InnoDB.

mysql> set aurora_tmptable_enable_per_table_limit=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@tmp_table_size;
+--------------------+------------------+------------------------------------------+------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@tmp_table_size |
+--------------------+------------------+------------------------------------------+------------------+
|                  0 | 3.04.0           |                                        1 |         16777216 |
+--------------------+------------------+------------------------------------------+------------------+
1 row in set (0.00 sec)

mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.00 sec)

mysql> show status like '%created_tmp_disk%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 6000000) SELECT max(n) FROM cte;
+---------+
| max(n)  |
+---------+
| 6000000 |
+---------+
1 row in set (4.10 sec)

mysql> show status like '%created_tmp_disk%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     |
+-------------------------+-------+
1 row in set (0.00 sec)
ejemplo Ejemplo de una instancia de base de datos del lector con aurora_tmptable_enable_per_table_limit establecido en OFF

La consulta finaliza sin errores porque no se aplica tmp_table_size, y no se ha alcanzado el límite de recursos global de TempTable.

mysql> set aurora_tmptable_enable_per_table_limit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@temptable_max_ram,@@temptable_max_mmap;
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@temptable_max_ram | @@temptable_max_mmap |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
|                  1 | 3.04.0           |                                        0 |          1073741824 |           1073741824 |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
1 row in set (0.00 sec)

mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.00 sec)

mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 60000000) SELECT max(n) FROM cte;
+----------+
| max(n)   |
+----------+
| 60000000 |
+----------+
1 row in set (14.05 sec)
ejemplo Ejemplo de una instancia de base de datos del lector con aurora_tmptable_enable_per_table_limit establecido en OFF

Esta consulta alcanza el límite global de recursos global de TempTable con aurora_tmptable_enable_per_table_limit configurado en OFF. La consulta finaliza con un error en las instancias del lector.

mysql> set aurora_tmptable_enable_per_table_limit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@temptable_max_ram,@@temptable_max_mmap;
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@temptable_max_ram | @@temptable_max_mmap |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
|                  1 | 3.04.0           |                                        0 |          1073741824 |           1073741824 |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
1 row in set (0.00 sec)

mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.01 sec)

mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 120000000) SELECT max(n) FROM cte;
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
ejemplo Ejemplo de una instancia de base de datos del lector con aurora_tmptable_enable_per_table_limit establecido en ON

La consulta finaliza con un error cuando se ha alcanzado el límite de tmp_table_size.

mysql> set aurora_tmptable_enable_per_table_limit=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@tmp_table_size;
+--------------------+------------------+------------------------------------------+------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@tmp_table_size |
+--------------------+------------------+------------------------------------------+------------------+
|                  1 | 3.04.0           |                                        1 |         16777216 |
+--------------------+------------------+------------------------------------------+------------------+
1 row in set (0.00 sec)

mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.00 sec)

mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 6000000) SELECT max(n) FROM cte;
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_8_2' is full

Mitigación de los problemas de llenado de las tablas temporales internas en las réplicas de Aurora

Para evitar problemas de limitación del tamaño de las tablas temporales, establezca los parámetros temptable_max_ram y temptable_max_mmap a un valor combinado que pueda ajustarse a los requisitos de su carga de trabajo.

Tenga cuidado al establecer el valor del parámetro temptable_max_ram. Si se establece un valor demasiado alto, se reduce la memoria disponible en la instancia de base de datos, lo que puede provocar una situación de falta de memoria. Supervise el promedio de memoria que se puede liberar en la instancia de base de datos. A continuación, determine un valor adecuado para temptable_max_ram, de modo que le quede una cantidad razonable de memoria libre en la instancia. Para obtener más información, consulte Problemas de memoria que se puede liberar en Amazon Aurora.

También es importante supervisar el tamaño del almacenamiento local y el consumo de espacio de las tablas temporales. Para obtener más información sobre cómo supervisar el almacenamiento local en una instancia, consulte el artículo del Centro de conocimientos de AWS What is stored in Aurora MySQL-compatible local storage, and how can I troubleshoot local storage issues? (¿Qué se guarda en el almacenamiento local compatible con Aurora MySQL y cómo puedo solucionar los problemas de almacenamiento local?).

nota

Este procedimiento no funciona cuando el parámetro aurora_tmptable_enable_per_table_limit está establecido en ON. Para obtener más información, consulte Limitación del tamaño de las tablas temporales internas en memoria.

ejemplo 1

Sabe que sus tablas temporales crecen hasta un tamaño acumulado de 20 GiB. Desea establecer las tablas temporales en memoria a 2 GiB y que crezcan hasta un máximo de 20 GiB en disco.

Establezca temptable_max_ram en 2,147,483,648 y temptable_max_mmap en 21,474,836,480. Estos valores están expresados en bytes.

Esta configuración de parámetros garantiza que las tablas temporales puedan crecer hasta alcanzar un total acumulado de 22 GiB.

ejemplo 2

El tamaño de su instancia actual es 16xlarge o superior. No sabe el tamaño total de las tablas temporales que podría necesitar. Desea poder utilizar hasta 4 GiB en la memoria y hasta el tamaño máximo de almacenamiento disponible en el disco.

Establezca temptable_max_ram en 4,294,967,296 y temptable_max_mmap en 1,099,511,627,776. Estos valores están expresados en bytes.

Aquí se establece temptable_max_mmap a 1 TiB, que es menos que el almacenamiento local máximo de 1,2 TiB en una instancia de base de datos 16xlarge de Aurora.

En una instancia de menor tamaño, ajuste el valor de temptable_max_mmap para que no llene el almacenamiento local disponible. Por ejemplo, una instancia 2xlarge solo dispone de 160 GiB de almacenamiento local. Por lo tanto, recomendamos establecer el valor a menos de 160 GiB. Para obtener más información sobre el almacenamiento local disponible para los tamaños de instancia de base de datos, consulte Límites de almacenamiento temporal de Aurora MySQL.

Tablas temporales creadas por el usuario (explícitas) en las instancias de base de datos del lector

Puede crear tablas temporales explícitas mediante la palabra clave TEMPORARY en la instrucción CREATE TABLE. Las tablas temporales explícitas se admiten en la instancia de base de datos del escritor en un clúster de base de datos de Aurora. También puede utilizar tablas temporales explícitas en instancias de base de datos del lector, pero las tablas no pueden imponer el uso del motor de almacenamiento InnoDB.

Para evitar errores al crear tablas temporales explícitas en las instancias de la base de datos de escritor de Aurora MySQL, asegúrese de que ejecuta todas las instrucciones CREATE TEMPORARY TABLE de una o ambas de las siguientes maneras:

  • No especifique la cláusula ENGINE=InnoDB.

  • No establezca el modo SQL en NO_ENGINE_SUBSTITUTION.

Errores de creación de tablas temporales y su mitigación

El error que recibe difiere en función de si utiliza una simple instrucción CREATE TEMPORARY TABLE o variante CREATE TEMPORARY TABLE AS SELECT. Los siguientes ejemplos muestran los diferentes tipos de errores.

Este comportamiento de tabla temporal solo se aplica a instancias de solo lectura. Este primer ejemplo confirma que ese es el tipo de instancia a la que está conectada la sesión.

mysql> select @@innodb_read_only;
+--------------------+
| @@innodb_read_only |
+--------------------+
|                  1 |
+--------------------+

Para instrucciones CREATE TEMPORARY TABLE simples, la instrucción falla cuando el modo SQL NO_ENGINE_SUBSTITUTION está activado. Cuando NO_ENGINE_SUBSTITUTION está desactivado (de forma predeterminada), se realiza la sustitución del motor adecuado y la creación de la tabla temporal se lleva a cabo correctamente.

mysql> set sql_mode = 'NO_ENGINE_SUBSTITUTION';

mysql>  CREATE TEMPORARY TABLE tt2 (id int) ENGINE=InnoDB;
ERROR 3161 (HY000): Storage engine InnoDB is disabled (Table creation is disallowed).

mysql> SET sql_mode = '';

mysql> CREATE TEMPORARY TABLE tt4 (id int) ENGINE=InnoDB;

mysql> SHOW CREATE TABLE tt4\G
*************************** 1. row ***************************
       Table: tt4
Create Table: CREATE TEMPORARY TABLE `tt4` (
  `id` int DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Para instrucciones CREATE TEMPORARY TABLE AS SELECT, la instrucción falla cuando el modo SQL NO_ENGINE_SUBSTITUTION está activado. Cuando NO_ENGINE_SUBSTITUTION está desactivado (de forma predeterminada), se realiza la sustitución del motor adecuado y la creación de la tabla temporal se lleva a cabo correctamente.

mysql> set sql_mode = 'NO_ENGINE_SUBSTITUTION';

mysql> CREATE TEMPORARY TABLE tt1 ENGINE=InnoDB AS SELECT * FROM t1;
ERROR 3161 (HY000): Storage engine InnoDB is disabled (Table creation is disallowed).

mysql> SET sql_mode = '';

mysql> show create table tt3;
+-------+----------------------------------------------------------+
| Table | Create Table                                             |
+-------+----------------------------------------------------------+
| tt3   | CREATE TEMPORARY TABLE `tt3` (
  `id` int DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------+
1 row in set (0.00 sec)

Para obtener más información sobre los aspectos de almacenamiento y las implicaciones de rendimiento de las tablas temporales en Aurora MySQL versión 3, consulte la entrada del blog Use the TempTable storage engine on Amazon RDS for MySQL and Amazon Aurora MySQL.