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 condition

  • 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
    Node specification Number of nodes
    2c8g 2
    4c16g 2
    4c16g 4
    8c32g 2
    8c32g 4
    16c64g 2
    16c64g 4
  • ECS instance specification for the stress test

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

Procedure

  1. Create an ECS instance for the stress test

    Create an Elastic Compute Service (ECS) instance that is used to prepare data and perform stress tests. To avoid performance bottlenecks when you test clusters with high specifications, we recommend that you create an ECS instance with 32 CPU cores 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 the stress test
    1. PolarDB-X instance for the stress test. For more information about how to create a PolarDB-X instance, see Create instances.
    2. Create a database to be tested in the instance. In this example, a database named sbtest is created. For more information about how to create a database, see Create a database.
  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. Change the value of the ENABLE_COROUTINE parameter to true and the value of the XPROTO_MAX_DN_CONCURRENT parameter to 4000. For more information, see Parameter settings.
    2. Connect to the PolarDB-X instance by using the command-line client. Then, execute the following SQL statements in the same session to disable logging and CPU statistic sampling:
      set ENABLE_SET_GLOBAL = true;
      set global RECORD_SQL = false;
      set global MPP_METRIC_LEVEL = 0;
      set global ENABLE_CPU_PROFILE = false;
  4. Prepare data for the stress test
    1. Run the following command to download and decompress the stress test tool package sysbench.tar.gz:
      tar xzvf sysbench.tar.gz
      cd sysbench/

      Run the following command 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.

    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'

      You can configure the following 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: second.
      • time: the duration of the stress test. Unit: second.
      • rand-type: the distribution type that is used to generate random number.
    3. Import the stress test data
      sysbench --config-file='sysb.conf' --create-table-options='dbpartition by hash(`id`) tbpartition by hash(id) tbpartitions 2'  --tables='16' --threads='16' --table-size='10000000' oltp_point_select prepare

      You can configure the following parameters in the preceding command:

      • config-file: specifies the common configuration file.
      • create-table-options: specifies the mode used to create tables.
      • tables: specifies the number of tables.
      • table-size: specifies the amount of data that can be contained in a table.
      • threads: specifies the number of concurrent threads that are used to import data.
      • auto_inc: specifies whether to use auto_increment.
  5. Perform the stress test

    Run the following commands to perform the test in the six scenarios defined by Sysbench.

    Note You can configure the following parameters in the commands:
    • db-ps-mode: specifies whether to enable the prepare mode.
    • skip-trx: specifies whether to enable transactions. The value of on indicates that transactions are not enabled. The value of off indicates that transactions are enabled.
    • 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.
    • point_select:
      sysbench --config-file='sysb.conf' --db-ps-mode='disable' --skip-trx='on' --mysql-ignore-errors='all'  --tables='16' --table-size='10000000' --threads={Number of concurrent threads} oltp_point_select run
    • read_only:
      sysbench --config-file='sysb.conf' --db-ps-mode='disable' --skip-trx='on' --mysql-ignore-errors='all' --tables='16' --table-size='10000000' --range-size=5 --threads={Number of concurrent threads} oltp_read_only run
    • read_write:
      sysbench --config-file='sysb.conf' --db-ps-mode='disable' --skip-trx='on' --mysql-ignore-errors='all' --tables='16' --table-size='10000000' --range-size=5 --threads={Number of concurrent threads} oltp_read_write run
    • update_index:
      sysbench --config-file='sysb.conf' --db-ps-mode='disable' --skip-trx='on' --mysql-ignore-errors='all' --tables='16' --table-size='10000000' --threads={Number of concurrent threads} oltp_update_index run
    • update_non_index:
      sysbench --config-file='sysb.conf' --db-ps-mode='disable' --skip-trx='on' --mysql-ignore-errors='all' --tables='16' --table-size='10000000' --threads={Number of concurrent threads} oltp_update_non_index run
    • write_only:
      sysbench --config-file='sysb.conf' --db-ps-mode='disable' --skip-trx='on' --mysql-ignore-errors='all'  --tables='16' --table-size='10000000' --threads={Number of concurrent threads} oltp_write_only run

Test results

In the following test results, queries per second (QPS) are used to measure the database performance.

point_select456789
Instance specification Concurrent threads QPS
2c8g * 2 512 42753.61
4c16g * 2 1024 86413.17
4c16g * 4 1024 170834.72
8c32g * 2 1024 161473.31
8c32g * 4 2048 304003.49
16c64g * 2 2048 227869.71
16c64g * 4 3096 403384.56
read_only456789
Instance specification Concurrent threads QPS
2c8g * 2 64 13303.04
4c16g * 2 256 28871.19
4c16g * 4 256 58643.49
8c32g * 2 256 72772.71
8c32g * 4 512 106942.17
16c64g * 2 256 113922.87
16c64g * 4 512 150396.33
read_write456789
Instance specification Concurrent threads QPS
2c8g * 2 64 12687.14
4c16g * 2 128 28658.71
4c16g * 4 256 52726.33
8c32g * 2 128 67373.28
8c32g * 4 128 101273.77
16c64g * 2 256 77931.34
16c64g * 4 512 123463.22
update_index456789
Instance specification Concurrent threads QPS
2c8g * 2 128 14164.71
4c16g * 2 256 33037.09
4c16g * 4 256 61381.59
8c32g * 2 256 54728.10
8c32g * 4 256 97620.78
16c64g * 2 512 73120.56
16c64g * 4 512 140905.00
update_non_index456789
Instance specification Concurrent threads QPS
2c8g * 2 128 14897.04
4c16g * 2 256 35605.90
4c16g * 4 256 64814.66
8c32g * 2 256 59162.14
8c32g * 4 256 100467.07
16c64g * 2 512 78192.59
16c64g * 4 512 142244.89
write_only456789
Instance specification Concurrent threads QPS
2c8g * 2 256 14763.85
4c16g * 2 256 34776.46
4c16g * 4 256 64548.93
8c32g * 2 256 63807.99
8c32g * 4 256 102137.14
16c64g * 2 256 83583.16
16c64g * 4 512 144374.46