Pause add an index(write reorganization) and set a time window to resume

[scsldb]

Description

In some scenarios, latencies of the SQL statements are very essential and should be stable all the time. However, the DDL CREATE TABLE ... ADD INDEX may affect the latencies of other statements: https://docs.pingcap.com/tidb/stable/online-workloads-and-add-index-operations.

For now, TiDB provided tidb_ddl_reorg_worker_cnt and tidb_ddl_reorg_batch_size system variables. By decreasing them, the resources occipued ADD INDEX operation can be lower, but they cannot be none.

It can be better if such features are provided:

• The ability to pause a running DDL statement like ADD INDEX.
• The ability to set a time-based trigger for customized SQL statements.

By the features above users are able to pause a running ADD INDEX manually, or schedule the ADD INDEX operations to make sure they will be ran at a suitable time.

Category

Feature, Stability

Value

Reduce latency jitter in peak periods of core application scenarios.