Automatic fragment reclamation

Limitations

• Automatic fragment reclamation is supported only for tables that use the InnoDB storage engine.

  Note

  You can run the SHOW TABLE STATUS or SHOW TABLE STATUS LIKE 'table_name'; statement to check the storage engine of a table. The value in the Engine column is the storage engine of the table.

• Automatic fragment reclamation is supported only for tables with a tablespace between 5 GB and 100 GB.

  Note

  If a tablespace is smaller than 5 GB or larger than 100 GB, DAS does not perform automatic fragment reclamation for the table.

Pre-optimization checks

DAS runs the OPTIMIZE TABLE or ALTER TABLE command on the primary instance to reclaim space from fragmented tablespaces. To minimize the impact on database performance and space, DAS automatically performs the following checks before it starts an Automatic Fragment Recycling operation. The Automatic Fragment Recycling operation runs only after all checks are passed.

• If any of the following checks fails, DAS immediately stops the Automatic Fragment Recycling operation for that table.

  • The available space on the instance is at least three times the physical space occupied by the target table.

    Note

    For example, if the target table occupies 30 GB of physical space, the instance requires at least 90 GB of available space.

  • The table does not have a FULLTEXT index.

• If any of the following checks fails, DAS waits 1 to 5 minutes and then retries the checks. If the checks do not pass within the current maintenance window, DAS retries during the next maintenance window. The Automatic Fragment Recycling operation starts only after all checks are passed.

  • No backup tasks are running on the instance.

  • No schema change operations are in progress.

  • The instance CPU utilization is below specific thresholds. DAS estimates the time required for Automatic Fragment Recycling and verifies that the CPU utilization during the same time period on the previous day and in the previous week was below 70%. It also verifies that the CPU utilization immediately before the operation is below 80%.

    Note

    For example, a table that occupies 90 GB of physical space is scheduled for reclamation at 03:00 on December 10, and the operation is expected to take 30 minutes. DAS verifies the following:

    • The CPU utilization from 03:00 to 03:30 on December 9 was below 70%.

    • The CPU utilization from 03:00 to 03:30 on December 3 was below 70%.

    • The CPU utilization at 03:00 on December 10 is below 80%.

  • No queries that have run for more than 3 seconds are executing on the target table.

  • No pending transaction (a transaction that has held a lock for more than 15 seconds without being committed) exists.

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

Enable automatic fragment reclamation

1. Log on to the DAS console.

2. In the navigation pane on the left, click Intelligent O&M Center > Instance Monitoring .

3. Find the target instance and click the instance ID to open the instance details page.

4. In the navigation pane on the left, click Autonomy Center.

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

6. On the Autonomous Function Management > Autonomous Function Settings tab, turn on the switch for the autonomy service.

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

   Important

   When the storage status of the database instance reaches the specified thresholds, DAS performs automatic fragment reclamation during the maintenance window of the instance.

   Parameter	Description
   Tablespace	The minimum tablespace size of a single physical table that triggers automatic fragment reclamation. Valid values: 5 GB to 100 GB. Default value: 10 GB. A large tablespace requires a longer reclamation time. Select a value based on your business requirements. Note If all tablespaces on the database instance are smaller than 5 GB or larger than 100 GB, the system does not perform fragment reclamation.
   Fragmentation Rate	The fragmentation rate of a single physical table that triggers automatic fragment reclamation. Valid values: 10% to 99%. Default value: 20%. A high fragmentation rate threshold reduces the optimization frequency. Select a value based on your business requirements. Note If the fragmentation rate of all tables on the database instance is less than 10%, the system does not perform fragment reclamation.

   Note

   For example, if you set Tablespace to 5 GB and Fragmentation Rate to 10%, automatic fragment reclamation is triggered for all tables that have a tablespace size of 5 GB to 100 GB and a fragmentation rate of 10% or higher.

8. Click OK.

9. Configure an Alert Template to receive status updates for automatic fragment reclamation on your database instance.

   The system recommends an alert template and adds alert rules for the corresponding autonomy events. You can follow the on-screen instructions to complete the configuration.

   Note

   • If an alert template is already configured for the instance, follow the on-screen instructions to add the alert rules for the autonomy events to the template.

   • If you want to configure the alert template and alert rules on your own, see Configure an alert template and Configure alert rules.

10. Select an Alert Contact Group to receive alert notifications.

    • Click Add Contact to add a new alert contact.

    • Click Create Contact Group to add a new alert contact group.

    • Click Modify or Remove next to a contact to modify or delete the contact information.

    For more information, see Manage alert contacts.

11. Click Submit Configuration and confirm the alert configuration in the dialog box that appears.

Related documents

If your database instance is running low on storage space:

• Use Storage Analysis to view the storage usage of the database instance and identify storage issues.

• Enable Automatic Space Expansion to automatically expand storage space when the instance is low on storage. This ensures the stability of your services.

API reference