TiDB Cloud Sysbench Performance Test Report for TiDB v8.1.0

This document provides the Sysbench performance test steps and results for a TiDB Dedicated cluster with the TiDB version of v8.1.0. This report can also be used as a reference for the performance of TiDB Self-Hosted v8.1.0 clusters.

Test overview

This test aims at showing the Sysbench performance of TiDB v8.1.0 in the Online Transactional Processing (OLTP) scenario.

Test environment

TiDB cluster

The test is conducted on a TiDB cluster with the following settings:

• Cluster type: TiDB Dedicated

• Cluster version: v8.1.0

• Cloud provider: AWS (us-west-2)

• Cluster configuration:

  Node type	Node size	Node quantity	Node storage
  TiDB	16 vCPU, 32 GiB	2	N/A
  TiKV	16 vCPU, 64 GiB	3	1000 GiB

Parameter configuration

The system variable tidb_session_plan_cache_size controls the maximum number of plans that can be cached. The default value is 100. For each workload, this document conducts tests with tidb_session_plan_cache_size set to 1000:

SET GLOBAL tidb_session_plan_cache_size = 1000;

The TiKV parameter prefill-for-recycle can make log recycling effective immediately after initialization. This document conducts tests based on different workloads with the following prefill-for-recycle configuration:

• For the oltp_point_select workload, use the default value of the prefill-for-recycle parameter:

  raft-engine.prefill-for-recycle = false
  

• For oltp_insert, oltp_read_write, oltp_update_index, and oltp_update_non_index workloads, enable the prefill-for-recycle parameter:

  raft-engine.prefill-for-recycle = true
  

Benchmark executor

The benchmark executor sends SQL queries to the TiDB cluster. In this test, its hardware configuration is as follows:

• Machine type: Amazon EC2 (us-west-2)
• Instance type: c6a.2xlarge
• Sysbench version: sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Test steps

This section introduces how to perform the Sysbench performance test step by step.

1. In the TiDB Cloud console, create a TiDB Dedicated cluster that meets the test environment requirements.

   For more information, see Create a TiDB Dedicated cluster.

2. On the benchmark executor, connect to the newly created cluster and create a database named sbtest.

   To connect to the cluster, see Connect to TiDB Dedicated via Private Endpoint.

   To create the sbtest database, execute the following SQL statement:

   CREATE DATABASE sbtest;
   

3. Load Sysbench data to the sbtest database.

   1. The test in this document is implemented based on sysbench. To install sysbench, see Building and installing from source.

   2. Run the following sysbench prepare command to import 32 tables and 10,000,000 rows to the sbtest database. Replace ${HOST}, ${PORT}, ${THREAD}, and ${PASSWORD} with your actual values.

      sysbench oltp_common \
         --threads=${THREAD} \
         --db-driver=mysql \
         --mysql-db=sbtest \
         --mysql-host=${HOST} \
         --mysql-port=${PORT} \
         --mysql-user=root \
         --mysql-password=${PASSWORD} \
         prepare --tables=32 --table-size=10000000
      

4. Run the following sysbench run command to conduct Sysbench performance tests on different workloads. This document conducts tests on five workloads: oltp_point_select, oltp_read_write, oltp_update_non_index, oltp_update_index, and oltp_insert. For each workload, this document conducts three tests with different values for the ${THREAD} variable. For oltp_point_select and oltp_read_write, the values are 50, 100, and 200. For other workloads, the values are 100, 200, and 400. For each concurrency, the test takes 20 minutes.

   sysbench ${WORKLOAD} run \
     --mysql-host=${HOST} \
     --mysql-port=${PORT} \
     --mysql-user=root \
     --db-driver=mysql \
     --mysql-db=sbtest \
     --threads=${THREAD} \
     --time=1200 \
     --report-interval=10 \
     --tables=32 \
     --table-size=10000000 \
     --mysql-ignore-errors=1062,2013,8028,9007 \
     --auto-inc=false \
     --mysql-password=${PASSWORD}
   

Test results

This section introduces the Sysbench performance of v8.1.0 in the test environment.

Point select performance

The performance on the oltp_point_select workload is as follows:

Read write performance

The performance on the oltp_read_write workload is as follows:

Update non-index performance

The performance on the oltp_update_non_index workload is as follows:

Update index performance

The performance on the oltp_update_index workload is as follows:

Insert performance

The performance on the oltp_insert workload is as follows: