¿Por qué el almacenamiento de mi instancia de Amazon RDS para MySQL o MariaDB está lleno?

Breve descripción

Para solucionar un problema de almacenamiento lleno, primero debe analizar el espacio total utilizado en la instancia de base de datos. El espacio de la instancia de base de datos se usa para lo siguiente:

• Bases de datos creadas por usuarios
• Tablas temporales
• Registros binarios o registros de retransmisión de instancias en espera de MySQL (si usa una réplica de lectura)
• Espacio de tablas InnoDB
• Registros generales, registros de consultas lentas y registros de errores

Puede recuperar espacio de almacenamiento después de identificar qué lo consume. En lo sucesivo, monitoree la métrica FreeStorageSpace para evitar volver a quedarse sin espacio.

Nota: Si se produce una disminución repentina del almacenamiento disponible, ejecute el comando SHOW FULL PROCESSLIST para comprobar las consultas en curso en el nivel de instancia de base de datos. El comando SHOW FULL PROCESSLIST proporciona información sobre todas las conexiones y las consultas activas de cada conexión. Para revisar las transacciones que han estado activas durante mucho tiempo, ejecute el comando INFORMATION_SCHEMA.INNODB_TRX o SHOW ENGINE INNODB STATUS. A continuación, revise el resultado.

Solución

Analice el espacio total utilizado en la instancia de base de datos (bases de datos creadas por el usuario)

Para encontrar el tamaño de cada base de datos creada por el usuario, ejecute la siguiente consulta:

mysql> SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024/1024,2) "size in GB" FROM information_schema.tables GROUP BY 1 ORDER BY 2 DESC;

Para comprobar el tamaño de cada tabla para una base de datos concreta (en su instancia de base de datos), ejecute la siguiente consulta:

mysql> SELECT table_schema "DB Name", table_name,(data_length + index_length)/1024/1024/1024 AS "TableSizeinGB" from information_schema.tables where table_schema='database_name';

Para obtener tamaños de tablas más precisos en MySQL versión 5.7 y superior o MySQL 8.0 y superior, utilice la siguiente consulta:
Nota: La consulta information_schema.files no es aplicable a los motores MariaDB.

mysql> SELECT file_name, ROUND(SUM(total_extents * extent_size)/1024/1024/1024,2) AS "TableSizeinGB" from information_schema.files where file_name like '%/database_name/%';

Para obtener todos los detalles del almacenamiento y un espacio fragmentado aproximado en el nivel de base de datos y tabla, ejecute la siguiente consulta:
Nota: Esta consulta no es aplicable a las tablas alojadas en un espacio de tablas compartido.

mysql> SELECT table_schema AS "DB_NAME", SUM(size) "DB_SIZE", SUM(fragmented_space) APPROXIMATED_FRAGMENTED_SPACE_GB FROM (SELECT table_schema, table_name, ROUND((data_length+index_length+data_free)/1024/1024/1024,2) AS size, ROUND((data_length - (AVG_ROW_LENGTH*TABLE_ROWS))/1024/1024/1024,2)
    AS fragmented_space FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema NOT IN ('performance_schema', 'mysql', 'information_schema') ) AS TEMP GROUP BY DB_NAME ORDER BY APPROXIMATED_FRAGMENTED_SPACE_GB DESC;

mysql> SELECT table_schema DB_NAME, table_name TABLE_NAME, ROUND((data_length+index_length+data_free)/1024/1024/1024,2) SIZE_GB, ROUND((data_length - (AVG_ROW_LENGTH*TABLE_ROWS))/1024/1024/1024,2) APPROXIMATED_FRAGMENTED_SPACE_GB from information_schema.tables
    WHERE table_type='BASE TABLE' AND table_schema NOT IN ('performance_schema', 'mysql', 'information_schema') ORDER BY APPROXIMATED_FRAGMENTED_SPACE_GB DESC;

Registre los tamaños de las bases de datos obtenidos con estas dos consultas y compárelos con las métricas de Amazon CloudWatch en Amazon RDS. Así podrá confirmar si el almacenamiento está lleno por el consumo de datos.

Tablas temporales

Las tablas temporales creadas por los usuarios de InnoDB y las tablas temporales internas en el disco se crean en un archivo de espacio de tablas temporales denominado ibtmp1. A veces, el archivo de espacio de tablas temporales puede incluso ampliarse con ibtmp2 en el directorio de datos de MySQL.

