PolarDB:Sysbench tests

Background information

Sysbench is an open source and modular tool that can use multiple threads to test the performance of databases across different platforms, including the CPU, memory, thread, and I/O. This topic verifies the performance of PolarDB-X Sysbench tests in various 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 test

  Instance specifications	Number of nodes
  4C32G	2
  4C32G	4
  8C64G	2
  8C64G	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 testing. To prevent performance bottlenecks when you test PolarDB-X instances with high specifications, we recommend that you create an ECS instance with 32 vCPUs and 128 GB of memory.

   Note

   The ECS instance must be deployed in a virtual private cloud (VPC). Record the name and ID of the VPC for subsequent use. You must deploy all database instances that are described in subsequent steps in the VPC.

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

   1. Create a PolarDB-X instance. Select the MySQL 5.7 or MySQL 8.0 database engine based on your business requirements. For information about how to create a PolarDB-X instance, see Create a PolarDB-X instance.

      Note

      Make sure that the PolarDB-X instance and the ECS instance reside in the same VPC.

   2. Create a database (the database name is sbtest in this example) in the instance. For more information, see Create a database.

      CREATE DATABASE sbtest;

3. Modify instance parameters.

   Note

   To achieve optimal performance in stress testing, modify specific parameters of the compute nodes 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 statistical sampling:

      set global RECORD_SQL=false;
      set global MPP_METRIC_LEVEL=0;
      set global ENABLE_CPU_PROFILE=false;
      set global ENABLE_TRANS_LOG=false;

4. Prepare data for stress testing.

   1. Download the stress test tool package benchmarksql.tar.gz and run the following command to decompress it:

      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 synben --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 data for stress testing.

      Create the configuration file sysb.conf and enter the PolarDB-X connection information in the configuration file. The following example shows the content of the configuration file. The parameters in the configuration file are described in this section after the example.

      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'

      The parameters in the configuration file:

      • 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: seconds.

      • 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='dbpartition by hash(id)'  --tables='16' --table-size='10000000' --threads='16' --auto_inc='off' oltp_point_select prepare

      The parameters in the command:

      • 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

   The parameters in the commands:

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

   • mysql-ignore-errors: the MySQL error codes to be ignored, such as some MySQL error codes caused by lock conflicts.

   • range-size: 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