All Products
Search
Document Center

PolarDB:Sysbench test

Last Updated:Dec 29, 2023

This topic describes the design, process, and results of the Sysbench test for PolarDB-X.

Background information

Sysbench is an open-source and modular tool that can use multiple threads to test the performance of databases, including the CPU, memory, thread, and I/O, across different platforms. The tests described in this topic are performed to test the performance of PolarDB-X instances by using Sysbench in different scenarios.

Test design

  • Amount of test data

    The results of the Sysbench test described in this topic are obtained based on 16 tables. Each table contains 10 million rows of data.

  • Instance specifications for the Sysbench test

    Instance specification

    Number of nodes

    4 vCPU, 32 GB of memory

    2

    4 vCPU, 32 GB of memory

    4

    8 vCPU, 64 GB of memory

    2

    8 vCPU, 64 GB of memory

    4

  • ECS instance type for stress testing

    ecs.g7ne.8xlarge (32 vCPUs, 128 GB of memory)

Procedure

  1. Create an Elastic Compute Service (ECS) instance for stress testing.

    Create an ECS instance that is used to prepare data and perform stress tests. To prevent performance bottlenecks when you test clusters with high specifications, we recommend that you create an ECS instance with 32 vCPUs and 128 GB of memory.

    Note

    We recommend that you deploy the ECS instance in a virtual private cloud (VPC). Record the name and the ID of the VPC for future use. You must deploy all database instances that are described in subsequent steps in this VPC.

  2. Create a PolarDB-X instance for stress testing.

    1. Create a PolarDB-X instance. For more information about how to create a PolarDB-X instance, see Create a PolarDB-X instance.

    2. Create a database to be tested in the PolarDB-X instance. In this example, a database named sbtest is created. For more information about how to create a database, see Create a database.

      CREATE DATABASE sbtest MODE = 'auto';
  3. Adjust instance parameters.

    Note

    To achieve optimal performance in stress tests, adjust some parameters of the compute node of the PolarDB-X instance.

    1. Set the ENABLE_COROUTINE parameter to true and the XPROTO_MAX_DN_CONCURRENT and XPROTO_MAX_DN_WAIT_CONNECTION parameters to 4000. For more information, see Parameter settings.

    2. Connect to the PolarDB-X instance by using a command-line client. Then, execute the following SQL statements in the same session to disable logging and CPU statistic sampling:

      set global RECORD_SQL=false;
      set global MPP_METRIC_LEVEL=0;
      set global ENABLE_CPU_PROFILE=false;
      set global ENABLE_BACKGROUND_STATISTIC_COLLECTION=false;
      set global ENABLE_STATISTIC_FEEDBACK=false;
      set global ENABLE_DEADLOCK_DETECTION=false;
      set global ENABLE_TRANS_LOG=false;
      set global GROUP_PARALLELISM=1;
      set global CONN_POOL_MAX_POOL_SIZE=500;
      set global ENABLE_STATEMENTS_SUMMARY=false;
      set global ENABLE_AUTO_SAVEPOINT=false;
      set global INNODB_ADAPTIVE_HASH_INDEX=off;
      set global TABLE_OPEN_CACHE = 20000;
      set global SHARE_READ_VIEW = false;
      set global CONN_POOL_XPROTO_XPLAN = true;
      set global NEW_SEQ_GROUPING_TIMEOUT=30000;
  4. Prepare data for stress testing.

    1. Run the following commands to download and decompress the stress test tool package benchmarksql.tar.gz:

      tar xzvf sysbench.tar.gz
      cd sysbench/

      Run the following commands to install and compile dependencies:

      yum -y install make automake libtool pkgconfig libaio-devel mysql-devel
      ./autogen.sh
      ./configure
      make -j
      make install

      Run the sysbench --version command. If sysbench 1.1.0 is returned, the stress test tool is installed.

      You can also download open source Sysbench from GitHub. For more information, see sysbench at GitHub.

    2. Configure the stress test.

      Create a configuration file named sysb.conf and add the connection information about the PolarDB-X instance to the configuration file. The following example shows the content of the configuration file:

      mysql-host='{HOST}'
      mysql-port='{PORT}'
      mysql-user='{USER}'
      mysql-password='{PASSWORD}'
      mysql-db='sbtest'
      db-driver='mysql'
      percentile='95'
      histogram='on'
      report-interval='1'
      time='60'
      rand-type='uniform'

      Parameters:

      • percentile: the percentile based on which the response time is sampled.

      • histogram: specifies whether to display the histogram of the response time distribution.

      • report-interval: the interval at which real-time results are displayed. Unit: seconds.

      • time: the duration of the stress test. Unit: second.

      • rand-type: the distribution type that is used to generate a random number.

    3. Import the stress testing data.

      sysbench --config-file='sysb.conf' --create-table-options='single locality="balance_single_table=on"'  --tables='16' --table-size='10000000' --threads='16' --auto_inc='off' oltp_point_select prepare

      Parameters:

      • config-file: the common configuration file.

      • create-table-options: the mode used to create tables.

      • tables: the number of tables.

      • table-size: the amount of data that can be contained in a table.

      • threads: the number of concurrent threads that are used to import data.

      • auto_inc: specifies whether to use auto_increment.

  5. Perform stress testing.

    Run the following commands to perform the test in the six scenarios defined by Sysbench. We recommend that you increase the duration of the first stress test.

    Note

    You can configure the following parameters in the commands:

    • db-ps-mode: specifies whether to enable the prepare mode.

    • mysql-ignore-errors: specifies that some MySQL error codes caused by lock conflicts are ignored.

    • range-size: specifies the range based on which range queries are performed.

    • oltp_point_select:

      sysbench --config-file='sysb.conf' --db-ps-mode='disable'  --mysql-ignore-errors='all'  --tables='16' --table-size='10000000' --threads={Number of concurrent threads} oltp_point_select run
    • oltp_read_only:

      sysbench --config-file='sysb.conf' --db-ps-mode='disable' --mysql-ignore-errors='all' --tables='16' --table-size='10000000' --range-size=5 --threads={Number of concurrent threads} oltp_read_only run
    • oltp_read_write:

      sysbench --config-file='sysb.conf' --db-ps-mode='disable' --mysql-ignore-errors='all' --tables='16' --table-size='10000000' --range-size=5 --threads={Number of concurrent threads} oltp_read_write run
    • oltp_update_index:

      sysbench --config-file='sysb.conf' --db-ps-mode='disable'  --mysql-ignore-errors='all' --tables='16' --table-size='10000000' --threads={Number of concurrent threads} oltp_update_index run
    • oltp_update_non_index:

      sysbench --config-file='sysb.conf' --db-ps-mode='disable'  --mysql-ignore-errors='all' --tables='16' --table-size='10000000' --threads={Number of concurrent threads} oltp_update_non_index run
    • oltp_write_only:

      sysbench --config-file='sysb.conf' --db-ps-mode='disable'  --mysql-ignore-errors='all'  --tables='16' --table-size='10000000' --threads={Number of concurrent threads} oltp_write_only run

