Why is my Amazon RDS for MySQL or MariaDB instance showing as storage full?

9 minute read
0

My Amazon Relational Database Service (Amazon RDS) for MySQL or MariaDB instance is showing as storage full. Why is this happening and how do I view what is using storage in my DB instance?

Short description

To troubleshoot a storage full issue, you must first analyze the total space used on your DB instance. Space on your DB instance is used for the following:

  • User-created databases
  • Temporary tables
  • Binary logs or MySQL standby instance relay logs (if you use a read replica)
  • InnoDB tablespace
  • General logs, slow query logs, and error logs

After you identify what's using storage space, you can reclaim storage space. Then, monitor the FreeStorageSpace metric to avoid running out of space again.

Note: If there's a sudden decrease in available storage, check ongoing queries at the DB instance level by running the SHOW FULL PROCESSLIST command. The SHOW FULL PROCESSLIST command provides information about all active connections and queries that are performed by each connection. To review the transactions that have been active for a long time, run the INFORMATION_SCHEMA.INNODB_TRX or SHOW ENGINE INNODB STATUS command. Then, review the output.

Resolution

Analyze the total space used on the DB instance (user-created databases)

To find the size of each user-created database, run the following query:

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;

To check the size of each table for a particular database (in your DB instance), run the following query:

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';

To get more accurate tables sizes in MySQL version 5.7 and higher, or MySQL 8.0 and higher, use the following query:
Note: The information_schema.files query is not applicable to MariaDB engines.

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/%';

To obtain complete storage details and approximate fragmented space at the database level and table level, run the following query:
Note: This query is not applicable to tables residing in shared tablespace.

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;

Record the database sizes acquired from these two queries and compare them to the Amazon CloudWatch metrics in Amazon RDS. You can then confirm whether the full storage is caused because of data usage.

Temporary tables

InnoDB user-created temporary tables and on-disk internal temporary tables are created in a temporary tablespace file named ibtmp1. Sometimes, the temporary tablespace file can even extend to ibtmp2 in the MySQL data directory.

Tip: If the temporary table (ibtmp1) uses excessive storage, reboot the DB instance to release the space.

The online DDL operations use temporary log files for the following:

  • Recording concurrent DML
  • Creating temporary sort files when an index is created
  • Creating temporary intermediate tables files when tables are rebuilt (so that temporary tables can occupy storage)

Note: File sizes of the InnoDB tablespace can be queried only using MySQL version 5.7 and higher, or MySQL 8.0 and higher.

To find the InnoDB temporary tablespace, run the following query:

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

To reclaim disk space that's occupied by a global temporary tablespace data file, restart the MySQL server or reboot your DB instance. For more information, see The temporary tablespace on the MySQL website.

InnoDB table space

Sometimes MySQL will create internal temporary tables that can't be removed because a query is intervening. These temporary tables aren't part of the table named "tables" inside information_schema. For more information, see Internal temporary table use in MySQL on the MySQL website.

Run the following query to find these internal temporary tables:

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

The InnoDB system tablespace is the storage area for the InnoDB data dictionary. Along with the data dictionary, the doublewrite buffer, change buffer, and undo logs are also present in the InnoDB system tablespace. Additionally, the tablespace might contain index and table data if tables are created in the system tablespace (instead of file-per-table or general tablespaces).

Run the following query to find the InnoDB system tablespace:

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

Note: This query runs on MySQL version 5.7 and higher, or MySQL 8.0 and higher.

After the size of your system table space is increased, you can't reduce it. However, you can dump all of your InnoDB tables and import the tables into a new MySQL DB instance. To avoid large system tablespaces, consider using file-per-table tablespaces. For more information, see File-per-table tablespaces on the MySQL website.

If you do enable Innodb_file_per_table, then each table will store the data and index in its own tablespace file. You can reclaim the space (from fragmentation on databases and tables) by running OPTIMIZE TABLE on that table. The OPTIMIZE TABLE command creates a new empty copy of your table. Then, data from the old table is copied row by row to the new table. During this process, a new .ibd tablespace is created and space is reclaimed. For more information about this process, see OPTIMIZE TABLE statement on the MySQL website.

Important: The OPTIMIZE TABLE command uses the COPY algorithm to create temporary tables that are the same size as the original table. Confirm that you have enough available disk space before running this command.

To optimize your table, run the following command syntax:

mysql> OPTIMIZE TABLE <tablename>;

Or, you can rebuild the table by running the following command:

mysql> ALTER TABLE <table_name> ENGINE=INNODB;

Binary logs

If you activate automated backups on your Amazon RDS instance, the binary logs are also automatically activated on your DB instance. These binary logs are stored on the disk and consume storage space, but are purged at every binary log retention configuration. The default binlog retention value for your instance is also set to "Null", which means that the file is removed immediately.

To avoid low storage space issues, set the appropriate binary log retention period in Amazon RDS for MySQL. You can review the number of hours that a binary log is retained with the mysql.rds_show_configuration command syntax:

CALL mysql.rds_show_configuration;

You can also reduce this value to retain logs for a shorter period to reduce the amount of space the logs use. A value of NULL means that logs are purged as soon as possible. If there's a standby instance for the active instance, then monitor the ReplicaLag metric on the standby instance. The ReplicaLag metric indicates any delays that occur during the binary log processing on the active instance or relay logs on the standby instance.

If there's a standby instance for the active instance, then monitor the ReplicaLag metric on the standby instance. The ReplicaLag metric indicates any delays during the binary log purge on the active instance and relay log on the standby instance. If there are purging or replication issues, then these binary logs can accumulate over time, consuming additional disk space. To check the number of binary logs on an instance and file size, use the SHOW BINARY LOGS command. For more information, see SHOW BINARY LOGS statement on the MySQL website.

If the DB instance is acting as a replication standby instance, then check the size of the relay logs (Relay_Log_Space) value using the following command:

SHOW SLAVE STATUS\G

MySQL logs (general logs, slow query logs, and error logs)

Amazon RDS for MySQL provides logs (such as general logs, slow query logs and error logs) that can be used to monitor your database. Error logs are active by default. However, the general logs and slow query logs can be activated using a custom parameter group on the RDS instance. After the slow query logs and general logs are activated, they are automatically stored in the slow_log and general_log tables inside the MySQL database. To check the sizes of any slow queries, general logs (of "FILE" type), and error logs, view and list the database log files.

If the slow query log and general log tables are using excessive storage, then manage the table-based MySQL logs by manually rotating the log tables. To completely remove the old data and reclaim your disk space, call the following commands twice in succession:

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

Note: The tables don't provide an accurate file size of the logs. Modify the parameter so that the value of log_output for slow_log and general_log is "File" instead of "Table".

It's also a best practice to monitor your Amazon RDS DB instance using Amazon CloudWatch. You can set up CloudWatch alarms on the FreeStorageSpace metric to receive alerts whenever your storage space drops below a certain threshold value. Finally, monitor the FreeStorageSpace metric by setting up a CloudWatch alarm to receive notifications whenever your DB instance is low on free space. For more information, see How can I create CloudWatch alarms to monitor the Amazon RDS free storage space and prevent storage full issues?

Also, you can use the Amazon RDS storage autoscaling feature to manage capacity automatically. With storage autoscaling, you don't have to manually scale up database storage. For more information about Amazon RDS storage autoscaling, see Working with storage for Amazon RDS DB instances.


Related information

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

AWS OFFICIAL
AWS OFFICIALUpdated 2 years ago