Skip to content

Latest commit

 

History

History
181 lines (124 loc) · 7.05 KB

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

File metadata and controls

181 lines (124 loc) · 7.05 KB
Raw
title summary
TiDB Cloud Sysbench Performance Test Report for TiDB v8.1.0
Introduce the Sysbench performance test results for a TiDB Cloud Dedicated cluster with the TiDB version of v8.1.0.

TiDB Cloud Sysbench Performance Test Report for TiDB v8.1.0

This document provides the Sysbench performance test steps and results for a TiDB Cloud 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-Managed 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 Cloud 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;

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 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:

Threads TPS 95% latency (ms)
50 32,741 1.96
100 62,545 2.03
200 111,470 2.48

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,232 46.6
100 2,341 51
200 3,240 109

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 14,000 9.39
200 25,215 10.5
400 42,550 12.8

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 11,188 11.7
200 17,805 14.7
400 24,575 23.5

Sysbench update index performance

Insert performance

The performance on the oltp_insert workload is as follows:

Threads TPS 95% latency (ms)
100 18,339 7.3
200 29,387 9.73
400 42,712 14.2

Sysbench insert performance