All Products
Document Center

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

Last Updated:Dec 22, 2023

Storage usage is an important metric that is used to measure the performance of an ApsaraDB RDS for MySQL instance. If the available storage is insufficient, the RDS instance may encounter serious issues. For example, data writes or backups fail, and the time that is required for a storage capacity expansion task is abnormally long. This topic describes how to view the storage usage of an RDS instance and how to troubleshoot storage issues.

View storage usage

  • 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.基本信息

  • You can view the storage usage for various data types in the ApsaraDB RDS console. For more information, see View monitoring information.

  • 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.空间分析1空间分析2

    • In the Tablespaces section, you can view the data storage usage, index storage usage, and available storage. The available storage is the amount of unused storage that is allocated to the table.

    • The storage usage is obtained from the collected statistics and may be inaccurate.

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

Troubleshoot insufficient storage caused by excessive indexes

  • 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.

Troubleshoot insufficient storage caused by large fields

  • 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.

Troubleshoot insufficient storage caused by excessive idle tablespaces

  • 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.

Troubleshoot insufficient storage caused by excessively large temporary tables

  • 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.

Optimize the storage usage

  • 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?