Test results

5.4.15-16704996

In the following test results, QPS are used to measure the database performance.

oltp_point_select456789

Instance specification

Number of concurrent threads

QPS

4C32G * 2

600

65611.43

4C32G * 4

1000

122036.76

8C64G * 2

1000

112588.98

8C64G * 4

2000

242439.92

oltp_read_only456789

Instance specification

Number of concurrent threads

QPS

4C32G * 2

200

72321.57

4C32G * 4

200

123499.89

8C64G * 2

200

112588.98

8C64G * 4

1000

235696.94

oltp_read_write456789

Instance specification

Number of concurrent threads

QPS

4C32G * 2

100

45417.06

4C32G * 4

200

84128.1

8C64G * 2

300

80474.9

8C64G * 4

600

143958.78

oltp_update_index456789

Instance specification

Number of concurrent threads

QPS

4C32G * 2

300

31744.92

4C32G * 4

300

64425.82

8C64G * 2

600

48569.83

8C64G * 4

1200

92562.98

oltp_update_non_index456789

Instance specification

Number of concurrent threads

QPS

4C32G * 2

300

42117.02

4C32G * 4

300

54015.52

8C64G * 2

400

45966.4

8C64G * 4

800

113690.88

oltp_write_only456789

Instance specification

Number of concurrent threads

