Database Autonomy Service:Automatic fragment reclamation

Prerequisites

Before you begin, make sure that:

• Alibaba Cloud Managed Services is enabled for the instance

• The instance is one of the following types:

  • RDS for MySQL High-availability Edition, RDS Enterprise Edition, or Cluster Edition

  • MyBase for MySQL High-availability Edition

• The instance has been running for at least 14 days

• The instance has at least 4 CPU cores

Limits

• Automatic fragment reclamation supports only tables that use the InnoDB storage engine. To check a table's storage engine, run SHOW TABLE STATUS or SHOW TABLE STATUS LIKE 'table_name' and check the Engine column.

• Automatic fragment reclamation supports only tables with a tablespace size between 5 GB and 100 GB. Tables outside this range are skipped.

Background information

Tablespace fragments are typically created for the following reasons:

• Records are deleted, and the original space cannot be reused.

• Records are updated, and the original space cannot be reused. This usually occurs in variable-length fields.

• Record insertions cause page splits, which reduces the fill factor of pages.

How it works

Before reclaiming fragments on any table, DAS runs two sets of checks.

Immediate-stop checks — if either fails, reclamation stops for that table:

Retry checks — if any fails, DAS waits 1–5 minutes and reruns the checks. If the checks do not pass before the Maintenance Window ends, DAS retries during the next window:

If the retry checks do not pass within the Maintenance Window, DAS skips the table and retries during the next window. To reclaim fragments immediately without waiting, run OPTIMIZE TABLE manually. For details, see Use the OPTIMIZE TABLE command to release the tablespace of a MySQL instance.

Enable automatic fragment reclamation

1. Log on to the DAS console.

2. In the left-side navigation pane, choose Intelligent O&M Center > Instance Monitoring.

3. Find the instance and click its ID.

4. In the left-side navigation pane of the instance details page, click Autonomy Center.

5. Click Autonomy Service Settings in the upper-right corner.

6. On the Autonomous Function Management > Autonomous Function Settings tab, turn on the autonomous feature switch.

7. On the Optimization and Throttling tab, select Automatic Fragment Recycling and configure the parameters.

   Note

   DAS triggers reclamation during the instance's Maintenance Window when the configured thresholds are met.

   Parameter	Description
   Tablespace	The minimum tablespace size of a single table that triggers reclamation. Valid range: 5 GB–100 GB. Default: 10 GB. Tables larger than 100 GB are always skipped. For example, setting this to 10 GB with Fragmentation Rate at 20% triggers reclamation for all tables between 10 GB and 100 GB that have a fragmentation rate of at least 20%.
   Fragmentation Rate	The minimum fragmentation rate of a single table that triggers reclamation. Valid range: 10%–99%. Default: 20%. Tables with a rate below 10% are always skipped.

8. Click OK.

9. (Optional) Click Event Subscription Settings to configure event notifications for reclamation events. For details, see Enable the event subscription feature.

10. Configure an Alert Template to subscribe to alerts about reclamation status. The system recommends a template and adds alert rules for the corresponding autonomy events. Follow the prompts to complete the configuration.

    Note

    If an alert template is already configured for the instance, add alert rules for the autonomy events to the existing template as prompted. To configure a template and rules manually, see Configure an alert template and Configure an alert rule.

11. Select an Alert Contact Group to receive notifications. For details, see Manage alert contacts.

    • Click Add Contact to create an alert contact.

    • Click Create Contact Group to create a contact group.

    • Click Edit or Remove next to a contact to modify or delete it.

12. Click Submit Configuration and confirm the alert configuration.

API reference

What's next

If the instance is running low on storage space:

• Use Storage Analysis to identify which tables and indexes are consuming the most space.

• Enable Automatic Space Expansion to automatically expand storage before it runs out.