TiDB Sysbench Performance Test Report -- v5.0 vs. v4.0

Test purpose

This test aims at comparing the Sysbench performance of TiDB v5.0 and TiDB v4.0 in the Online Transactional Processing (OLTP) scenario.

Test environment (AWS EC2)

Hardware configuration

Service typeEC2 typeInstance count
PDm5.xlarge3
TiKVi3.4xlarge3
TiDBc5.4xlarge3
Sysbenchc5.9xlarge1

Software version

Service typeSoftware version
PD4.0 and 5.0
TiDB4.0 and 5.0
TiKV4.0 and 5.0
Sysbench1.0.20

Parameter configuration

TiDB v4.0 configuration

log.level: "error" performance.max-procs: 20 prepared-plan-cache.enabled: true tikv-client.max-batch-wait-time: 2000000

TiKV v4.0 configuration

storage.scheduler-worker-pool-size: 5 raftstore.store-pool-size: 3 raftstore.apply-pool-size: 3 rocksdb.max-background-jobs: 3 raftdb.max-background-jobs: 3 raftdb.allow-concurrent-memtable-write: true server.grpc-concurrency: 6 readpool.unified.min-thread-count: 5 readpool.unified.max-thread-count: 20 readpool.storage.normal-concurrency: 10 pessimistic-txn.pipelined: true

TiDB v5.0 configuration

log.level: "error" performance.max-procs: 20 prepared-plan-cache.enabled: true tikv-client.max-batch-wait-time: 2000000

TiKV v5.0 configuration

storage.scheduler-worker-pool-size: 5 raftstore.store-pool-size: 3 raftstore.apply-pool-size: 3 rocksdb.max-background-jobs: 8 raftdb.max-background-jobs: 4 raftdb.allow-concurrent-memtable-write: true server.grpc-concurrency: 6 readpool.unified.min-thread-count: 5 readpool.unified.max-thread-count: 20 readpool.storage.normal-concurrency: 10 pessimistic-txn.pipelined: true server.enable-request-batch: false

TiDB v4.0 global variable configuration

set global tidb_hashagg_final_concurrency=1; set global tidb_hashagg_partial_concurrency=1;

TiDB v5.0 global variable configuration

set global tidb_hashagg_final_concurrency=1; set global tidb_hashagg_partial_concurrency=1; set global tidb_enable_async_commit = 1; set global tidb_enable_1pc = 1; set global tidb_guarantee_linearizability = 0; set global tidb_enable_clustered_index = 1;

Test plan

  1. Deploy TiDB v5.0 and v4.0 using TiUP.
  2. Use Sysbench to import 16 tables, each table with 10 million rows of data.
  3. Execute the analyze table statement on each table.
  4. Back up the data used for restore before different concurrency tests, which ensures data consistency for each test.
  5. Start the Sysbench client to perform the point_select, read_write, update_index, and update_non_index tests. Perform stress tests on TiDB via AWS NLB. In each type of test, the warm-up takes 1 minute and the test takes 5 minutes.
  6. After each type of test is completed, stop the cluster, overwrite the cluster with the backup data in step 4, and restart the cluster.

Prepare test data

Execute the following command to prepare the test data:

sysbench oltp_common \ --threads=16 \ --rand-type=uniform \ --db-driver=mysql \ --mysql-db=sbtest \ --mysql-host=$aws_nlb_host \ --mysql-port=$aws_nlb_port \ --mysql-user=root \ --mysql-password=password \ prepare --tables=16 --table-size=10000000

Perform the test

Execute the following command to perform the test.

sysbench $testname \ --threads=$threads \ --time=300 \ --report-interval=1 \ --rand-type=uniform \ --db-driver=mysql \ --mysql-db=sbtest \ --mysql-host=$aws_nlb_host \ --mysql-port=$aws_nlb_port \ run --tables=16 --table-size=10000000

Test results

Point Select performance

Threadsv4.0 QPSv4.0 95% latency (ms)v5.0 QPSv5.0 95% latency (ms)QPS improvement
150159451.191.32177876.251.2311.56%
300244790.381.96252675.031.823.22%
600322929.053.75331956.843.362.80%
900364840.055.67365655.045.090.22%
1200376529.187.98366507.477.04-2.66%
1500368390.5210.84372476.358.901.11%

Compared with v4.0, the Point Select performance of TiDB v5.0 has increased by 2.7%.

Point Select

Update Non-index performance

Threadsv4.0 QPSv4.0 95% latency (ms)v5.0 QPSv5.0 95% latency (ms)QPS improvement
15017243.7811.0430866.236.9179.00%
30025397.0615.8345915.399.7380.79%
60033388.0825.2860098.5216.4180.00%
90038291.7536.8970317.4121.8983.64%
120041003.4655.8276376.2228.6786.27%
150044702.8462.1980234.5834.9579.48%

Compared with v4.0, the Update Non-index performance of TiDB v5.0 has increased by 81%.

Update Non-index

Update Index performance

Threadsv4.0 QPSv4.0 95% latency (ms)v5.0 QPSv5.0 95% latency (ms)QPS improvement
15011736.2117.0115631.3417.0133.19%
30015435.9528.6719957.0622.6929.29%
60018983.2149.2123218.1441.8522.31%
90020855.2974.4626226.7653.8525.76%
120021887.64102.9728505.4169.2930.24%
150023621.15110.6630341.0682.9628.45%

Compared with v4.0, the Update Index performance of TiDB v5.0 has increased by 28%.

Update Index

Read Write performance

Threadsv4.0 QPSv4.0 95% latency (ms)v5.0 QPSv5.0 95% latency (ms)QPS improvement
15059979.9161.0866098.5755.8210.20%
30077118.32102.9784639.4890.789.75%
60090619.52183.21101477.46167.4411.98%
90097085.57267.41109463.46240.0212.75%
1200106521.61331.91115416.05320.178.35%
1500116278.96363.18118807.5411.962.17%

Compared with v4.0, the read-write performance of TiDB v5.0 has increased by 9%.

Read Write