ALTER TABLE ADD INDEX Execution Hangs or Is Ineffective

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: ALTER TABLE ADD INDEX 执行卡死、无效

| username: TiDBer_N3caE3vj

[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]

| username: tidb菜鸟一只 | Original post link

Use ADMIN SHOW DDL; to see the current DDL tasks and their statuses.

| username: linnana | Original post link

Before adding the index with DDL, were there any other changes made in TiDB?

| username: Anna | Original post link

Take a look at the current DDL tasks.

| username: 我是咖啡哥 | Original post link

admin show ddl jobs;

Check if there is any change in row_count.

| username: 我是咖啡哥 | Original post link

You can also check the TiDB logs to see if they are being written continuously. :joy:

| username: TiDBer_N3caE3vj | Original post link

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.

| username: zhanggame1 | Original post link

Just observe the running.

| username: tidb菜鸟一只 | Original post link

Check the corresponding SQL for any waiting events using show processlist.

| username: xingzhenxiang | Original post link

admin show ddl
Check if the number of completed rows has changed?

| username: TiDBer_jYQINSnf | Original post link

Just wait until running becomes synced. After executing the DDL, this connection can be disconnected.

| username: 像风一样的男子 | Original post link

How large is the data volume of the table with the index added?

| username: TiDB_C罗 | Original post link

Take a look at the TiDB log files.

| username: Edwin | Original post link

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.

| username: redgame | Original post link

Three directions: data volume, resource constraints, concurrency conflicts

| username: liuis | Original post link

DDL operations, just have to wait.

| username: cassblanca | Original post link

get a piece of knowledge

| username: TiDBer_N3caE3vj | Original post link

There is no change, every time I add an index it times out and fails, feeling a bit confused.

| username: knull | Original post link

“What does ‘timeout failure’ mean?
Can you elaborate?
For example, what kind of error, what kind of phenomenon, etc.”

| username: cassblanca | Original post link

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.