RDS for MySQL Performance Tuning – Resolving Insufficient Storage Issues

Storage usage is an important metric for measuring the performance of your DB instances. If the available storage space is insufficient, your DB instance may encounter serious issues. For example, data cannot be backed up or written into databases, or scaling up storage takes an extended period of time.

Viewing Storage Space Usage

On the Basic Information page, you can see how much of your instance storage and backup space has been used. However, this page does not provide any details about what different types of data are being stored.
To view the historical usage and the changes over time, click View Metrics on the Basic Information page.

Insufficient Storage Caused by Excessive Indexes

Cause and symptom
In most cases, a table contains primary key indexes and secondary indexes. More secondary indexes mean that the table takes up more space.
Solution
Optimize the data structure of the table to reduce the number of secondary indexes.

Insufficient Storage Caused by Large Fields

Cause and symptom
If large fields of the binary large object (BLOB), TEXT, or VARCHAR data type are defined in the schema of a table, the table takes up a lot of space.
Solution
Compress data before you insert the data into the table.

Insufficient Storage Caused by Excessive Idle Tablespaces

Cause and symptom
If the fragmentation ratio of an InnoDB table is high, there will be an excessive number of idle tablespaces. InnoDB manages tablespaces by page. If some records of a full page are deleted and no new records are inserted into the positions these records were deleted from, a large number of tablespaces will be idle.
Solution
Run the show table status like '<Name of the table>'; command to query idle tablespaces. If there are too many tablespaces, run the optimize table '<Name of the table>'; command to manage the tablespaces.

Insufficient Storage Caused by Excessively Large Temporary Tables

Cause and symptom
When you perform a semi-join, distinct, or sort operation without using an index on a table, a temporary table is created. If the temporary table contains an excessive amount of data, the storage usage for the temporary table may be excessively high.

When you execute data definition language (DDL) statements to rebuild tablespaces that are used to store the data of a large table, the temporary table that is generated from an index-based sort operation will also be large. If your DB instance runs MySQL 5.6 or MySQL 5.7, you cannot immediately add fields, and some DDL statements can be executed only on new tables. If you send requests to execute these DDL statements on a table, RDS creates a new table and executes these DDL statements on the new table. The new table is a replica of the original table. In this situation, you end up with two copies of the files when these DDL statements are being executed. After these DDL statements are executed, the original table is deleted.
Solution
View the plans that the DDL statements were based on. Check whether the Using Temporary field was specified.

Before you execute DDL statements on large tables, check whether your DB instance provides sufficient storage space. If the available storage space is insufficient, scale up the storage space of your DB instance before executing the statements.