Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: ALTER TABLE ADD INDEX 执行卡死、无效
[TiDB Usage Environment] Production Environment
[TiDB Version]
[Reproduction Path] What operations were performed when the problem occurred
All ALTER TABLE ADD INDEX operations are stuck and not effective
[Encountered Problem: Problem Phenomenon and Impact]
[Resource Configuration]
Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]
Use ADMIN SHOW DDL; to see the current DDL tasks and their statuses.
Before adding the index with DDL, were there any other changes made in TiDB?
Take a look at the current DDL tasks.
admin show ddl jobs;
Check if there is any change in row_count.
You can also check the TiDB logs to see if they are being written continuously. 
After executing the above statement, as shown in the picture, I executed the statement to add an index again before taking the screenshot, and it has been running continuously.
Just observe the running.
Check the corresponding SQL for any waiting events using show processlist.
admin show ddl
Check if the number of completed rows has changed?
Just wait until running becomes synced. After executing the DDL, this connection can be disconnected.
How large is the data volume of the table with the index added?
Take a look at the TiDB log files.
Version 7.1? The tidb_ddl_enable_fast_reorg is enabled by default. First, check if it is ON. If it is ON, then run show config where name like '%temp-dir%' to see if the disk corresponding to this directory is out of space.
Three directions: data volume, resource constraints, concurrency conflicts
DDL operations, just have to wait.
There is no change, every time I add an index it times out and fails, feeling a bit confused.
“What does ‘timeout failure’ mean?
Can you elaborate?
For example, what kind of error, what kind of phenomenon, etc.”
How large is the table data? The reasons can be summarized as follows: 1. When the table is too large, the ALTER TABLE ADD INDEX operation may take a long time to complete, which appears to be stuck. 2. Insufficient resources in the TiDB cluster, such as memory, CPU, or disk space. 3. There are already many indexes on the table. 4. Other concurrent operations are locking the table, causing the operation to be blocked.
|
username: TiDBer_N3caE3vj
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: