Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 多个ddl 如何调整执行顺序，让修改表结构的先执行，索引后执行
There is a DDL adding an index in the system, and there is a temporary requirement to modify the table structure. However, the modification of the table structure and the creation of the table are queued behind. How can I increase the priority of modifying the table structure? I seem to have seen before that the queues for modifying the table structure and the index are not the same, right? Seeking a solution from experts.
TiDB has two DDL queues: an add index queue and another queue for other types of DDL operations. Generally speaking, operations between the two queues do not affect each other, but there are dependencies between them. For example, if a column is added and an index is added to this column, the add index operation must wait for the add column operation to complete. Additionally, some DDL operations, such as changing the field type, also involve data backfilling and can be time-consuming, so it may be advisable to schedule them later.
Currently, the situation is that the queue for adding indexes is blocking the DDL for modifying the table structure, and the two are not even on the same table. How can I make the table structure modification and index addition execute in parallel?
admin show ddl jobs to check
The picture in my question is from admin show ddl jobs, and the modify is queued at the back.
If the add column operation is not completed, adding an index should report an error and not enter the queue, right?
The table structure modification has not taken effect yet, but the index is being written normally.
DDL operations will be queued for execution, and an error will indeed be reported when adding an index.
Currently, the table with the indexed column and the table with the index are not the same. The previous indexes have already been completed for two, and the modification of the table is still in the queue, which does not conform to the principle of two independent queues.
Indeed, it doesn’t quite fit, and there are still some unknown details.
Is there a primary-foreign key relationship between the two tables, and are you operating on the primary-foreign key fields?
Try canceling this drop index operation: ADMIN CANCEL DDL JOBS job_id
In the same table, jobs with smaller job IDs are executed first, and DDL operations on the same table are executed serially. If there is an add index operation in the queue, it will block the subsequent drop index operation. The drop index uses a different job queue and is the first in that job queue, which causes subsequent operations like modify column to be blocked.
I tested it:
- Due to the serial dependency of DDL on the same table, the unfinished previous operation causes the cancel drop index to remain in the canceling state. Once the previous operation is completed, it quickly changes to canceled.
- Executing add index on table 1 and modify column on table 2 simultaneously does not cause blocking and can be executed in parallel.
To avoid blocking non-add index DDL operations on other tables, avoid executing other types of DDL operations when performing add index on the same table.
It seems like a deadlock.
It seems that the official team is currently improving DDL by using system tables under MySQL to replace the queue.
This topic will be automatically closed 60 days after the last reply. No new replies are allowed.
|
username: 爱白话的晓辉
|
username: 
|
username: 
|
username: 
|
username: 
|
username: