Automatic tablespace fragment recycling

Items that the system checks before it recycles tablespace fragments

To mitigate the negative impacts of tablespace fragment recycling on 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 cannot be performed on the corresponding table.
  • The available storage space of the database instance must be at least three times larger than the physical space of the table whose tablespace fragments you want to recycle.
    Note For example, if you want to recycle the tablespace fragments of the Table_1 table whose physical space is 30 GB, the available storage space of the database instance must be greater 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 requirements are not met, the system waits for approximately 1 to 5 minutes and then performs the 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, assume that tablespace fragment recycling is scheduled to start at 03:00 on December 10 for a table whose physical space is 90 GB, and the operation is expected to take 30 minutes to complete. 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 or more of the following requirements are not met, the system does not start the recycling task:
  • No SQL statements that take 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 is less than 64.

Procedure

1. Log on to the DAS console.
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.
   
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. On the Autonomous Function Settings tab of the Autonomous Function Management panel, enable the autonomy service. On the Optimization and Throttling tab, select Automatic Fragment Recycling.
5. Configure the parameters that are described in the following table and then click OK. Automatic fragment recycling is triggered when conditions specified in both of the following parameters are met.

   Parameter	Description
   Tablespace	The storage usage threshold for a single physical table. Unit: GB. Valid values: 5 to 100. Default value: 10. If you set this parameter to a large value, automatic fragment recycling may not be triggered until a large amount of fragments is accumulated. As a result, the recycling process may take a long time. Specify a valid value based on your business requirements. Note If the storage usage of all tables on the database instance is less than 5 GB or greater than 100 GB, the system cannot recycle the tablespace fragments.
   Fragmentation Rate	The percentage of the storage usage of tablespace fragments to the total storage usage of a physical table. Unit: %. Valid values: 10 to 99. Default value: 20. If you set this parameter to a large value, tablespace fragment recycling is performed less frequently. Specify a valid value based on your business requirements. Note If the fragmentation rate of all tables on the database instance is less than 10%, the system cannot recycle the tablespace fragments.

   Note For example, if the Tablespace value is 5 GB and the Fragmentation Rate value is 10%, tablespace fragment recycling is triggered for all tables whose tablespace ranges from 5 GB to 100 GB and whose fragmentation rate is greater than or equal to 10%.

Best practices