ApsaraDB RDS:AliSQL DDL best practices

Method 1: Use Performance Schema (real-time monitoring)

Starting with MySQL 5.7, Performance Schema tracks DDL progress through stage events. You can monitor a running DDL operation and estimate the remaining time by querying the processlist and the events_stages_current table.

• When to use: Real-time progress tracking for a DDL operation that is already running.

• Query statement:

  SELECT * FROM information_schema.processlist WHERE ID = <Replace with actual ID>;
  SELECT THREAD_ID, EVENT_ID, EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED,(WORK_COMPLETED/WORK_ESTIMATED)*100 AS PROGRESS  
  FROM performance_schema.events_stages_current WHERE THREAD_ID = <Replace with actual ID>;  

  Note

  Run SHOW PROCESSLIST to find the process ID that corresponds to your DDL statement.

• Example: For a local 10 GB Sysbench table, the query returns TIME=116 and PROGRESS=83.9961%, as shown below:

  mysql> SELECT * FROM information_schema.processlist WHERE ID = 18;
  +----+------+-----------+----------+---------+------+----------------+------------------------+
  | ID | USER | HOST      | DB       | COMMAND | TIME | STATE          | INFO                   |
  +----+------+-----------+----------+---------+------+----------------+------------------------+
  | 18 | root | localhost | ddl_test | Query   |  116 | altering table | optimize table sbtest1 |
  +----+------+-----------+----------+---------+------+----------------+------------------------+
  1 row in set (0.00 sec)
  
  mysql> SELECT THREAD_ID, EVENT_ID, EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED,(WORK_COMPLETED/WORK_ESTIMATED)*100 AS PROGRESS FROM performance_schema.events_stages_current WHERE THREAD_ID = 32;
  +-----------+----------+------------------------------------------------------+----------------+----------------+----------+
  | THREAD_ID | EVENT_ID | EVENT_NAME                                           | WORK_COMPLETED | WORK_ESTIMATED | PROGRESS |
  +-----------+----------+------------------------------------------------------+----------------+----------------+----------+
  |        32 |      127 | stage/innodb/alter table (read PK and internal sort) |        2273884 |        2707129 |  83.9961 |
  +-----------+----------+------------------------------------------------------+----------------+----------------+----------+
  1 row in set (0.00 sec)

  • Estimated total time = elapsed time / (progress / 100) = 116 / 0.8399 = 138.1 seconds

  • Actual execution time: 123.14 seconds (2 min 3.14 s)

Running optimize table sbtest1; took 2 minutes 3.14 seconds, or about 123 seconds, which closely matches the 138.1 seconds estimated from Performance Schema.

mysql>
mysql> optimize table sbtest1;
+------------------+----------+----------+-------------------------------------------------------------------+
| Table            | Op       | Msg_type | Msg_text                                                          |
+------------------+----------+----------+-------------------------------------------------------------------+
| ddl_test.sbtest1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| ddl_test.sbtest1 | optimize | status   | OK                                                                |
+------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (2 min 3.14 sec)

Method 2: Estimate from tablespace size (before execution)

• When to use: You want to estimate DDL duration before starting the operation. Use this method when Performance Schema is disabled, which is the default for RDS MySQL to reduce memory overhead and performance impact.

• Steps:

  1. Query table size:

     SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA =  <Replace with actual database name> AND TABLE_NAME = <Replace with actual table name>;  

  2. Estimate the DDL time based on your storage type:

     • Cloud disk instances (Premium ESSD and ESSD): 30–60 MB/s

     • Instance with Premium Local SSD: 50–100 MB/s

       Note

       These throughput values are estimates based on typical instance I/O capabilities. Actual speeds may vary.

  3. (Optional) If the table has many secondary indexes, add additional time for index sorting and building. Index building is approximately 3× slower than raw data copy speed.

• Example: For a local 10 GB Sysbench table, the query returns the following result:

  mysql> SELECT * from information_schema.tables WHERE TABLE_SCHEMA = 'ddl_test' AND TABLE_NAME = 'sbtest1'\G
  *************************** 1. row ***************************
    TABLE_CATALOG: def
     TABLE_SCHEMA: ddl_test
       TABLE_NAME: sbtest1
       TABLE_TYPE: BASE TABLE
           ENGINE: InnoDB
          VERSION: 10
       ROW_FORMAT: Dynamic
       TABLE_ROWS: 39452112
   AVG_ROW_LENGTH: 260
      DATA_LENGTH: 10270785536
  MAX_DATA_LENGTH: 0
     INDEX_LENGTH: 665829376
        DATA_FREE: 3145728
   AUTO_INCREMENT: 40000001
      CREATE_TIME: 2025-05-09 15:58:33
      UPDATE_TIME: NULL
       CHECK_TIME: NULL
  TABLE_COLLATION: utf8_general_ci
         CHECKSUM: NULL
   CREATE_OPTIONS:
    TABLE_COMMENT:
  1 row in set (0.00 sec)

  • Estimated time = (DATA_LENGTH / throughput) + (INDEX_LENGTH / throughput × 3) = 9,570/100 + 635/100 × 3 = 95.7 + 19.1 = 117 seconds

  • Actual execution time: 123.14 seconds (2 min 3.14 s)

General guidelines

1. Schedule DDL during off-peak hours. DDL is a change operation that can impact running workloads.

2. Prefer INSTANT or metadata-only operations. These operations are safe to run at any time. However, monitor for slow queries and large transactions that could prevent the DDL from acquiring the required MDL X lock.

3. Use caution with COPY operations. The COPY algorithm blocks all writes to the table for the entire duration of the DDL operation.

4. Plan INPLACE operations carefully. Although INPLACE allows concurrent reads and writes, evaluate the expected execution time, disk space requirements, and potential replication lag before running the operation.

AliSQL DDL optimization features

• Large table deletion optimization

  • Cleans up large data files asynchronously to avoid filesystem jitter when dropping tables.

  • Parameter: innodb_data_file_purge=ON (enabled by default)

• Buffer Pool management optimization

  • Reduces DDL execution time and minimizes the performance impact on concurrent workloads.

  • Parameter: loose_innodb_rds_faster_ddl=ON (must be enabled manually)

• Instant column addition

  • Adds columns by modifying metadata only, without rebuilding the table. Equivalent to MySQL 8.0 INSTANT DDL.

  • Parameter: loose_innodb_instant_ddl_enabled=ON (enabled by default in MySQL 8.0; must be enabled manually in 5.7 and earlier)

• Other optimizations

  • Adaptive Hash Index (AHI) cleanup optimization: Prevents performance degradation caused by AHI cleanup when dropping tables or indexes. Parameter: innodb_rds_drop_ahi_ahead (disabled by default).

  • Unique index conflict handling: Resolves DDL failures caused by unique index conflicts during online DDL operations.

  • Non-blocking MDL lock acquisition: Changes the MDL X lock wait behavior from blocking to non-blocking, preventing a single DDL operation from causing a cascading block on all queries to the table.

  • ANALYZE TABLE fix: Resolves the MySQL 5.7 issue where ANALYZE TABLE blocks subsequent queries on tables with long-running operations.

  • Parallel DDL: Fixes DDL performance regressions by enabling parallel execution of certain DDL stages.

References

• Upgrade to the latest version of AliSQL to access all optimization features.

• AliSQL kernel minor version release notes