PolarDB:Parallel DDL

Add GSIs, repartition tables, or perform Online Modify Column (OMC)

Move partitions or scale tables

Recommended configurations

Example: add a global index at default settings

Instance: 2 CNs, 2 DNs, 16 CPU cores and 64 GB memory each. Add a global index to bmsql_order_line (TPC-C 10,000 warehouse) using default parameter values:

/*+TDDL:cmd_extra(PURE_ASYNC_DDL_MODE=true)*/
ALTER TABLE `bmsql_order_line` ADD GLOBAL INDEX `g_i_ol_d_id`(`ol_d_id`) partition by hash(`ol_d_id`) partitions 32;

Check progress:

show ddl status;
+----------------------------------+-----------+
| METRIC                           | VALUE     |
+----------------------------------+-----------+
| BACKFILL_PARALLELISM             |        32 |
| BACKFILL_ROWS_FINISHED           | 288679866 |
| BACKFILL_ROWS_SPEED              |    150186 |
| BACKFILL_TASK_FAILED             |         0 |
| BACKFILL_TASK_FINISHED           |         1 |
| BACKFILL_TASK_TOTAL              |         0 |
| BACKFILL_TIME_MILLIS             |      3450 |
| CHANGESET_APPLY_PARALLELISM      |         0 |
| CHANGESET_APPLY_ROWS_SPEED       |         0 |
| CHECKER_ROWS_FINISHED            |         0 |
| CHECKER_TIME_MILLIS              |         0 |
| DDL_EXECUTION_TIME_MILLIS        |    696412 |
| DDL_JOBS_FINISHED                |         5 |
| DDL_JOBS_TOTAL                   |         6 |
| DDL_TASK_FAILED                  |         4 |
| DDL_TASK_FINISHED                |       107 |
| DDL_TASK_TOTAL                   |        63 |
| FASTCHECKER_TASK_RUNNING         |         0 |
| FASTCHECKER_TASK_WAITING         |         0 |
| FASTCHECKER_THREAD_POOL_MAX_SIZE |         1 |
| FASTCHECKER_THREAD_POOL_NOW_SIZE |         0 |
| FASTCHECKER_THREAD_POOL_NUM      |         2 |
| THROTTLE_RATE                    |    150000 |
+----------------------------------+-----------+

Key metrics to watch:

• BACKFILL_PARALLELISM — total active backfilling threads

• BACKFILL_ROWS_SPEED — current backfilling rate (rows/s)

• THROTTLE_RATE — active rate limit; -1 means no limit

With default settings, THROTTLE_RATE is 150000, so the backfilling rate is capped at about 150,000 rows/s.

Example: add a global index at maximum DOP

To remove throttling and maximize parallelism (maintenance window or off-peak hours):

/*+TDDL:cmd_extra(SLIDE_WINDOW_TIME_INTERVAL=0,PURE_ASYNC_DDL_MODE=true,PHYSICAL_TABLE_BACKFILL_PARALLELISM=8,GSI_BACKFILL_PARALLELISM=32,GSI_BACKFILL_SPEED_LIMITATION=-1)*/
ALTER TABLE bmsql_order_line ADD GLOBAL INDEX g_i_ol_d_id(`ol_d_id`) partition by hash(`ol_d_id`) partitions 32;

With THROTTLE_RATE at -1 and all 32 threads active, the backfilling rate reaches approximately 860,000 rows/s:

show ddl status;
+----------------------------------+----------+
| METRIC                           | VALUE    |
+----------------------------------+----------+
| BACKFILL_PARALLELISM             |       32 |
| BACKFILL_ROWS_FINISHED           | 37272038 |
| BACKFILL_ROWS_SPEED              |   867669 |
| BACKFILL_TASK_FAILED             |        0 |
| BACKFILL_TASK_FINISHED           |        1 |
| BACKFILL_TASK_TOTAL              |        0 |
| BACKFILL_TIME_MILLIS             |     3450 |
| CHANGESET_APPLY_PARALLELISM      |        0 |
| CHANGESET_APPLY_ROWS_SPEED       |        0 |
| CHECKER_ROWS_FINISHED            |        0 |
| CHECKER_TIME_MILLIS              |        0 |
| DDL_EXECUTION_TIME_MILLIS        |   112397 |
| DDL_JOBS_FINISHED                |        3 |
| DDL_JOBS_TOTAL                   |        4 |
| DDL_TASK_FAILED                  |        2 |
| DDL_TASK_FINISHED                |       75 |
| DDL_TASK_TOTAL                   |       42 |
| FASTCHECKER_TASK_RUNNING         |         0 |
| FASTCHECKER_TASK_WAITING         |         0 |
| FASTCHECKER_THREAD_POOL_MAX_SIZE |        1 |
| FASTCHECKER_THREAD_POOL_NOW_SIZE |        0 |
| FASTCHECKER_THREAD_POOL_NUM      |        2 |
| THROTTLE_RATE                    |       -1 |
+----------------------------------+----------+
23 rows in set (0.02 sec)

Test 1: add a local index

/*+TDDL:cmd_extra(PURE_ASYNC_DDL_MODE=true,ENABLE_TWO_PHASE_DDL=false,ENBALE_DRDS_MULTI_PHASE_DDL=false,MERGE_DDL_CONCURRENT=true,MERGE_CONCURRENT=true,PREFETCH_SHARDS=xxx)*/
ALTER TABLE `bmsql_order_line` ADD LOCAL INDEX i_ol_o_id(ol_o_id);

IOPS saturates at PREFETCH_SHARDS=8. Beyond that, additional threads improve throughput only modestly while CPU usage continues to climb.

Test 2: add a global index

/*+TDDL:cmd_extra(PURE_ASYNC_DDL_MODE=true,SLIDE_WINDOW_TIME_INTERVAL=0,PHYSICAL_TABLE_BACKFILL_PARALLELISM=xxx,GSI_BACKFILL_SPEED_LIMITATION=xxx)*/ ALTER TABLE `bmsql_order_line` ADD GLOBAL INDEX `g_i_ol_o_id`(`ol_o_id`) PARTITION BY HASH(`ol_o_id`) PARTITIONS 32;

CN CPU usage is a significant factor for logically executed DDL. Before increasing PHYSICAL_TABLE_BACKFILL_PARALLELISM, confirm that your CNs have available capacity.