v6.5-performance-benchmarking-with-sysbench.md

title	summary
TiDB Cloud Sysbench Performance Test Report for TiDB v6.5.6	Introduce the Sysbench performance test results for a TiDB Cloud Dedicated cluster with the TiDB version of v6.5.6.

TiDB Cloud Sysbench Performance Test Report for TiDB v6.5.6

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

Test overview

This test aims at showing the Sysbench performance of TiDB v6.5.6 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 Cloud Dedicated

• Cluster version: v6.5.6

• 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

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 Cloud Dedicated cluster that meets the test environment requirements.

   For more information, see Create a TiDB Cloud 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 Cloud 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 the ${THREAD} value of 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 v6.5.6 in the test environment.

Point select performance

The performance on the oltp_point_select workload is as follows:

Threads	TPS	95% latency (ms)
50	34125	2.03
100	64987	2.07
200	121656	2.14

Read write performance

The performance on the oltp_read_write workload is as follows:

Threads	TPS	95% latency (ms)
50	1232	46.6
100	2266	51.9
200	3578	81.5

Update non-index performance

The performance on the oltp_update_non_index workload is as follows:

Threads	TPS	95% latency (ms)
100	11016	11.0
200	20640	12.1
400	36830	13.5

Update index performance

The performance on the oltp_update_index workload is as follows:

Threads	TPS	95% latency (ms)
100	9270	14.0
200	14466	18.0
400	22194	24.8

Insert performance

The performance on the oltp_insert workload is as follows:

Threads	TPS	95% latency (ms)
100	16008	8.13
200	27143	10.1
400	40884	15.0