Database Autonomy Service (DAS) provides the automatic space debris recycling feature. This feature allows DAS to automatically optimize tablespaces. After you enable this feature, the primary instance automatically runs the Optimize Table command to recycle tablespace debris and helps you defragment physical tablespaces.

Prerequisites

  • DAS Professional Edition is activated for the instance. For more information, see DAS Professional Edition.
  • The instance edition is ApsaraDB RDS for MySQL High-availability Edition. The instance was created 14 days ago or earlier.
  • The CPU has at least four cores.
  • The physical table uses the InnoDB engine.

Check items before optimization

To mitigate the negative impacts of automatic space debris recycling on the database performance and tablespace, the system checks the following items:

  • If one or more of the following items fail to pass the check, automatic space debris recycling for the corresponding table ends.
    • The remaining free space is at least three times the physical space of the table to be recycled.
      Note For example, if you want to recycle Table_1 and the physical space of the table is 30 GB, a remaining free space of 90 GB is required before automatic space debris recycling is enabled.
    • No full-text indexes are defined for the table.
  • If one or more of the following items fail to pass the check, the system checks again after 1 to 5 minutes. This mechanism applies until the maintenance window ends.
    • No backup tasks are associated with the instance that you want to manage.
    • Schema change operations are not being performed.
    • View the CPU utilization of the instance and estimate the time that is required for automatic space debris recycling. Ensure that the CPU utilization during the same period of time on the previous day and that during the same period of time in the last week are less than 70%. Ensure that the CPU utilization before the recycling is less than 80%.
      Note For example, assume that the physical space of the table is 90 GB and the space debris recycling is estimated to take 30 minutes. To enable automatic space debris recycling at 03:00 on December 10, ensure that the following conditions are met:
      • The CPU utilization from 03:00 to 03:30 on December 9, 2020 is less than 70%.
      • The CPU utilization from 03:00 to 03:30 on December 3, 2020 is less than 70%.
      • The CPU utilization from 03:00 on December 9, 2020 to the time when the recycling is performed is less than 80%.
  • Before automatic space debris recycling, the following items are automatically checked:
    • No SQL statements that require longer than 3 seconds to execute have been executed on the table for which you want to recycle the tablespace debris.
    • Pending transactions do not exist. Pending transactions indicates the transactions that hold locks but are not committed within 15 seconds.
    • The number of active sessions is fewer than 64.

Procedure

  1. Log on to the DAS console.
  2. In the left-side navigation pane, click Instance Monitoring. Click the MySQL tab and click the high-availability instance that you want to manage to go to the instance details page.
    Instance monitoring
  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 Autonomous function switch.
  4. In the Set dialog box, turn on the Automatic Space Debris Recycling switch on the Autonomous function switch tab.
    s
  5. After Automatic Space Debris Recycling is enabled, configure the following parameters and click Determine.
    Parameter Description
    Tablespace The physical space of the table. Both the default and minimum values are 10 GB. The maximum value is 100 GB.
    Note A large tablespace requires a long time for recycling. Select an appropriate value based on your actual business requirements.
    Fragmentation rate The fragmentation rate of the physical table. Both the default and minimum values are 20%. The maximum value is 99%.
    Note A high fragmentation rate reduces the frequency of space optimization. Select an appropriate value based on your actual business requirements.