QPS

4C32G * 2

200

34261.48

4C32G * 4

200

59498.09

8C64G * 2

300

52285.58

8C64G * 4

400

89546.16

5.4.15-16715927

In the following test results, QPS are used to measure the database performance.

oltp_point_select456789

Instance specification

Number of concurrent threads

QPS

4C32G * 2

600

71399.03

4C32G * 4

1000

122491.05

8C64G * 2

1000

121634.04

8C64G * 4

2000

235068.85

oltp_read_only456789

Instance specification

Number of concurrent threads

QPS

4C32G * 2

200

62859.64

4C32G * 4

200

117161.64

8C64G * 2

200

123697.56

8C64G * 4

1000

240998.7

oltp_read_write456789

Instance specification

Number of concurrent threads

QPS

4C32G * 2

100

43023.64

4C32G * 4

200

84318.95

8C64G * 2

300

86702.66

8C64G * 4

600

143754.44

oltp_update_index456789

Instance specification

Number of concurrent threads

QPS

4C32G * 2

300

31837.19

4C32G * 4

300

64450.22

8C64G * 2

600

50669.57

8C64G * 4

1200

92942.66

oltp_update_non_index456789

Instance specification

Number of concurrent threads

QPS

4C32G * 2

300

31437.64

4C32G * 4

300

70815.01

8C64G * 2

400

52093.29

8C64G * 4

800

123643.37

oltp_write_only456789

Instance specification

Number of concurrent threads

QPS

4C32G * 2

200

34026.33

4C32G * 4

200

58624.59

8C64G * 2

300

53741.5

8C64G * 4

400

86151.32

5.4.16-16717637

In the following test results, QPS are used to measure the database performance.

oltp_point_select456789

Instance specification

Number of concurrent threads

QPS

4C32G * 2

600

74650.4

4C32G * 4

1000

106492.52

8C64G * 2

1000

127032.44

8C64G * 4

2000

217089.44

oltp_read_only456789

Instance specification

Number of concurrent threads

QPS

4C32G * 2

200

69994.6

4C32G * 4

200

116932.02

8C64G * 2

200

112704.98

8C64G * 4

1000

238991.34

oltp_read_write456789

Instance specification

Number of concurrent threads

QPS

4C32G * 2

100

44818.53

4C32G * 4

200

84642.45

8C64G * 2

300

81493.08

8C64G * 4

600

140726.78

oltp_update_index456789

Instance specification

Number of concurrent threads

QPS

4C32G * 2

300

31393.88

4C32G * 4

300

63485.13

8C64G * 2

600

47620.55

8C64G * 4

1200

92960.04

oltp_update_non_index456789

Instance specification

Number of concurrent threads

QPS

4C32G * 2

300

45550.3

4C32G * 4

300

54273.97

8C64G * 2

400

62637.64

8C64G * 4

800

121596.45

oltp_write_only456789

Instance specification

Number of concurrent threads

QPS

4C32G * 2

200

33277.91

4C32G * 4

200

57892.17

8C64G * 2

300

51673.2

8C64G * 4

400

86455.57

5.4.17-16835173

In the following test results, QPS are used to measure the database performance.

oltp_point_select456789

Instance specification

Number of concurrent threads

QPS

4 vCPUs, 32 GB of memory × 2

600

86033.36

4 vCPUs, 32 GB of memory × 4

1000

166897.13

8 vCPUs, 64 GB of memory × 2

1000

154769.16

8 vCPUs, 64 GB of memory × 4

2000

305428.85

oltp_read_only456789

