Skip to content

Latest commit

 

History

History
176 lines (121 loc) · 6.87 KB

v7.1-performance-benchmarking-with-sysbench.md

File metadata and controls

176 lines (121 loc) · 6.87 KB
Raw
title summary aliases
TiDB Cloud Sysbench Performance Test Report for TiDB v7.1.3
Introduce the Sysbench performance test results for a TiDB Cloud Dedicated cluster with the TiDB version of v7.1.3.
/tidbcloud/v7.1.0-performance-benchmarking-with-sysbench

TiDB Cloud Sysbench Performance Test Report for TiDB v7.1.3

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

Test overview

This test aims at showing the Sysbench performance of TiDB v7.1.3 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: v7.1.3

  • 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

Note:

For TiDB Cloud, to modify the TiKV parameters of your cluster, you can contact PingCAP Support for help.

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 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 v7.1.3 in the test environment.

Point select performance

The performance on the oltp_point_select workload is as follows:

Threads TPS 95% latency (ms)
50 35309 1.93
100 64853 2.00
200 118462 2.22

Sysbench point select performance

Read write performance

The performance on the oltp_read_write workload is as follows:

Threads TPS 95% latency (ms)
50 1218 48.3
100 2235 53.9
200 3380 87.6

Sysbench read write performance

Update non-index performance

The performance on the oltp_update_non_index workload is as follows:

Threads TPS 95% latency (ms)
100 10928 11.7
200 19985 12.8
400 35621 14.7

Sysbench update non-index performance

Update index performance

The performance on the oltp_update_index workload is as follows:

Threads TPS 95% latency (ms)
100 8854 14.7
200 14414 18.6
400 21997 25.3

Sysbench update index performance

Insert performance

The performance on the oltp_insert workload is as follows:

Threads TPS 95% latency (ms)
100 15575 8.13
200 25078 11.0
400 38436 15.6

Sysbench insert performance