[TiDB Usage Environment] Production Environment
[TiDB Version]
[Reproduction Path] What operations were performed that led to the issue
[Encountered Issue: Problem Phenomenon and Impact] The TiDB cluster uses SSD disks, TiDB v6.5.0. For a large table with 200 million rows and over 70 columns, adding a composite index with 3 columns took from 2023-09-05 18:05:58 to 2023-09-05 20:42:00, totaling 2 hours and 37 minutes.
Is there any way to improve the efficiency of adding indexes and reduce the time consumption?
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]
The link you provided appears to be an image from the AskTUG forum. Unfortunately, I cannot view or translate the content of the image directly. Please provide the text you need translated, and I will be happy to assist you.
Setting @@global.tidb_ddl_reorg_batch_size to 1024 and @@global.tidb_ddl_reorg_worker_cnt to 8 can improve the index creation speed. However, it is not recommended to set these values too high.
It looks like you added the index using the traditional method based on your DDL job results. In version 6.5, there is a fast index addition feature with tidb_ddl_enable_fast_reorg. First, check if the default value is On. Were there any other DDL operations when adding the index?
Will the columns for which indexes are created be frequently updated and modified? This could lead to conflicts and affect normal business operations. If the columns involved in the index are updated very frequently, it will cause a lot of conflicts and retries, making the index creation process take a long time.
You can try adjusting the following parameters: tidb_ddl_reorg_worker_cnt and tidb_ddl_reorg_batch_size to dynamically adjust the index creation speed. Generally, the smaller their values, the less impact on the system, but the longer the execution time. Additionally, you can set the parameter tidb_ddl_reorg_priority to PRIORITY_HIGH to keep the index creation task at a high priority to speed up the process.
TiDB supports online index addition operations, which can be performed using ADD INDEX or CREATE INDEX. Adding an index does not block data reads and writes in the table. You can adjust the parallelism of the DDL operation re-organize phase and the batch size of index backfill by modifying the following system variables:
To minimize the impact on online business, the default speed for adding indexes is relatively conservative. When the target column for adding an index only involves query load or is not directly related to the online load, you can appropriately increase the above variables to speed up the index addition:
SET @@global.tidb_ddl_reorg_worker_cnt = 16;
SET @@global.tidb_ddl_reorg_batch_size = 4096;
When the target column for the index addition operation is frequently updated (including UPDATE, INSERT, and DELETE), increasing the above configurations can cause more frequent write conflicts, resulting in a larger online load; at the same time, the index addition operation may take a long time to complete due to constant retries. In this case, it is recommended to reduce the above configurations to avoid write conflicts with online business:
SET @@global.tidb_ddl_reorg_worker_cnt = 4;
SET @@global.tidb_ddl_reorg_batch_size = 128;
|
username: vcdog
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 