Interaction Test on Online Workloads and ADD INDEX Operations

Test purpose

This document tests the interaction effects between online workloads and ADD INDEX operations in the OLTP scenario.

Test version, time, and place

TiDB version: v3.0.1

Time: July, 2019

Place: Beijing

Test environment

This test runs in a Kubernetes cluster deployed with 3 TiDB instances, 3 TiKV instances and 3 PD instances.

Version information

Sysbench version：1.0.17

TiDB parameter configuration

TiDB, TiKV and PD all use the default TiDB Operator configuration.

Cluster topology

Online workloads simulation using Sysbench

Use Sysbench to import a table with 2,000,000 rows of data into the Kubernetes cluster.

Execute the following command to import data:

sysbench oltp_common \
    --threads=16 \
    --rand-type=uniform \
    --db-driver=mysql \
    --mysql-db=sbtest \
    --mysql-host=$tidb_host \
    --mysql-port=$tidb_port \
    --mysql-user=root \
    prepare --tables=1 --table-size=2000000

Execute the following command to run the test:

sysbench $testname \
    --threads=$threads \
    --time=300000 \
    --report-interval=15 \
    --rand-type=uniform \
    --rand-seed=$RANDOM \
    --db-driver=mysql \
    --mysql-db=sbtest \
    --mysql-host=$tidb_host \
    --mysql-port=$tidb_port \
    --mysql-user=root \
    run --tables=1 --table-size=2000000

Test plan 1: Frequently perform write operations to the target column of the ADD INDEX statement

1. Start the oltp_read_write test.
2. Perform at the same time with step 1: use alter table sbtest1 add index c_idx(c) to add an index.
3. Perform at the end of step 2: when the index is added successfully, stop the oltp_read_write test.
4. Get the duration of alter table ... add index and the average TPS and QPS of Sysbench in this period.
5. Gradually increase the value of two parameters tidb_ddl_reorg_worker_cnt and tidb_ddl_reorg_batch_size, and then repeat step 1-4.

Test results

Test result of oltp_read_write without ADD INDEX operations

tidb_ddl_reorg_batch_size = 32

tidb_ddl_reorg_batch_size = 64

tidb_ddl_reorg_batch_size = 128

tidb_ddl_reorg_batch_size = 256

tidb_ddl_reorg_batch_size = 512

tidb_ddl_reorg_batch_size = 1024

tidb_ddl_reorg_batch_size = 2048

tidb_ddl_reorg_batch_size = 4096

Test conclusion

When you perform frequent write operations (this test involves UPDATE, INSERT and DELETE operations) to the target column of the ADD INDEX statement, the default ADD INDEX configuration has a significant impact on the online workload of the system. It is mainly because of the write conflicts caused by the concurrent ADD INDEX operation and column update. The performance of the system is as follows:

• As the value of tidb_ddl_reorg_worker_cnt and tidb_ddl_reorg_batch_size parameters increase, the value of TiKV_prewrite_latch_wait_duration increases significantly, slowing down the write speed.
• When the value of tidb_ddl_reorg_worker_cnt and tidb_ddl_reorg_batch_size are very large, you can execute the admin show ddl command to see multiple retry attempts of the DDL job, such as Write conflict, txnStartTS 410327455965380624 is stale [try again later], ErrCount:38, SnapshotVersion: 410327228136030220. In this situation, the ADD INDEX operation takes a very long time to complete.

Test plan 2: Do not perform write operations to the target column of the ADD INDEX statement (query-only)

1. Start the oltp_read_only test.
2. Perform at the same time with step 1: use alter table sbtest1 add index c_idx(c) to add an index.
3. Perform at the end of step 2: when the index is added successfully, stop the oltp_read_only test.
4. Get the duration of alter table ... add index and the average TPS and QPS of Sysbench in this period.
5. Gradually increase the value of two parameters tidb_ddl_reorg_worker_cnt and tidb_ddl_reorg_batch_size, and then repeat step 1-4.

Test results

Test result of oltp_read_only without ADD INDEX operations

tidb_ddl_reorg_batch_size = 32

tidb_ddl_reorg_batch_size = 1024

tidb_ddl_reorg_batch_size = 4096

Test conclusion

When you only perform query operations to the target column of the ADD INDEX statement, the effect of ADD INDEX operations on online workloads is not obvious.

Test plan 3: The target column of the ADD INDEX statement is irrelevant to online workloads

1. Start the oltp_read_write test.
2. Perform at the same time with step 1: use alter table test add index pad_idx(pad) to add an index.
3. Perform at the end of step 2: when the index is added successfully, stop the oltp_read_only test.
4. Get the duration of alter table ... add index and the average TPS and QPS of Sysbench in this period.
5. Gradually increase the value of two parameters tidb_ddl_reorg_worker_cnt and tidb_ddl_reorg_batch_size, and then repeat step 1-4.

Test results

Test result of oltp_read_write without ADD INDEX operations

tidb_ddl_reorg_batch_size = 32

tidb_ddl_reorg_batch_size = 1024

tidb_ddl_reorg_batch_size = 4096

Test conclusion

When the target column of the ADD INDEX statement is irrelevant to online workloads, the effect of ADD INDEX operations on the workload is not obvious.

Summary

• When you perform frequent write operations (including INSERT, DELETE and UPDATE operations) to the target column of the ADD INDEX statement, the default ADD INDEX configuration causes relatively frequent write conflicts, which has a great impact on online workloads. At the same time, the ADD INDEX operation takes a long time to complete due to continuous retry attempts. In this test, you can modify the product of tidb_ddl_reorg_worker_cnt and tidb_ddl_reorg_batch_size to 1/32 of the default value. For example, you can set tidb_ddl_reorg_worker_cnt to 4 and tidb_ddl_reorg_batch_size to 256 for better performance.
• When only performing query operations to the target column of the ADD INDEX statement or the target column is not directly related to online workloads, you can use the default ADD INDEX configuration.