Consejo: Si la tabla temporal (ibtmp1) consume demasiado almacenamiento, reinicie la instancia de base de datos para liberar espacio.

Las operaciones DDL en línea utilizan archivos de registros temporales para lo siguiente:

• Registro simultáneo de DML
• Creación de archivos de clasificación temporales al crear un índice
• Creación de archivos temporales de tablas intermedias cuando se reconstruyen tablas (para que las tablas temporales puedan ocupar espacio de almacenamiento)

Nota: Los tamaños de los archivos del espacio de tablas InnoDB solo se pueden consultar con MySQL versión 5.7 y superior o MySQL 8.0 y superior.

Para buscar el espacio de tablas temporales InnoDB, ejecute la siguiente consulta:

mysql> SELECT file_name, tablespace_name, table_name, engine, index_length, total_extents, extent_size from information_schema.files WHERE file_name LIKE '%ibtmp%';

Para recuperar espacio en el disco ocupado por un archivo de datos de espacio de tablas temporales global, reinicie el servidor MySQL o reinicie la instancia de base de datos. Para obtener más información, consulte The temporary tablespace en el sitio web de MySQL.

Espacio de tablas InnoDB

A veces, MySQL crea tablas temporales internas que no se pueden eliminar por la intervención de una consulta. Estas tablas temporales no forman parte de la tabla denominada «tables» dentro de information_schema. Para obtener más información, consulte Internal temporary table use in MySQL en el sitio web de MySQL.

Ejecute la siguiente consulta para buscar estas tablas temporales internas:

mysql> SELECT * FROM information_schema.innodb_sys_tables WHERE name LIKE '%#%';

El espacio de tablas del sistema InnoDB es el área de almacenamiento del diccionario de datos InnoDB. Además del diccionario de datos, el espacio de tablas del sistema InnoDB contiene el búfer de doble escritura, el búfer de cambios y los registros de acciones deshechas. Además, el espacio de tablas puede contener datos de índices y tablas si las tablas se crean en el espacio de tablas del sistema (en lugar de en espacios de tablas generales o de archivo por tabla).

Ejecute la siguiente consulta para buscar el espacio de tablas del sistema InnoDB:

mysql> SELECT file_name, tablespace_name, table_name, engine, index_length, total_extents, extent_size from information_schema.files WHERE file_name LIKE '%ibdata%';

Nota: Esta consulta se ejecuta en MySQL 5.7 y superior o en MySQL 8.0 y superior.

Una vez aumentado el tamaño del espacio de tablas del sistema, no podrá reducirlo. Sin embargo, puede volcar todas las tablas de InnoDB e importarlas a una nueva instancia de base de datos de MySQL. Para evitar grandes espacios de tablas en el sistema, plantéese usar espacios de tablas de archivo por tabla. Para obtener más información, consulte File-per-table tablespaces en el sitio web de MySQL.

Si habilita Innodb_file_per_table, cada tabla almacenará los datos y el índice en su propio archivo de espacio de tablas. Para recuperar el espacio (debido a la fragmentación de bases de datos y tablas), ejecute OPTIMIZE TABLE en esa tabla. El comando OPTIMIZE TABLE crea una nueva copia vacía de la tabla. A continuación, los datos de la tabla anterior se copian fila por fila en la nueva tabla. Durante este proceso, se crea un nuevo espacio de tabla .ibd y se recupera espacio. Para obtener más información sobre este proceso, consulte OPTIMIZE TABLE statement en el sitio web de MySQL.

Importante: El comando OPTIMIZE TABLE usa el algoritmo COPY para crear tablas temporales del mismo tamaño que la tabla original. Confirme que dispone de suficiente espacio en el disco antes de ejecutar este comando.

Para optimizar la tabla, ejecute la siguiente sintaxis de comandos:

mysql> OPTIMIZE TABLE <tablename>;

Como alternativa, también puede ejecutar el siguiente comando para reconstruir la tabla:

mysql> ALTER TABLE <table_name> ENGINE=INNODB;

Registros binarios

