ApsaraDB RDS:Troubleshoot insufficient storage issues on an ApsaraDB RDS for MySQL instance

Log on to the ApsaraDB RDS console and go to the Basic Information page of your RDS instance. In the Usage Statistics section of the page, view the overall storage usage of your RDS instance. The Usage Statistics section does not provide the current or historical storage usage for various data types.

Log on to the ApsaraDB RDS console. In the left-side navigation pane of the details page of your RDS instance, choose Autonomy Service > Diagnostics. On the page that appears, click the Storage Analysis tab to view the details about the storage usage, including the comparison between the storage used by data and logs, storage usage trend, details about the storage used by top N databases, and details about the storage used by top N tables.

Log on to a specified database. Then, execute the SHOW TABLE STATUS LIKE '<Table name>'; statement to view the storage usage of the table.

Symptom

In most cases, a table contains primary key indexes and secondary indexes. More secondary indexes indicate higher storage usage for the table.

Solution

Optimize the data structure of the table to reduce secondary indexes.

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 occupies a large amount of storage.

Solution

Compress data before you insert the data into the table.

Symptom

The fragmentation ratio of an InnoDB table is high. This results in 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 from which the records are deleted, a large number of idle tablespaces are generated.

Solution

Execute the SHOW TABLE STATUS LIKE '<Table name>'; statement to view the idle tablespace. If the idle tablespace is excessively large, execute the OPTIMIZE TABLE <Table name>; statement to optimize the tablespace.

Symptom

• When you perform a sort operation that does not use an index, semi-join operation, or distinct operation 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 perform data definition language (DDL) statements to rebuild tablespaces, the temporary table that is generated from an index-based sort operation is large. This applies if the tablespaces are used to store the data of a large table. If your RDS instance runs MySQL 5.6 or MySQL 5.7, you cannot immediately add fields. Some DDL statements can be executed only on new tables. If you send requests to execute these DDL statements on a table, the system creates a table and executes these DDL statements on the new table. The new table is a replica of the original table. Therefore, you can find two file replicas when these DDL statements are being executed. After these DDL statements are executed, the original table is deleted.

Solution

• View the plans based on which the DDL statements are executed. This allows you to check whether the Using Temporary field is specified.

• Before you execute DDL statements on large tables, check whether your RDS instance provides sufficient storage. If the available storage is insufficient, expand the storage capacity of your RDS instance. For more information, see Change the specifications of an ApsaraDB RDS for MySQL instance.

Enable the automatic tablespace fragment recycling feature. For more information, see Automatic tablespace fragment recycling. After the feature is enabled, the primary RDS instance automatically executes the OPTIMIZE TABLE statement to reclaim tablespace fragments. This helps you clean up physical tablespace fragments.

Use cloud disks. For more information, see Storage types. Cloud disks provide a larger storage capacity than local disks.

Use the X-Engine storage engine. For more information, see X-Engine overview. X-Engine supports a high compression ratio.

Use PolarDB. For more information, see PolarDB overview. PolarDB is developed based on a distributed storage system. It provides a large storage capacity that can be automatically expanded. The combination of PolarDB Archive Database Edition and X-Engine significantly reduces the storage usage for various data types.

If your RDS instance runs RDS High-availability Edition and uses cloud disks, enable the automatic storage expansion feature. This feature prevents your RDS instance from being locked due to insufficient storage. For more information, see Configure automatic storage expansion.

Use AnalyticDB for MySQL. For more information, see What is AnalyticDB for MySQL?