ApsaraDB RDS:AliSQL DDL best practices

Method 1: Estimation based on Performance Schema (PFS)

Starting from MySQL 5.7, DDL-related Stage statistics were added to PFS. You can view DDL progress through processlist and the events_stages_current table in the P_S database, and estimate the remaining time.

• Applicable scenario: Real-time monitoring of DDL progress during execution.

• 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

  Execute the SHOW Processlist command to obtain the ID based on the executed SQL.

• Example: For a local Sysbench 10 GB table, the query result shows TIME=116, PROGRESS=83.9961%, as shown in the following figure:

  • Calculated time = TIME / PROGRESS = 116 / (83.9961%) = 138.1 s

  • Actual execution time: 2 min 3.14 s = 123.14 s

Method 2: Estimation based on tablespace size

• Applicable scenario: Estimation without enabling PFS (which consumes memory and affects performance, RDS MySQL disables it by default).

• 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. Based on the DATA_LENGTH size obtained from the query, directly estimate the DDL time. Taking the OPTIMIZE TABLE operation as an example:

     • Cloud disk instances (premium performance disk and ESSD cloud disk): Estimate at a speed of 30~60 MB/s.

     • High-performance local disk instances: Estimate at a speed of 50~100 MB/s.

       Note

       The speeds of 30~60 MB/s and 50~100 MB/s are estimated values based on the IO capability of the instance and may vary in practice.

  3. (Optional) Index impact: If the table has many secondary indexes, you also need to consider the time for sorting and building secondary indexes. Estimate the index building time at 1/3 of the main table speed.

• Example: For a local Sysbench 10 GB table, the query result is as follows:

  • Calculated time = DATA_LENGTH / 1024 / 1024 / 100 + INDEX_LENGTH / 1024 / 1024 / 100 × 3 = 10270785536 / 1024 / 1024 / 100 + 665829376 / 1024 / 1024 / 100 × 3 = 117 s

  • Actual execution time: 2 min 3.14 s = 123.14 s

Basic principles

1. DDL is a change operation. Try to avoid business peak hours.

2. DDL operations that support the INSTANT algorithm, or other operations that only modify metadata, can be executed safely (to avoid being unable to obtain MDL X locks at the beginning and end of the operation, still pay close attention to slow queries and large transactions).

3. DDL operations that only support the COPY algorithm make the table read-only during execution and do not allow write operations. Execute these operations with caution.

4. Other DDL operations that support the INPLACE algorithm allow the table to be read and written during execution, but you need to comprehensively evaluate the execution time, space usage, and potential replication delay issues.

AliSQL DDL optimization features

• Large table deletion optimization

  • Feature: Asynchronously cleans up large files to avoid file system jitter.

  • Parameter: innodb_data_file_purge=ON (enabled by default).

• Buffer Pool management optimization

  • Feature: Reduces DDL execution time and lowers the performance impact of DDL operations.

  • Parameter: loose_innodb_rds_faster_ddl=ON (needs to be manually enabled).

• Second-level column addition

  • Feature: Metadata change without rebuilding the table.

  • Parameter: loose_innodb_instant_ddl_enabled=ON (enabled by default in MySQL 8.0, needs to be manually enabled in version 5.7 and below).

• Other optimizations

  • AHI cleanup optimization: Optimizes performance issues caused by AHI cleanup during table and index deletion. Parameter: innodb_rds_drop_ahi_ahead, default is OFF.

  • Unique index conflict optimization: Optimizes issues where unique index conflicts cause DDL failures during DDL operations.

  • MDL lock wait logic optimization: Optimizes the blocking wait for MDL X locks to non-blocking wait, solving the problem of business-wide blocking caused by DDL operations that cannot obtain MDL locks for a long time.

  • ANALYZE TABLE optimization: Solves the problem where ANALYZE TABLE may cause business blocking in MySQL 5.7.

  • Parallel DDL: Fixes DDL performance regression issues.

References

• Upgrade to the latest version of AliSQL to get all optimization features

• AliSQL kernel minor version release notes