tidb-ddl的速度的调整

一、环境描述

ip	组件	配置
192.168.1.1	tidb、pd、tikv	2c4g
192.168.1.2	tidb、pd、tikv	2c4g
192.168.1.3	tidb、pd、tikv	2c4g

二、实验环境准备

1.建表

# ddl_1,ddl_2,ddl_3表结构一致
CREATE TABLE `ddl_1` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `z_name` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `z_info` varchar(30) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

2.插入

import pymysql
conn = pymysql.Connect()
cur = conn.cursor()
# ddl_1，ddl_2，ddl_3插入数据
sql_str = 'insert into ddl_2(z_name, z_info) values("csdfsdfsdfsda", "qwwerwerisdfnsdvj vsd")'
for i in range(1000):
    sql_str = sql_str + ',("csdfsdfsdfsda", "qwwerwerisdfnsdvj vsd")'
for i in range(1000):
    print(i)
    cur.execute(sql_str)
    conn.commit()
conn.close()

3.查看表信息

mysql> show table ddl_2 regions\G
*************************** 1. row ***************************
           REGION_ID: 1149
           START_KEY: t_80_
             END_KEY: t_85_
           LEADER_ID: 1152
     LEADER_STORE_ID: 5
               PEERS: 1150, 1151, 1152
          SCATTERING: 0
       WRITTEN_BYTES: 0
          READ_BYTES: 0
APPROXIMATE_SIZE(MB): 108
    APPROXIMATE_KEYS: 1346062
1 row in set (0.02 sec)

mysql> show table ddl_1 regions\G
*************************** 1. row ***************************
           REGION_ID: 2
           START_KEY: t_85_
             END_KEY:
           LEADER_ID: 3
     LEADER_STORE_ID: 1
               PEERS: 3, 6, 7
          SCATTERING: 0
       WRITTEN_BYTES: 0
          READ_BYTES: 0
APPROXIMATE_SIZE(MB): 97
    APPROXIMATE_KEYS: 1013491
1 row in set (0.10 sec)

mysql> show table ddl_3 regions\G
*************************** 1. row ***************************
           REGION_ID: 2
           START_KEY: t_89_
             END_KEY:
           LEADER_ID: 3
     LEADER_STORE_ID: 1
               PEERS: 3, 6, 7
          SCATTERING: 0
       WRITTEN_BYTES: 0
          READ_BYTES: 93093000
APPROXIMATE_SIZE(MB): 101
    APPROXIMATE_KEYS: 1031510
1 row in set (0.01 sec)

4.添加索引

mysql> alter table ddl_1 add index idx_z_info(z_info);
Query OK, 0 rows affected (2 min 16.17 sec)

set global tidb_ddl_reorg_batch_size=1024;
mysql> alter table ddl_2 add index idx_z_info(z_info);
Query OK, 0 rows affected (1 min 44.53 sec)

set global tidb_ddl_reorg_worker_cnt=8;
mysql> alter table ddl_3 add index idx_z_info(z_info);
Query OK, 0 rows affected (1 min 40.01 sec)

5.控制ddl的相关参数

参数	意义
ddl_slow_threshold	耗时超过该阈值的 DDL 操作会被输出到日志
tidb_ddl_error_count_limit	用来控制 DDL 操作失败重试的次数。失败重试次数超过该参数的值后，会取消出错的 DDL 操作
tidb_ddl_reorg_priority	用来设置 ADD INDEX 操作 re-organize 阶段的执行优先级，可设置为 PRIORITY_LOW/PRIORITY_NORMAL/PRIORITY_HIGH
tidb_ddl_reorg_batch_size	用来设置 DDL 操作 re-organize 阶段的 batch size，比如 ADD INDEX 操作，需要回填索引数据，通过并发 tidb_ddl_reorg_worker_cnt 个 worker 一起回填数据，每个 worker 以 batch 为单位进行回填
tidb_ddl_reorg_worker_cnt	用来设置 DDL 操作 re-organize 阶段的并发度

三、对比结论

tidb_ddl_reorg_batch_size	tidb_ddl_reorg_worker_cnt	执行时间	消耗cpu
256	4	136s	102%
1024	4	104s	128%
1024	8	100s	130%

结论： 从测试结果上看，越大的tidb_ddl_reorg_batch_size，越大的tidb_ddl_reorg_worker_cnt会带来更快的执行时间，同时也会带来更高的cpu