Database Autonomy Service:Automatic tablespace fragment recycling

Limits

• Automatic tablespace fragment recycling is supported only for tables whose storage engine is InnoDB.

  Note

  To view the storage engine of the table that you want to manage, execute the SHOW TABLE STATUS or SHOW TABLE STATUS LIKE 'table_name'; statement. The Engine parameter in the output indicates the storage engine of the table.

• Automatic tablespace fragment recycling is supported only for tables whose tablespace size is in the range of 5 GB to 100 GB.

  Note

  If the tablespace size is smaller than 5 GB or larger than 100 GB, automatic tablespace fragment recycling is not triggered.

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

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

• If 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 average CPU utilization during the same period of time on the previous day and the average CPU utilization during the same period of time on the same day of last week 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, 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 average CPU utilization must be less than 70% from 03:00 to 03:30 on December 9, 2020.

    • The average 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% at 03:00 on December 9, 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. Log on to the DAS console.

2. In the left-side navigation pane, click Instance Monitoring.

3. On the page that appears, find the database instance that you want to manage and click the instance ID. The instance details page appears.

4. On the instance details page, click Autonomy Center in the left-side navigation pane.

5. On the Autonomy Center tab, click Autonomy Service Settings in the upper-right corner.

6. On the Autonomous Function Settings tab of the Autonomous Function Management panel, enable the autonomy service.

7. On the Optimization and Throttling tab, select Automatic Fragment Recycling and configure the parameters that are described in the following table.

   Important

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

   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 tablespace fragment recycling may not be triggered until a large number of fragments are accumulated. As a result, the recycling process may take a long time. Specify an appropriate value based on your business requirements. Note If the storage usage of all tables in the database instance is less than 5 GB or greater than 100 GB, DAS cannot recycle the tablespace fragments.
   Fragmentation Percentage	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 an appropriate value based on your business requirements. Note If the fragmentation percentage of all tables in the database instance is less than 10%, DAS cannot recycle the tablespace fragments.

   Note

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

References

When the storage space of a database instance is insufficient, you can perform the following operations:

• Check the storage usage of the database instance to identify storage usage issues. For more information, see Storage analysis.

• Enable automatic space expansion for the database instance. This way, when the storage space of the database instance is insufficient, DAS can automatically expand the storage space to ensure the stability of your business. For more information, see Automatic space expansion.

Related API operations