When you update or delete table data in a database of an ApsaraDB RDS for MySQL instance, the storage of the table data on the disk becomes discontinuous. As a result, tablespace fragments are generated, which causes disk space wastes and performance downgrades. Database Autonomy Service (DAS) supports the automatic fragment reclamation feature. Within the maintenance window of an RDS instance, the system automatically executes the OPTIMIZE TABLE or ALTER TABLE statement on the primary RDS instance to reclaim tablespace fragments.
Prerequisites
Your RDS instance runs RDS High-availability Edition, RDS Enterprise Edition, or RDS Cluster Edition.
Your RDS instance is created for 14 days or more.
Your RDS instance has at least four CPU cores.
The physical tables on your RDS instance use the InnoDB engine.
DAS Professional Edition is enabled. For more information, see Purchase DAS Professional Edition.
Background information
Tablespace fragments are generated due to the following reasons:
Records are deleted, and the original tablespace cannot be reused.
Records are updated, and the original tablespace cannot be reused. In most cases, records are updated for variable-length fields.
Inserted records cause page splits and decrease the fill rate of pages.
Items that the system checks before it recycles tablespace fragments
To mitigate the negative impacts of tablespace fragment recycling on database performance and tablespace, the system checks the database instance and the tables in the database instance.
If one or more of the following requirements are not met, tablespace fragment recycling cannot be performed on a table.
The available storage space of the database instance must be at least three times larger than the physical space of the table whose tablespace fragments you want to recycle.
NoteFor 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 when tablespace fragment recycling is triggered.
No full-text indexes are created for the table.
If one or more of the following requirements are not met, the system waits for approximately 1 to 5 minutes and then performs the check again. This process is repeated until the maintenance window ends.
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 CPU utilization during the same period of time on the day that is seven days earlier than the current date must be less than 70%, and the CPU utilization before the recycling task is scheduled to start must be less than 80%.
NoteFor example, tablespace fragment recycling is scheduled to start at 03:00 on December 10 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 must be less than 70% from 03:00 to 03:30 on December 9, 2020.
The 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% from 03:00 on December 9, 2020 to the time when the recycling starts.
Before the system recycles the tablespace fragments of a table, the system checks the table. If one or more of the following requirements are not met, the system does not start the recycling task:
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 on the table is less than 64.
If a table does not pass the check, 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 How do I use the OPTIMIZE TABLE statement to release the tablespace of an ApsaraDB RDS for MySQL instance?
Enable the feature
- 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.
Use one of the following methods to 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 Performance Trends tab of the page that appears, click Autonomy Service Settings.
On the Autonomous Function Settings tab of the panel, enable the autonomy service. On the Optimization and Throttling tab, select Automatic Fragment Recycling.
Configure the following parameters. Then, click OK.
ImportantWhen 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.
NoteIf 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.
NoteIf the fragmentation rate of all tables on the RDS instance is less than 10%, DAS does not reclaim the tablespace fragments.
NoteFor 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%.