Use TiDB DDL V2 to Accelerate Table Creation

Starting from v7.6.0, the new version V2 of TiDB DDL supports creating tables quickly, which improves the efficiency of bulk table creation.

TiDB uses the online asynchronous schema change algorithm to change the metadata. All DDL jobs are submitted to the mysql.tidb_ddl_job table, and the owner node pulls the DDL job to execute. After executing each phase of the online DDL algorithm, the DDL job is marked as completed and moved to the mysql.tidb_ddl_history table. Therefore, DDL statements can only be executed on the owner node and cannot be linearly extended.

However, for some DDL statements, it is not necessary to strictly follow the online DDL algorithm. For example, the CREATE TABLE statement only has two states for the job: none and public. Therefore, TiDB can simplify the execution process of DDL, and executes the CREATE TABLE statement on a non-owner node to accelerate table creation.

Compatibility with TiDB tools

  • TiCDC does not support replicating the tables that are created by TiDB DDL V2.

Limitation

You can now use TiDB DDL V2 only in the CREATE TABLE statement, and this statement must not include any foreign key constraints.

Use TiDB DDL V2

You can enable or disable TiDB DDL V2 by specifying the value of the system variable tidb_ddl_version .

To enable TiDB DDL V2, set the value of this variable to 2:

SET GLOBAL tidb_ddl_version = 2;

To disable TiDB DDL V2, set the value of this variable to 1:

SET GLOBAL tidb_ddl_version = 1;

Implementation principle

The detailed implementation principle of TiDB DDL V2 for accelerating table creation is as follows:

  1. Create a CREATE TABLE Job.

    This step is the same as that of the V1 implementation. The corresponding DDL Job is generated by parsing the CREATE TABLE statement.

  2. Execute the CREATE TABLE job.

    Different from V1, in TiDB DDL V2, the TiDB node that receives the CREATE TABLE statement executes it directly, and then persists the table structure to TiKV. At the same time, the CREATE TABLE job is marked as completed and inserted into the mysql.tidb_ddl_history table.

  3. Synchronize the table information.

    TiDB notifies other nodes to synchronize the newly created table structure.