ApsaraDB RDS:Use the automatic fragment reclamation feature

Items that the system checks before it recycles tablespace fragments

DAS executes the OPTIMIZE TABLE or ALTER TABLE statement on the primary instance to recycle tablespace fragments. To mitigate the negative impacts on database performance and tablespace, DAS automatically checks the database instance and the tables in the database instance before automatic tablespace fragment recycling is performed. DAS performs automatic tablespace fragment recycling only after the check is passed.

• If one or more of the following requirements are not met, automatic tablespace fragment recycling cannot be performed on a table.

  • The available storage space of the database instance must be at least three times 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 Table_1 whose physical space is 30 GB, the available storage space of the database instance must be greater than or equal to 90 GB.

  • No full-text indexes are created for the table.

• If one or more of the following requirements are not met, DAS waits for 1 to 5 minutes and then performs the check again. If the check fails throughout the entire maintenance window, DAS checks the table during the next maintenance window until the check is passed. After the check is passed, DAS performs automatic tablespace fragment recycling.

  • 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 same day of last week is less than 70%, and the CPU utilization before the recycling task is scheduled to start is less than 80%.

    Note

    For example, tablespace fragment recycling is scheduled to start at 03:00 on December 10, 2020 for a table whose physical space is 90 GB, and the operation is expected to take 30 minutes to complete. In this case, make sure that the following requirements are met:

    • The CPU utilization is less than 70% from 03:00 to 03:30 on December 9, 2020.

    • The CPU utilization is less than 70% from 03:00 to 03:30 on December 3, 2020.

    • The CPU utilization is less than 80% at 03:00 on December 10, 2020.

  • No SQL statements take longer than 3 seconds to execute.

  • No pending transactions exist. Pending transactions are the transactions that hold locks but are not committed within 15 seconds.

  • The number of active sessions in the database instance is less than 64.

Procedure

1. Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.

2. Go to the Autonomous Function Management panel.

   • In the left-side navigation pane, choose Autonomy Services > Diagnostics. On the page that appears, click the Autonomy Center tab. On the Autonomy Center tab, click Autonomy Service Settings.

   • In the left-side navigation pane, choose Autonomy Services > Dashboard. On the page that appears, click Autonomy Service Settings.

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

4. Configure the following parameters and click OK.

   Important

   When the storage usage of a table in the database instance reaches the specified thresholds, DAS automatically reclaims the tablespace fragments during the specified maintenance window for the database instance.

   Parameter	Description
   Tablespace >	The threshold of storage usage for a physical table. Unit: GB. Valid values: 5 to 100. Default value: 10. If you set this parameter to a large value, the reclamation process may require a long period of time to complete. We recommend that you specify a value based on your business requirements. Note If the storage usage of all tables on the RDS instance is less than 5 GB or greater than 100 GB, DAS does not reclaim the tablespace fragments.
   Fragmentation Percentage >	The percentage of the storage usage of tablespace fragments to the total storage usage of a physical table. Valid values: 10 to 99. Default value: 20. If you set this parameter to a large value, automatic fragment reclamation is performed less frequently. We recommend that you specify a value based on your business requirements. Note If the fragmentation rate of all tables on the RDS instance is less than 10%, DAS does not reclaim the tablespace fragments.

   Note

   For example, if the value of the Tablespace > parameter is 5 GB and the value of the Fragmentation Rate > parameter is 10%, automatic fragment reclamation 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%.

5. Optional. In the Alert Configuration section, configure an alert template and subscribe to alert notifications. This helps you understand the status of an automatic fragment reclamation task at the earliest opportunity.

   The system recommends an alert template and adds alert rules for the required autonomy events in the alert template. You can configure the alert template as prompted.

   Note

   • If you have configured an alert template for your RDS instance, you must add alert rules for the required autonomy events to the alert template as prompted.

   • If you have not configured an alert template for your RDS instance but you want to configure one, you can configure the alert template by following the instructions provided in Configure alert templates and Configure alert rules.

   1. In the Select Alert Contact Group step, select an alert contact group.

      • Click Add Contact to add an alert contact.

      • Click Create Contact Group to create an alert contact group.

      • Find the alert contact that you want to manage and click Edit or Remove in the Actions column to modify or delete information about the alert contact.

      For more information, see Manage alert contacts.

   2. In the Associate with Resources step, confirm the associated resources.

   3. Click Submit Configuration. In the dialog box that appears, confirm the configuration.