Support dynamic change to DDL job configuration
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Feature Request
**Is your feature request related to a problem? Please describe:**
TiDB v8.3.0 [removes the dynamic change feature](https://github.com/pingcap/tidb/pull/55334) for `tidb_ddl_reorg_worker_cnt` and `tidb_ddl_reorg_batch_size`, but this feature is useful in some scenarios:
The foreseeable business peak arrives during the DDL operation, and it is necessary to reduce DDL's impact on the cluster.
- DDL execution reaches a certain stage, and resource usage suddenly increases. The resources available to DDL must be reduced (for example, ADD INDEX entering the ingest stage usually incurs a large CPU memory overhead).
- During the DDL operation, the user observed that the data backfilling speed was slow, and it is necessary to increase the backfilling concurrency to complete the DDL within the expected time.
Although the above scenario can be bypassed by canceling and re-executing DDL, it reduces usability and affects user experience.
**Describe the feature you'd like:**
Add a new syntax that supports dynamically changing relevant config for DDL jobs.
```
ADMIN ALTER DDL JOBS *job_id* THREAD = 8; -- tidb_ddl_reorg_worker_cnt
ADMIN ALTER DDL JOBS *job_id* BATCH_SIZE = 256; -- tidb_ddl_reorg_batch_size
ADMIN ALTER DDL JOBS *job_id* MAX_WRITE_SPEED = '200MiB'; -- tidb_ddl_reorg_max_write_speed
```
This syntax refers to the ADMIN clause currently supported by TiDB.
```
ADMIN SHOW DDL JOBS ...
ADMIN PAUSE DDL JOBS ...
ADMIN RESUME DDL JOBS ...
ADMIN CANCEL DDL JOBS ...
```
`THREAD` and `BATCH_SIZE` only work for DDLs that need to reorganize data (reorg) types, currently including `ADD INDEX`, `MODIFY COLUMN`, and `REORGANIZE PARTITION`. Changing job config is not supported for non-reorg DDLs.
On the other hand, add a column `COMMENTS` to the result set of `ADMIN SHOW DDL JOBS`, which displays the current DDL job config:
```
ADMIN SHOW DDL JOBS *job_id*;
... | COMMENTS |
... | THREAD=4, BATCH_SIZE=256 |
```
**Describe alternatives you've considered:**
- Revert https://github.com/pingcap/tidb/pull/55334
**Teachability, Documentation, Adoption, Migration Strategy:**
Contributor guide
Assessment
This issue has not been assessed yet.