Instance specification

Number of concurrent threads

QPS

4 vCPUs, 32 GB of memory × 2

200

94950.84

4 vCPUs, 32 GB of memory × 4

200

157773.44

8 vCPUs, 64 GB of memory × 2

200

162426.9

8 vCPUs, 64 GB of memory × 4

1000

303120.64

oltp_read_write456789

Instance specification

Number of concurrent threads

QPS

4 vCPUs, 32 GB of memory × 2

100

59371.01

4 vCPUs, 32 GB of memory × 4

200

106015.85

8 vCPUs, 64 GB of memory × 2

300

108037.57

8 vCPUs, 64 GB of memory × 4

600

185936.25

oltp_update_index456789

Instance specification

Number of concurrent threads

QPS

4 vCPUs, 32 GB of memory × 2

300

32958.09

4 vCPUs, 32 GB of memory × 4

300

62640.72

8 vCPUs, 64 GB of memory × 2

600

52925.62

8 vCPUs, 64 GB of memory × 4

1200

100219.02

oltp_update_non_index456789

Instance specification

Number of concurrent threads

QPS

4 vCPUs, 32 GB of memory × 2

300

44617.7

4 vCPUs, 32 GB of memory × 4

300

91142.8

8 vCPUs, 64 GB of memory × 2

400

78510.47

8 vCPUs, 64 GB of memory × 4

800

143923.67

oltp_write_only456789

Instance specification

Number of concurrent threads

QPS

4 vCPUs, 32 GB of memory × 2

200

36829.85

4 vCPUs, 32 GB of memory × 4

200

60741.75

8 vCPUs, 64 GB of memory × 2

300

63400.16

8 vCPUs, 64 GB of memory × 4

400

100897.1

5.4.18-16977056

In the following test results, QPS are used to measure the database performance.

oltp_point_select

DFDF.png

Instance specification

Number of concurrent threads

QPS

4 vCPUs, 32 GB of memory × 2

600

86076.19

4 vCPUs, 32 GB of memory × 4

1000

171788.14

8 vCPUs, 64 GB of memory × 2

1000

153816.64

8 vCPUs, 64 GB of memory × 4

2000

310194.34

oltp_read_onlyQWQW.png

Instance specification

Number of concurrent threads

QPS

4 vCPUs, 32 GB of memory × 2

200

97286.57

4 vCPUs, 32 GB of memory × 4

200

178936.19

8 vCPUs, 64 GB of memory × 2

200

159390.63

8 vCPUs, 64 GB of memory × 4

1000

307663.98

oltp_read_write

ZXZX.png

Instance specification

Number of concurrent threads

QPS

4 vCPUs, 32 GB of memory × 2

100

56500.61

4 vCPUs, 32 GB of memory × 4

200

107762.36

8 vCPUs, 64 GB of memory × 2

300

119275.8

8 vCPUs, 64 GB of memory × 4

600

170881.36

oltp_update_indexERER.png

Instance specification

Number of concurrent threads

QPS

4 vCPUs, 32 GB of memory × 2

300

30654.23

4 vCPUs, 32 GB of memory × 4

300

64730.37

8 vCPUs, 64 GB of memory × 2

600

65536.57

8 vCPUs, 64 GB of memory × 4

1200

95629.61

oltp_update_non_indexTYTY.png

Instance specification

Number of concurrent threads

QPS

4 vCPUs, 32 GB of memory × 2

300

44992.24

4 vCPUs, 32 GB of memory × 4

300

97440.82

8 vCPUs, 64 GB of memory × 2

400

99340.7

8 vCPUs, 64 GB of memory × 4

800

131181.64

oltp_write_only

GHGH.png

Instance specification

Number of concurrent threads

QPS

4 vCPUs, 32 GB of memory × 2

200

35168.48

4 vCPUs, 32 GB of memory × 4

200

64152.25

8 vCPUs, 64 GB of memory × 2

300

74929.45

8 vCPUs, 64 GB of memory × 4

400

93430.79