Adding indexes to multiple tables in parallel can only be done serially one at a time in the running state, while others are queued

translator_bot · June 23, 2024, 7:13am

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

Original topic: 多个表并行加索引，只能串行一个一个在running状态，其它都在排队

| username: starCrush

To improve efficiency, please provide the following information. A clear problem description will help resolve the issue faster:
【TiDB Usage Environment】

【Overview】 Scenario + Problem Overview
Using sysbench to prepare data for 30 tables, but found that adding index is done serially, while others are in a queueing state.

【Background】 Operations performed

【Phenomenon】 Business and database phenomena

【Problem】 Current issue encountered

Only one table’s add index is running, while others are queued. Is there a misconfiguration on my part?

【Business Impact】

【TiDB Version】
TiDB v5.4.2

【Application Software and Version】

【Attachments】 Relevant logs and configuration information

TiUP Cluster Display information
TiUP Cluster Edit config information

Monitoring (https://metricstool.pingcap.com/)

TiDB-Overview Grafana monitoring
TiDB Grafana monitoring
TiKV Grafana monitoring
PD Grafana monitoring
Corresponding module logs (including logs from 1 hour before and after the issue)

For questions related to performance optimization or troubleshooting, please download the script and run it. Please select all and copy-paste the terminal output.

translator_bot · June 23, 2024, 7:13am

| username: forever | Original post link

In TiDB, only one TiDB Server can perform Online DDL operations at any given time. Completed jobs are moved to the history queue, and the Owner then fetches the next job from the job queue to execute.

List Item

The scheduling process for Online DDL operations in TiDB is roughly as follows:

Different users connect to different TiDB Servers, and the DDL operations they issue are received by the start job module, which generates a corresponding job and places it in the job queue on the TiKV nodes.

The worker module in the TiDB Server that assumes the Owner role fetches the Online DDL job from the job queue on the TiKV nodes to execute. Completed jobs are moved to the history queue, and the Owner then fetches the next job from the job queue to execute.

When the current Owner’s term ends, a new TiDB Server is elected as the new Owner to execute Online DDL tasks. Meanwhile, the schema load module in the new Owner synchronizes the latest schema information of all tables to the cache of the TiDB Server.

translator_bot · June 23, 2024, 7:13am

| username: forever | Original post link

There are two queues, one for indexing and one for non-indexing DDL, handled by two workers.

translator_bot · June 23, 2024, 7:13am

| username: h5n1 | Original post link

The pain of TiDB DDL: Adding indexes can only be done serially, and the table with the added index cannot have other DDL operations, otherwise it will cause subsequent non-add index operations on other tables to be blocked. The speed of adding indexes can only be increased by adjusting parameters: tidb_ddl_reorg_batch_size and tidb_ddl_reorg_worker_cnt.