Si activa las copias de seguridad automatizadas en la instancia de Amazon RDS, los registros binarios también se activan automáticamente en la instancia de base de datos. Estos registros binarios se almacenan en el disco y consumen espacio de almacenamiento, pero se purgan con cada configuración de retención de registros binarios. El valor de retención del registro binario predeterminado para su instancia también está establecido en «Null», lo que significa que el archivo se elimina de inmediato.

Para evitar problemas de falta de espacio de almacenamiento, defina el periodo de retención de registros binarios adecuado en Amazon RDS para MySQL. Puede revisar el número de horas que se conserva un registro binario con la sintaxis de comando mysql.rds_show_configuration:

CALL mysql.rds_show_configuration;

También puede reducir este valor para conservar los registros durante un periodo más corto a fin de reducir la cantidad de espacio que utilizan. El valor NULL significa que los registros se purgan lo antes posible. Si hay una instancia en espera para la instancia activa, monitoree la métrica ReplicaLag en la instancia en espera. La métrica ReplicaLag indica cualquier retraso que se produzca durante el procesamiento del registro binario en la instancia activa o los registros de retransmisión en la instancia en espera.

Si hay una instancia en espera para la instancia activa, monitoree la métrica ReplicaLag en la instancia en espera. La métrica ReplicaLag indica cualquier retraso durante la purga del registro binario en la instancia activa y el registro de retransmisión en la instancia en espera. Si hay problemas de purga o replicación, estos registros binarios pueden acumularse con el tiempo y consumir espacio adicional en el disco. Para comprobar la cantidad de registros binarios de una instancia y el tamaño del archivo, utilice el comando SHOW BINARY LOGS. Para obtener más información, consulte SHOW BINARY LOGS statement en el sitio web de MySQL.

Si la instancia de base de datos actúa como una instancia de replicación en espera, compruebe el tamaño del valor de los registros de retransmisión (Relay_Log_Space) mediante el siguiente comando:

SHOW SLAVE STATUS\G

Registros de MySQL (registros generales, registros de consultas lentas y registros de errores)

Amazon RDS para MySQL proporciona registros (como registros generales, registros de consultas lentas y registros de errores) que se pueden usar para monitorear la base de datos. Los registros de errores están activos de forma predeterminada. Sin embargo, los registros generales y los registros de consultas lentas se pueden activar mediante un grupo de parámetros personalizados en la instancia de RDS. Una vez activados los registros de consultas lentas y los registros generales, se almacenan automáticamente en las tablas slow_log y general_log, dentro de la base de datos de MySQL. Para comprobar el tamaño de las consultas lentas, los registros generales (del tipo «FILE») y los registros de errores, visualice un listado de los archivos de registro de la base de datos.

Si el registro de consultas lentas y las tablas de registros generales consumen demasiado almacenamiento, rote manualmente las tablas de registros para administrar los registros de MySQL basados en tablas. Para eliminar por completo los datos antiguos y recuperar espacio en el disco, ejecute los siguientes comandos dos veces seguidas:

mysql> CALL mysql.rds_rotate_slow_log;
mysql> CALL mysql.rds_rotate_general_log;

Nota: Las tablas no proporcionan el tamaño de archivo exacto de los registros. Modifique el parámetro para que el valor de log_output para slow_log y general_log sea «File» en lugar de «Table».

También se recomienda monitorear la instancia de base de datos de Amazon RDS con Amazon CloudWatch. Puede configurar alarmas de CloudWatch para la métrica FreeStorageSpace con el fin de recibir alertas cada vez que su espacio de almacenamiento descienda por debajo de un determinado valor umbral. Por último, monitoree la métrica FreeStorageSpace mediante la configuración de una alarma de CloudWatch para recibir notificaciones cuando la instancia de base de datos tenga poco espacio libre. Para obtener más información, consulte ¿Cómo puedo crear alarmas de CloudWatch para monitorear el espacio de almacenamiento gratuito de Amazon RDS y evitar problemas porque el almacenamiento está lleno?

Además, puede usar la característica de escalado automático del almacenamiento de Amazon RDS para administrar la capacidad automáticamente. Con el escalado automático del almacenamiento, no es necesario escalar verticalmente el almacenamiento de la base de datos de forma manual. Para obtener más información sobre el escalado automático del almacenamiento de Amazon RDS, consulte Uso de almacenamiento para instancias de base de datos de Amazon RDS.

---

Información relacionada

How do I resolve problems with my Amazon RDS for MySQL DB instance that's using more storage than expected?