Deleting and updating data in database tables can cause data to be stored non-contiguously on the disk. This creates space fragmentation, which wastes disk space and degrades performance. Database Autonomy Service (DAS) supports automatic fragment reclamation. During an instance's maintenance window, DAS automatically runs the `Optimize Table` or `Alter Table` command on the primary instance to reclaim tablespace fragments and optimize space usage.
Prerequisites
The instance is an ApsaraDB RDS for MySQL instance that runs one of the following editions: High-availability Edition, Enterprise Edition, or Cluster Edition.
The instance was created 14 or more days ago.
The instance has a CPU specification of 4 cores or more.
The physical tables of the instance use the InnoDB engine.
Background information
The following are common causes of tablespace fragmentation:
Records are deleted, and the original space cannot be reused.
Records are updated, usually in variable-length fields, and the original space cannot be reused.
Record insertions cause page splits, which reduces the page fill factor.
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.
NoteFor 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%.
NoteFor 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.
If a table does not pass the check, the automatic recycling of tablespace fragments for the table cannot be triggered. In this case, you can manually execute the OPTIMIZE TABLE statement to recycle the tablespace fragments. For more information, see Use the OPTIMIZE TABLE command to release tablespace in MySQL instances
Enable Alibaba Cloud Managed Services for DAS
Before you can use the Automatic Fragment Reclamation feature, you must enable Alibaba Cloud Managed Services for DAS for the target instance. If this service is already enabled, you can skip this section.
Log on to the DAS console.
In the navigation pane on the left, click .
On the Instances page, find the target instance and click Enable in the Managed Service column.
On the page that appears, select a Subscription Duration and click Enable.
Automatic Fragment Reclamation
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.
In the navigation pane on the left, choose . On the Autonomy Center page, click Autonomy Service Switch.
On the tab, enable the main switch for autonomy services. Then, on the Optimization and Throttling tab, enable Automatic Fragment Reclamation.
After you enable Automatic Fragment Reclamation, configure the following parameters and click OK.
ImportantWhen the space status of the database instance reaches the specified thresholds, DAS performs automatic fragment reclamation during the O&M Window of the database instance.
Parameter
Description
Tablespace >
The minimum size of a single physical table to trigger automatic fragment reclamation. The value can range from 5 GB to 100 GB. The default value is 10 GB.
A large single table can result in a long reclamation time. Select an appropriate value as needed.
NoteIf the tablespace of all tables in the database instance is smaller than 5 GB or larger than 100 GB, the system does not perform fragment reclamation.
Fragmentation Percentage >
The fragmentation rate of a single physical table to trigger automatic fragment reclamation. The value can range from 10% to 99%. The default value is 20%.
A high fragmentation rate reduces the frequency of space optimization. Select an appropriate value as needed.
NoteIf the fragmentation rate of all tables in the database instance is lower than 10%, the system does not perform fragment reclamation.
NoteFor example, if you set Tablespace > to 5 GB and Tablespace > to 10%, automatic fragment reclamation is triggered for all tables that are 5 GB to 100 GB in size and have a fragmentation rate of 10% or higher.
(Optional) Configure an Alert Template and subscribe to alerts to receive timely notifications about the Automatic Fragment Reclamation status of the database instance.
The system recommends an alert template and adds alert rules for the corresponding autonomy events. Follow the prompts to complete the configuration.
NoteIf an alert template is already configured for the instance, follow the prompts to add the corresponding alert rules for autonomy events to the template.
To configure the alert template and alert rules yourself, see Configure an alert template and Configure an alert rule.
Select an Alert Contact Group to receive alert notifications.
Click Add Contact to add a new alert contact.
Click Add Contact Group to add a new alert contact group.
Click Modify or Remove next to a contact to modify or delete the contact's information.
For more information, see Manage alert contacts.
Confirm the Linked Instance.
Click Submit Configuration and confirm the alert configuration in the dialog box that appears.