Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog Parallel DDL Accelerates the Creation of Secondary Indexes

Parallel DDL Accelerates the Creation of Secondary Indexes

This article explains how PolarDB’s Parallel DDL feature dramatically accelerates the creation of secondary indexes.

PolarDB supports the parallel data definition language (DDL) feature. When available hardware resources exist in a cluster, the parallel DDL feature can speed up the execution of DDL operations. This prevents subsequent DML operations from being blocked and shortens the execution windows of DDL operations.

Prerequisites

When you create secondary indexes, the PolarDB cluster must meet one of the following requirements:

• PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.1.7 or later.

• PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.10 or later.

• PolarDB for MySQL 5.7 whose revision version is 5.7.1.0.7 or later.

When you create primary indexes, the PolarDB cluster must meet one of the following requirements:

• PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.9 or later.

• PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.31 or later.

Limits

The parallel DDL feature can accelerate only the DDL operations that are used to create primary key indexes and secondary indexes. The secondary indexes exclude full-text indexes, spatial indexes, and the secondary indexes on virtual columns.

Enable parallel DDL

Specify the number of concurrent threads for each DDL operation: You can enable the parallel DDL feature by specifying the innodb_polar_parallel_ddl_threads parameter:

1

Database table preparation before testing

Table structure, table data volume, and index before creating secondary indexes

The table has 22,000,004 rows of data.

3

Table creation statement: Table creation statement.sql

4

Create a secondary index without enabling parallel DDL

innodb_polar_parallel_ddl_threads is not enabled.

Description: The number of concurrent threads for each DDL operation. Valid values: 1 to 16. The default value 1 indicates that single-threaded DDL operations are executed. If the value of this parameter is not 1 and when you create secondary indexes, the parallel DDL feature is automatically enabled.

5

Index creation statement: ALTER TABLE `sys_user` ADD KEY `index_phone`(`phonenumber`);

Creating indexes without enabling parallel DDL takes: 93,979 ms

6

Create a secondary index with parallel DDL enabled

Enable: innodb_polar_parallel_ddl_threads is changed to 16

7
8

To optimize the tablespace before adding indexes again, please execute: optimize table sys_user;

Index addition statement: ALTER TABLE `sys_user` ADD KEY `index_phone`(`phonenumber`);

9

Create with parallel DDL enabled took 72,255 ms.

MySQL Database Developers Cloud Database POLARDB Tutorials Data Definition Language Secondary Index PolarDB for MySQL
0 1 0
Share on

Read previous post:

Monitor Changes in the Uptime Value of Resources

Read next post:

Running TPC-H Benchmark on AnalyticDB for PostgreSQL

ApsaraDB

516 posts | 167 followers

You may also like

Comments

ApsaraDB

516 posts | 167 followers

Related Products

More Posts by ApsaraDB

See All