All Products
Search

This Product

    Data Management:Historical Data Cleanup

    Document Center

    Data Management:Historical Data Cleanup

    Last Updated:Jan 06, 2026

    When large tables cause slow queries or storage space runs low, use the Historical Data Cleanup feature in Data Management (DMS) to automatically remove old data, improve database performance, and prevent stability issues.

    Prerequisites

    • The database must be MySQL.

    • The database instance must use the Stable Change or Security Collaboration control mode. For more information about Control Modes, see Control Mode.

    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

      In DMS simple mode, click the 2023-01-28_15-57-17.png icon in the upper-left corner, then select All Features > Database Development > Data Change > Historical Data Cleanup.

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

      The following table describes some of the parameters.

      Parameter

      Description

      Database

      Select a database for which you have change permissions. You cannot submit a ticket with read-only or table-level permissions only. For more information, see View My Permissions.

      Deletion Settings

      Enter the Table Name, Time Field, Time Accuracy, Retention Period (Days), and Filter Condition (Nullable). The system automatically generates a cleanup script based on this information.

      Note

      • For logical tables, enter the logical table name.

      • The retention period defines when data is automatically deleted. For example, a 7-day retention period removes data older than 7 days.

      For example: If Table Name is api_call_record_11, Time Field is gmt_create, Retention Period is 7, and Filter Condition is status = 1 or status=2, the following SQL statement is generated: DELETE FROM `api_call_record_11` WHERE `gmt_create` < SUBDATE(CURDATE(),INTERVAL 7 DAY) AND (status = 1 or status=2);

      Schedule

      DMS scans the entire table during data cleanup and removes data in batches based on the primary key or a non-null unique key. Schedule execution during off-peak hours with low frequency to minimize database performance impact.

      Note

      • The actual execution time of a scheduled task may deviate by up to one minute from the scheduled time.

      • The minimum interval for scheduled execution is one hour. By default, the task runs at 02:00 every day.

      Policy Configuration

      You can specify an execution duration. The task will automatically pause after running for the specified duration to avoid impacting services during peak hours.

      • Execute Task Without End Time.

      • Specify End Time (Hours): Set an execution duration to prevent bottlenecks in downstream sync pipelines (such as DTS or AnalyticDB) from impacting DMS performance.

      After specifying the duration, you can enable Periodically Optimize Table (defragmentation), which is disabled by default. It runs OPTIMIZE TABLE after a specified number of cleanup cycles. The default interval is 60 cycles. For example, with interval count 60, the system executes OPTIMIZE TABLE every 60 cleanups.

      Note

      • The OPTIMIZE TABLE feature is supported only for RDS for MySQL and PolarDB for MySQL databases.

      • The duration of the OPTIMIZE TABLE operation is limited by the execution duration specified in the policy configuration. The OPTIMIZE TABLE operation stops when the task execution duration ends.

      Change Stakeholder

      Specified stakeholders can view and collaborate on the ticket. Other users cannot access it, except administrators and DBAs.

    4. After you submit the ticket, you can enable a replication lag check, set a threshold, and modify the SQL.

      • (Optional) To prevent excessive replication lag that can affect primary/standby instance switchovers, enable a replication lag check and set a threshold.

        In the Basic Information section, click chunk option to set a reasonable replication lag threshold in seconds. If the replication lag exceeds the threshold, SQL execution is interrupted.

        Note

        Currently, this feature is supported only for ApsaraDB RDS for MySQL databases.

      • (Optional) Modify the SQL.

        After you submit the ticket, the system automatically performs an SQL precheck. If the precheck fails, you can modify SQL based on the failure reason and try again.

      Note

      Before submitting for approval, you can modify the batch execution and scheduling configurations. Once submitted, these settings cannot be changed.

    5. Click Submit. For instances in Security Collaboration mode, the ticket is then sent for approval according to the configured rules. For instances in Stable Change mode, the ticket is automatically approved.

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

      • Pause Schedule

        Note

        If you need to disable the schedule permanently, go to the ticket details page for the Historical Data Cleanup, click Close Ticket in the upper-right corner, enter a reason, and click Submit.

      • Resume Schedule

        Note

        After a ticket is closed, you must submit a new ticket to resume the schedule.

      • Change Ticket Owner

        The user who submitted the ticket is the owner by default. Only the ticket owner can pause or resume the schedule. Email notifications for each scheduled execution are also sent exclusively to the owner.

    7. After the scheduled task is generated, the system executes the generated SQL script according to the configured schedule policy. You can view all scheduling information and the details of each execution in the ticket.

      Note

      At each scheduled time, the system checks if a cleanup task for the ticket is already running. If a task is in progress, a new one is not created. Therefore, you must configure the execution frequency appropriately.

    FAQ

    • Q: Will running OPTIMIZE TABLE as part of a Historical Data Cleanup task impact business?

      A: It depends. The OPTIMIZE TABLE operation will not impact business if you have enabled Lock-free Schema Change for the target instance. If Lock-free Schema Change is not enabled, perform OPTIMIZE TABLE during off-peak hours to minimize business impact. To enable Lock-free Schema Change, see Enable the lock-free schema change feature.

    • Q: How can I stop an OPTIMIZE TABLE operation that is taking too long?

      A: Navigate to the Ticket Details page and pause the task in the Execute section.