Adapting to TiDB Issues: Application Deployment Timeout Due to Prolonged DDL Statement Execution Time

Translated
1

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

Original topic: 适配tidb问题，DDL语句执行时间过长导致应用发布超时问题

| username: TiDBer_sUGm8DSa

Is there anyone who can answer this TiDB DDL statement performance issue? On a MySQL database with 4 cores and 8GB of RAM, modifying table column data types usually takes milliseconds, but on TiDB it takes about 3 seconds.

【TiDB Environment】Testing
【TiDB Version】v6.1.0
【Reproduction Path】Directly using the database connection tool BEAVER to test modifying table column data types, the average execution time is 3 seconds.
Example of the execution statement:
ALTER TABLE XD_wbshe59p_V20221118173739.Smt_AppEnviroment MODIFY COLUMN EnviromentName varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL;
Execution result screenshot:

Enterprise WeChat Screenshot_16704017441212
Enterprise WeChat Screenshot_167040174412121380×776 94.3 KB

【Problem Encountered: Phenomenon and Impact】
Affects application deployment, as the database statement execution time is too long, causing application packaging to timeout.
【Resource Configuration】
tidb-pd 4c8g
TiKV-01 4c8g
TiKV-02 4c8g
TiKV-03 4c8g
TiCDC 4c8g
TiFlash 8c16g
【Attachments: Screenshots/Logs/Monitoring】

2
| username: h5n1 | Original post link

TiDB has a feature to ensure the consistency of async commit, where DDL operations like adding a secondary index will wait for 2 seconds.

3
| username: TiDBer_sUGm8DSa | Original post link

Is there any way to solve the problem of long execution time for this DDL statement?

4
| username: 我是咖啡哥 | Original post link
  • Due to compatibility with the Async Commit feature, DDL will have a certain waiting time (about 2.5s) before starting to enter Reorg Data:
Query OK, 0 rows affected (2.52 sec)

The documentation does mention this. Got it. Thanks @h5n1.

5
| username: 特雷西-迈克-格雷迪 | Original post link

My instance has no waiting, version 6.1.2

image
image870×384 8.49 KB

6
| username: buddyyuan | Original post link

admin show ddl jobs;
Check the results.

7
| username: TiDBer_sUGm8DSa | Original post link

I upgraded to 6.4.0 and still have this issue.

8
| username: TiDBer_sUGm8DSa | Original post link

1670467858848
16704678588481380×770 126 KB

9
| username: buddyyuan | Original post link

Take this bunch of scripts and run them under the MySQL client to see. Are you increasing or decreasing this field?

10
| username: TiDBer_sUGm8DSa | Original post link

In MySQL, both increasing and decreasing the size are at the millisecond level.

11
| username: buddyyuan | Original post link

Since there are no issues with the MySQL client, it must be a problem with the DBeaver client. :smiley:

12
| username: TiDBer_sUGm8DSa | Original post link

I am using the same client, DBEAVER, but with different databases. One is TiDB, and the other is MySQL. Only TiDB has this issue.

13
| username: jansu-dev | Original post link
  1. Firstly, it cannot be ensured that DBEAVER has no issues. There have been several instances where the behavior of Navicat, DBEAVER, and MySQL client differed. Moreover, MySQL and TiDB use different implementations behind the scenes :thinking:, and only the MySQL Protocol is compatible. It is highly likely that the issue lies with third-party tools.
  2. Secondly, regarding the problem, set the slow log threshold to 0 in the test environment to collect all SQLs, capture this DDL, and then check the information inside to see if it is useful and at which stage it is slow (this is just an idea and may not ultimately solve the problem, but it might be helpful).
14
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.