PolarDB:Sysbench test

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. 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 a Sysbench test

  Node 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 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 PolarDB-X instances.
      Note Make sure that the PolarDB-X instance and the ECS instance are in the same VPC.
   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 DATABASE.

      CREATE DATABASE sbtest;

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 GROUP_PARALLELISM = 1;
      set global SHARE_READ_VIEW = false;
      set global CONN_POOL_XPROTO_XPLAN = false;
      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: 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

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