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

View the storage usage

  • Log on to the ApsaraDB RDS console and go to the Basic Information page. 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.Basic Information page
  • Log on to the ApsaraDB RDS console and go to the Monitoring and Alerts page. On the Standard Monitoring tab of the page, click Resource Monitoring. Then, view the current and historical storage usage for various data types.Disk Space (MB) metric
  • Log on to the ApsaraDB RDS console. In the left-side navigation pane, choose Autonomy Service > Diagnostics. On the page that appears, click the Storage Analysis tab. Then, view further details about the storage usage of your RDS instance. These details include the storage usage comparison between data and logs, the storage usage trend, the storage usage for top databases, and the storage usage for top tablespaces.Storage Analysis tab - 1Storage Analysis tab - 2
    Note
    • In the Tablespaces section, you can view the data storage usage, index storage usage, and available storage space of a specific tablespace. The available storage space is the amount of unused storage space that is allocated to the tablespace.
    • The storage usage is obtained from the statistics of INFORMATION_SCHEMA tables and may be inaccurate.
  • Log on to a specified database. Then, run the show table status like '<The name of a table>'; command to view the storage usage for the specified table.

Troubleshoot out-of-space conditions 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 out-of-space conditions 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 space.

  • Solution

    Compress data before you insert the data into the table.

Troubleshoot out-of-space conditions 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 these records are deleted, a large number of tablespaces are idle.

  • Solution

    Run the show table status like '<The name of the table>'; command to query the idle tablespaces that store the data of the table. If an excessively large number of tablespaces are idle, run the optimize table <The name of the table>; command to manage the tablespaces.

Troubleshoot out-of-space conditions caused by excessively large temporary tables

  • Symptom
    • When you perform a semi-join, distinct, or sort operation on a table, a temporary table is created. The sort operation does not use an index. 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, ApsaraDB creates a new 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 space. If the available storage space is insufficient, scale 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 space debris recycling feature. This feature allows your primary RDS instance to automatically execute OPTIMIZE TABLE statements. These statements are used to recycle fragments from tablespaces. For more information, see Automatic space fragments recycling.
  • Use standard or enhanced SSDs. These types of SSDs provide a larger storage capacity than local SSDs. For more information, see Storage types.
  • Use the X-Engine storage engine. X-Engine supports a high compression ratio. For more information, see X-Engine overview.
  • Use PolarDB. PolarDB is based on a distributed storage system. It provides a large storage capacity that can be automatically scaled. The combination of PolarDB Archive Database and X-Engine significantly reduces the storage usage for various data types. For more information, see PolarDB overview.
  • If your RDS instance is equipped with standard or enhanced SSDs, enable the automatic storage expansion feature. This feature prevents your RDS instance from being locked due to insufficient storage space. For more information, see Configure automatic storage expansion for an ApsaraDB RDS for MySQL instance.
  • Use AnalyticDB for MySQL. For more information, see What is AnalyticDB for MySQL?