Skip to content

Latest commit

 

History

History
176 lines (121 loc) · 6.9 KB

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

File metadata and controls

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

TiDB Cloud Sysbench Performance Test Report for TiDB v7.5.0

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

Test overview

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

  • Cluster version: v7.5.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

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

Point select performance

The performance on the oltp_point_select workload is as follows:

Threads TPS 95% latency (ms)
50 33,344 1.96
100 64,810 2.03
200 118,651 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 1,181 49.2
100 2,162 54.8
200 3,169 92.4

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 10,567 11.7
200 20,223 13.0
400 34,011 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 8,896 14.7
200 13,718 19.0
400 20,377 26.9

Sysbench update index performance

Insert performance

The performance on the oltp_insert workload is as follows:

Threads TPS 95% latency (ms)
100 15,132 8.58
200 24,756 10.8
400 37,247 16.4

Sysbench insert performance