¿Cómo soluciono los problemas temporales de tablas y almacenamiento completo en Amazon RDS y ediciones de Aurora compatibles con MySQL?

Descripción corta

Al ejecutar consultas en Amazon RDS o en una versión compatible con Aurora MySQL, es posible que aparezca el siguiente mensaje de error:

«The table '/rdsdbdata/tmp/#sqlxx_###' is full»

Aparece el mensaje de error anterior cuando utilizaste todo el almacenamiento de tablas temporal de tu instancia compatible con Amazon RDS o Aurora MySQL. También recibes el mensaje de error cuando ejecutas consultas que generan tablas temporales de gran tamaño que superan las cuotas de almacenamiento configuradas.

Resolución

Resolución de tablas temporales completas

MySQL 8.0 y la versión 3.x compatible con Aurora MySQL utilizan un motor de almacenamiento TempTable para consultas complejas que requieren almacenar resultados intermedios. 

El motor de almacenamiento TempTable crea tablas temporales en la memoria, hasta el tamaño predeterminado del parámetro temptable_max_ram. Cuando se supera la cuota, las tablas temporales utilizan archivos asignados en memoria para desbordarse en el almacenamiento en disco, hasta el tamaño predeterminado del parámetro temptable_max_mmap.

Si los datos temporales que generan las consultas superan el tamaño predeterminado del parámetro temptable_max_mmap, es posible que aparezca el error «Table is full».

Para solucionar este problema, toma las siguientes medidas:

• Aumenta los parámetros temptable_max_ram y temptable_max_mmap para permitir más memoria y espacio en disco para las tablas temporales. Para obtener más información, consulta temptable_max_ram y temptable_max_mmap en el sitio web de MySQL.
  Nota: El parámetro temptable_max_mmap no debe superar el 80 % del tamaño de almacenamiento local de la instancia de Aurora.
• Establece el parámetro internal_tmp_mem_storage_engine en MEMORY para establezca la cuota de disco de temptable_max_mmap. Además, define los parámetros tmp_table_size y max_heap_table_size para limitar el tamaño de las tablas temporales internas individuales. Para obtener más información, consulta internal_temp_mem_storage_engine, tmp_table_size y max_heap_table_size en el sitio web de MySQL.
  Nota: La configuración MEMORY solo se aplica a la instancia de escritura de Aurora. En las instancias de lectura, Aurora MySQL usa el motor de almacenamiento TempTable.
• Activa los parámetros aurora_tmptable_enable_per_table_limit y tune tmp_table_size para restringir la memoria máxima de cada tabla temporal interna.
• Utiliza la métrica FreeLocalStorage de Amazon CloudWatch para supervisar el espacio de almacenamiento local asociado a la instancia de base de datos Aurora. Para supervisar el espacio de almacenamiento de datos libre asociado a la instancia MySQL de RDS, utiliza la métrica FreeStorageSpace de CloudWatch.

Optimización de las consultas para reducir las tablas temporales

Algunas consultas pueden generar tablas temporales de gran tamaño que pueden provocar errores.

Para optimizar las consultas, lleva a cabo las siguientes acciones:

• Usa los índices apropiados, especialmente para las consultas que incluyan GROUP BY, ORDER BY, subconsultas y vistas en tablas con particiones.
• Usa la eliminación de particiones para las consultas en tablas con particiones, de modo que no analices toda la tabla.
• Utiliza índices para las consultas que hacen filesorts para generar tablas temporales implícitas. O bien, ajusta el parámetro sort_buffer_size.

Resolución de otras causas de problemas de almacenamiento

Los siguientes problemas también pueden provocar problemas de almacenamiento lleno en instancias compatibles con Amazon RDS y Aurora MySQL:

• Tamaños de bases de datos o tablas de gran tamaño que superan la capacidad de almacenamiento local. 
• Uso del espacio en disco local por parte de los registros de auditoría, los registros de consultas lentas o los registros de consultas generales.

Para resolver estos problemas, toma las siguientes medidas:

• Para las operaciones de DDL, como la reconstrucción de índices o la modificación de tablas, utiliza el kit de herramientas de Percona para no generar tablas temporales de gran tamaño. Para obtener más información, consulta pt-online-schema-change en el sitio web de Percona.
• Supervisa el uso del almacenamiento local.
• Optimiza las consultas y las estructuras de tablas.
• Desactiva los registros.
• Escala tu instancia de Aurora MySQL para aumentar la capacidad de almacenamiento local.

Información relacionada

Use the TempTable Storage Engine on Amazon RDS for MySQL and Amazon Aurora MySQL (Uso del motor de almacenamiento TempTable en Amazon RDS para MySQL y Amazon Aurora MySQL)

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

Límites de almacenamiento temporal de Aurora MySQL