Database Autonomy Service (DAS) provides the automatic recycling feature for tablespace fragments. This feature can help optimize the usage of tablespace. After you enable this feature for your database instance, DAS runs the Optimize Table command on the primary instance to recycle tablespace fragments. This can help you reorganize the physical tablespace fragments.

Prerequisites

  • DAS Professional Edition is activated for the database instance. For more information, see DAS Professional Edition.
  • The database instance is an ApsaraDB RDS for MySQL instance of the High-availability Edition. The date when the instance is created is at least 14 days before the current date.
  • The CPU specification of the database instance is at least four cores.
  • The physical table uses the InnoDB engine.

Items that the system checks before tablespace fragment recycling

To mitigate negative impacts of tablespace fragment recycling on the database performance and tablespace, the system checks the database instance and the tables in the instance:

  • If one or more of the following requirements are not met, tablespace fragment recycling for the corresponding table ends.
    • The free space of the database instance must be higher than or equal to three times the physical space of the table whose tablespace fragments that you want to recycle.
      Note For example, you want to recycle the tablespace fragments of the Table_1 table. The physical space of the table is 30 GB. The free space of the database instance must be higher than or equal to 90 GB when tablespace fragment recycling is triggered.
    • No full-text indexes are created for the table.
  • If one or more of the following conditions are not met, the system waits for approximately 1 to 5 minutes and then checks again. This process is repeated until the maintenance window ends.
    • No Backup tasks are being performed on the database instance.
    • No schema change tasks are being performed.
    • The CPU utilization during the same period of time on the previous day and the CPU utilization during the same period of time on the day that is seven days earlier than the current date must be less than 70%, and the CPU utilization before the recycling task is scheduled to start must be less than 80%.
      Note For example, the physical space of a table is 90 GB and the tablespace fragment recycling can take approximately 30 minutes. The tablespace fragment recycling task is scheduled to run at 03:00 on December 10. You must make sure that the following requirements are met:
      • The CPU utilization must be less than 70% from 03:00 to 03:30 on December 9, 2020.
      • The CPU utilization must be less than 70% from 03:00 to 03:30 on December 3, 2020.
      • The CPU utilization must be less than 80% from 03:00 on December 9, 2020 to the time when the recycling starts.
  • Before the system recycles the tablespace fragments of a table, the system checks the table. If one of the following conditions are not met, the system does not start the recycling task:
    • No SQL statements that require longer than 3 seconds to execute exist.
    • No pending transactions exist. Pending transactions are the transactions that hold locks but are not committed within 15 seconds.
    • The number of active sessions on the table does not exceed 64.

Enable the tablespace fragment recycling feature

  1. Log on to the .
  2. In the left-side navigation pane, click Instance Monitoring. On the page that appears, click the MySQL tab, and then click the ID of the ApsaraDB RDS for MySQL High-availability Edition instance for which you want to enable the tablespace fragment recycling feature.
    Instance Monitoring page
  3. In the left-side navigation pane of the instance details page, click Autonomy Center. In the upper-right corner of the page that appears, click Autonomy Service Settings.
  4. In the Settings dialog box, turn on the Automatic Fragment Reclamation switch on the Autonomy Service Settings tab.
  5. Configure the parameters that are described in the following table and then click OK.
    Parameter Description
    Tablespace The space of tablespace fragments in the physical table. You can set a value from 10 GB to 100 GB. The default value is 10 GB.
    Note A large space of tablespace fragments can require a long period of time to recycle. You must specify a valid value for this parameter based on your business requirements.
    Fragmentation Rate The percentage of the space of tablespace fragments to the total space of the physical table. You can set a value from 20% to 99%. The default value is 20%.
    Note If you set this parameter to a large value, tablespace fragment recycling is performed at a less frequent rate. You must specify a valid value based on your business requirements.