Data Management:Clear historical data

Prerequisites

• A MySQL database is used.

• The database instance is managed in Stable Change or Security Collaboration mode in DMS. For more information, see Control modes.

Procedure

1. Log on to the DMS console V5.0.

2. In the top navigation bar, choose Database Development > Data Change > Historical Data Cleanup.

   Note

   If you use the DMS console in simple mode, move the pointer over the icon in the upper-left corner of the console and choose All Features > Database Development > Data Change > Historical Data Cleanup.

3. On the Data Change Tickets page, configure the parameters and click Submit.

   The following table describes some of the parameters that are displayed.

   Parameter	Description
   Database	The database on which you have the change permissions. If you have only the permissions to query databases or change data in tables, you cannot submit data change tickets. For more information, see View owned permissions.
   Deletion Settings	The settings related to data deletion. Configure the Table Name, Time Field, Time Accuracy, Retention Period (Days), and Filter Condition (Nullable) parameters. DMS automatically generates an SQL script to clear historical data based on the preceding settings. Note To specify a logical table, you must enter its logical name rather than its physical name. If you specify the retention period, the system clears the data that is retained longer than the retention period. For example, if you specify seven days as the retention period, the system clears the data that is retained for more than seven days from the data table. For example, if the table name is api_call_record_11, the time field is gmt_create, the retention period is seven days, and the filter condition is status = 1 or status=2, DMS generates the following SQL statement: DELETE FROM 'api_call_record_11' WHERE 'gmt_create' < SUBDATE(CURDATE(),INTERVAL 7 DAY) AND (status = 1 or status=2);
   Schedule	The time at which DMS begins to run the task. In DMS, if you use a scheduled task to clear data from a table, the task scans the full table and deletes data in different batches based on the primary key or non-null unique key of the table. We recommend that you set a point in time during off-peak hours to run the task and specify a reasonable interval at which the scheduled task is run. Note The minimum interval is 1 hour. By default, the task is run at 02:00 every day.
   Policy Configuration	The policy for running the task. You can specify an end time for the task, so that the task is automatically suspended after this end time. You can use this policy to prevent the data change from affecting your business during peak hours. Execute Task Without End Time Specify End Time(Hours): Specify an execution duration to ensure that DMS is not affected by consumption limits of downstream synchronization services, such as Data Transmission Service (DTS) or AnalyticDB for MySQL. After you choose whether to specify an end time, you can also choose whether to turn on Periodically Optimize Table for defragmentation. By default, this feature is disabled. You can enable the feature and specify an interval. The default interval is 60. For example, if the interval is set to 60, the system performs defragmentation once after 60 data cleanups. Note The Periodically Optimize Table feature can be enabled only for ApsaraDB RDS for MySQL or PolarDB for MySQL databases. The execution duration of defragmentation depends on the execution duration specified for the Policy Configuration parameter. Defragmentation is stopped when a cleanup ends.
   Change Stakeholder	The stakeholders involved in the change operation. All the specified stakeholders can view the ticket details and participate in the approval process. Irrelevant users, except for DMS administrators and database administrators (DBAs), have no access to the ticket details.

4. After you submit the ticket, you can enable the master-slave delay check, specify a threshold, and modify the SQL statements.

   • Optional. Enable the master-slave delay check and specify a threshold. This prevents the primary/secondary switchover from being affected by high master-slave delay.

     In the Basic Information section of the ticket details page, click chunk option. In the chunk option dialog box, set an appropriate value for the master-slave delay threshold (seconds) parameter. If the master-slave delay is greater than the threshold, the SQL execution is interrupted.

     Note

     Only ApsaraDB RDS for MySQL databases support the master-slave delay check feature.

   • Optional. Modify the SQL statements.

     After you submit the ticket, DMS automatically prechecks the SQL statements. If the precheck fails, click SQL Statements for Modification in the Precheck step to modify the SQL statements and try again.

   Note

   Before you submit the ticket for approval, you can modify the chunk option and Schedule Configuration parameters in the Basic Information section. You cannot modify these parameters after you submit the ticket for approval.

5. Click Submit for Approval. Tickets for instances managed in Security Collaboration mode are approved based on the specified approval rules. Tickets for instances managed in Stable Change mode are automatically approved.

6. After the ticket is approved, DMS automatically generates the scheduled task and sends an email to the ticket owner. You can click View Scheduled Tasks in the Basic Information section to view the scheduling information. You can also perform the following operations:

   • Suspend the scheduled task

     Note

     To stop a scheduled task, go to the details page of the ticket, click Close Ticket in the upper-right corner, enter the reason for closing the ticket, and then click Submit.

   • Restart the scheduled task

     Note

     After you close the ticket, you cannot restart the scheduled task unless you submit another ticket.

   • Change the ticket owner

     By default, the owner of a ticket is the user who submits the ticket. Only the ticket owner has the permissions to suspend and restart the scheduled task that corresponds to the ticket. Each time the scheduled task is run, DMS sends an email only to the ticket owner.

7. After the scheduled task is generated, DMS executes the SQL script that is generated based on your configurations at the specified point in time and intervals. You can view all scheduling information and the details each time the scheduled task is run on the ticket details page.

   Note

   Each time before the scheduled task is run, DMS checks whether the previous scheduled task is complete. If the previous scheduled task is still in progress, DMS does not execute the SQL script again. Therefore, we recommend that you carefully determine the interval based on your business requirements.

FAQ

• Are my services affected if I recycle tablespace fragments when the historical data is being cleared?

  If the lock-free schema change feature is enabled for your database instance, your services are not affected if you recycle tablespace fragments when the historical data is being cleared. If the lock-free schema change feature is disabled for your database instance, we recommend that you recycle tablespace fragments during off-peak hours to reduce the impact on your services. For more information about how to enable the lock-free schema change feature, see Enable the lock-free schema change feature.

• Can I stop defragmentation if the execution duration of defragmentation is longer than expected?

  Yes, if the execution duration of defragmentation is longer than expected, you can stop defragmentation in the Execute section of the ticket details page.