Example of Concurrent Table Creation

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

Original topic: 并发建表例子

| username: vincentLi

[Test Environment for TiDB]
[TiDB Version]
[Reproduction Path]
Creating 1024*30 tables for sharding, found the efficiency to be very low.
[Encountered Issue: Problem Description and Impact]
Referred to the official documentation, which states:
“To create tables faster, it is recommended to send multiple DDL requests concurrently to achieve the fastest table creation speed. If DDL requests are sent serially and not to the Owner node, the table creation speed will be very slow.”
Does anyone have specific examples?

| username: zhanggame1 | Original post link

Upgrading to version 8.1 includes DDL table creation optimizations.

| username: 啦啦啦啦啦 | Original post link

Write a script, such as in Python, for multithreaded concurrent table creation.

| username: zhanggame1 | Original post link

Creating tables in TiDB is serialized and cannot be done concurrently.

| username: 啦啦啦啦啦 | Original post link

It cannot be concurrent, but concurrent requests can be placed in the execution queue, which will be faster.

| username: TIDB-Learner | Original post link

Multiple connections to different TiDB instances sending DDLs, server performance is not an issue. It probably won’t be created in a short time.

| username: zhaokede | Original post link

DDL execution is serialized in a queue.

| username: Jack-li | Original post link

It is also necessary to adjust the concurrency level appropriately to avoid overloading.

| username: zhh_912 | Original post link

Does it have anything to do with the performance of the host machine? You can take a look.

| username: TiDB_C罗 | Original post link

Since it is sharding, the table structures are the same, right? Then using “create table a like b” should be faster, right?

| username: 健康的腰间盘 | Original post link

Upgrade to version 8, with targeted optimizations.

| username: 友利奈绪 | Original post link

Serial execution is recommended to upgrade to 8.

| username: kelvin | Original post link

DDL execution is serialized in a queue.

| username: WinterLiu | Original post link

DDL is a queue, it can’t be fast.

| username: TiDBer_小阿飞 | Original post link

In MySQL 8.0, the INSTANT method was introduced, which only modifies the MetaData without affecting the table data, so the execution efficiency is almost unrelated to the table size. The full name is “Online DDL for Fast Column Addition” (at the second level), and it does not cause business jitter. This feature was introduced in MySQL 8.0.12 and was contributed by the Tencent Games DBA team. Chinese programmers are indeed quite impressive :call_me_hand: :call_me_hand: :call_me_hand:
Reference: Functionality Added or Changed
InnoDB now supports ALGORITHM=INSTANT for the following ALTER TABLE operations:

  • Adding a column. This feature is referred to as “Instant ADD COLUMN”.
  • Adding or dropping a virtual column.
  • Adding or dropping a column default value.
  • Modifying the definition of an ENUM or SET column.
  • Changing the index type.
  • Renaming a table.
    https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-12.html
    But it seems that currently only ADD COLUMN and Renaming a table are supported.
| username: vincentLi | Original post link

Thank you all for your guidance. I feel that the original text means to first send all the table creation DDLs to the queue to wait uniformly, rather than sending the create table DDLs serially and waiting for success before sending the next one. In fact, this does not take into account that there may be dependencies between these table creation statements. For example, the creation of table b depends on the successful creation of table a.

| username: 小龙虾爱大龙虾 | Original post link

There should be no problem.

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

The image you provided cannot be processed directly by this text-based translation API. Please provide the text content you need translated.

| username: Kongdom | Original post link

This kind of control can only be managed at the business level; the database itself should not be able to control it.

| username: 濱崎悟空 | Original post link

Create tables serially, try the new version of TiDB.