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
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.
NoteWe 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.
Create a PolarDB-X instance for stress testing.
Create a PolarDB-X instance. For more information about how to create a PolarDB-X instance, see Create a PolarDB-X instance.
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';
Adjust instance parameters.
NoteTo achieve optimal performance in stress tests, adjust some parameters of the compute node of the PolarDB-X instance.
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.
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;
Prepare data for stress testing.
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. Ifsysbench 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.
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.
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.
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.
NoteYou 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_select
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_only
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_write
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_index
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_index
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_only
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_select
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_only
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_write
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_index
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_index
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_only
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_select
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_only
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_write
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_index
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_index
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_only
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_select
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_only
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_write
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_index
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_index
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_only
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
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_only
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
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_index
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_index
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
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 |