This topic describes how to use SysBench to test the online transaction processing
(OLTP) performance of PolarDB for MySQL clusters. You can follow the instructions in this topic to perform tests to view
the performance of databases.
Test tool
SysBench is a modular, cross-platform, and multi-threaded benchmark tool. You can
use it to evaluate the performance of core parameters of a system that runs a database
of heavy loads. SysBench allows you to quickly test the performance of a database
without complex benchmark settings, even if you do not install the database.
Test environment
- The Elastic Compute Service (ECS) instances and PolarDB for MySQL clusters are deployed in the same zone of the same region.
- The network type is virtual private cloud (VPC).
Note The ECS instances and PolarDB for MySQL clusters are in the same VPC.
- The following list describes the information about the PolarDB for MySQL clusters that are used in the test:
- Clusters with two nodes (one primary node and one read-only node) are used to test
the read-only, write-only, and read and write performance. To test the performance
of multiple read-only nodes, clusters that contain one primary node and one to eight
read-only nodes are used in sequence.
- The cluster endpoints are used as the connection strings. For more information about
how to view cluster endpoints of a PolarDB for MySQL cluster, see Apply for a cluster endpoint or a primary endpoint.
- To fully utilize cluster resources, specifications of the PolarProxy used in this
whitepaper have been upgraded to Standard × 4. For more information about upgrading PolarProxy specifications, see Upgrade the specifications of PolarProxy.
- The following list describes the information about the ECS instances that are used
in the test:
- The instance type is ecs.c5.4xlarge.
- The image of the instances is 64-bit CentOS 7.0.
- The number of ECS instances varies based on the specifications of the PolarDB for
MySQL clusters. For example, you can use one ECS instance for a cluster that has 8
CPU cores, and two ECS instances for a cluster that has 16 CPU cores.
Testing scenario
- Test the OLTP performance of Dedicated clusters that have different specifications. The tests evaluate the read-only, write-only,
and read and write performance of the clusters that have one primary node and one
read-only node. The tests also evaluate the read-only performance of the clusters
that have one primary node and multiple read-only nodes.
- Test the OLTP performance of General-purpose clusters that have different specifications. The tests evaluate the read-only, write-only,
and read and write performance of the clusters that have one primary node and one
read-only node.
Metrics
- Transactions per second (TPS): the number of transactions that are performed per second
in the database. Only committed transactions are counted.
- Queries per second (QPS): the number of SQL statements that are executed per second
in the database, including the INSERT, SELECT, UPDATE, and DELETE statements.
Install SysBench
- Run the following commands on the ECS instances to install SysBench:
yum install gcc gcc-c++ autoconf automake make libtool bzr mysql-devel git mysql
git clone https://github.com/akopytov/sysbench.git
##Download SysBench from Git.
cd sysbench
##Change the current working directory to sysbench.
git checkout 1.0.18
##Switch to SysBench 1.0.18.
./autogen.sh
##Run autogen.sh.
./configure --prefix=/usr --mandir=/usr/share/man
make
##Compile SysBench.
make install
- Run the following command to configure the SysBench client. This way, the kernel can
use all CPU cores to process packets, instead of the default setting that uses two
cores. This minimizes the number of times for switching across CPU cores.
sudo sh -c 'for x in /sys/class/net/eth0/queues/rx-*; do echo ffffffff>$x/rps_cpus; done'
Note ffffffff
indicates that 32 cores are used. Specify the parameter based on the actual configuration.
For example, enter ff
if your ECS instance has eight CPU cores.
sudo sh -c "echo 32768 > /proc/sys/net/core/rps_sock_flow_entries"
sudo sh -c "echo 4096 > /sys/class/net/eth0/queues/rx-0/rps_flow_cnt"
sudo sh -c "echo 4096 > /sys/class/net/eth0/queues/rx-1/rps_flow_cnt"
Test methods
Note PolarDB for MySQL Cluster clusters are used in the following example.
- Obtain the cluster endpoint and port number of the PolarDB for MySQL cluster. For more information, see Apply for a cluster endpoint or a primary endpoint.
- Disable the Primary Node Accepts Read Requests feature of PolarDB for MySQL cluster endpoints. For more information, see Configure PolarProxy.
- Run the following command on the ECS instances to create a database that is named
testdb
in the PolarDB for MySQL cluster: mysql -h XXX -P XXX -u XXX -p XXX -e 'create database testdb'
Note Replace the
XXX
parts in this command and the following commands with the cluster endpoint, port
number, username, and the password of the
PolarDB for MySQL cluster.
Parameter |
Description |
-h |
The cluster endpoint of the PolarDB for MySQL cluster.
|
-P |
The port number that is used to connect to the PolarDB for MySQL cluster.
|
-u |
The username of the PolarDB for MySQL cluster.
|
-p |
The password that is used to log on to the ClickHouse cluster. |
- Use SysBench to test the read-only performance of the PolarDB for MySQL cluster that contains one primary node and one read-only node. It requires 10 minutes
to perform the test.
sysbench --db-driver=mysql --mysql-host=XXX --mysql-port=XXX --mysql-user=XXX --mysql-password=XXX --mysql-db=sbtest --table_size=25000 --tables=250 --events=0 --time=600 oltp_read_only prepare
##Prepare test data.
sysbench --db-driver=mysql --mysql-host=XXX --mysql-port=XXX --mysql-user=XXX --mysql-password=XXX --mysql-db=sbtest --table_size=25000 --tables=250 --events=0 --time=600 --threads=XXX --percentile=95 --range_selects=0 --skip-trx=1 --report-interval=1 oltp_read_only run
##Run workloads.
sysbench --db-driver=mysql --mysql-host=XXX --mysql-port=XXX --mysql-user=XXX --mysql-password=XXX --mysql-db=sbtest --table_size=25000 --tables=250 --events=0 --time=600 --threads=XXX --percentile=95 --range_selects=0 oltp_read_only cleanup
##Clear the data.
- Use SysBench to test the write performance of the PolarDB for MySQL cluster that contains one primary node and one read-only node. It requires 10 minutes
to perform the test.
sysbench --db-driver=mysql --mysql-host=XXX --mysql-port=XXX --mysql-user=XXX --mysql-password=XXX --mysql-db=sbtest --table_size=25000 --tables=250 --events=0 --time=600 oltp_write_only prepare
##Prepare test data.
sysbench --db-driver=mysql --mysql-host=XXX --mysql-port=XXX --mysql-user=XXX --mysql-password=XXX --mysql-db=sbtest --table_size=25000 --tables=250 --events=0 --time=600 --threads=XXX --percentile=95 --report-interval=1 oltp_write_only run
##Run workloads.
sysbench --db-driver=mysql --mysql-host=XXX --mysql-port=XXX --mysql-user=XXX --mysql-password=XXX --mysql-db=sbtest --table_size=25000 --tables=250 --events=0 --time=600 --threads=XXX --percentile=95 oltp_write_only cleanup
##Clear the data.
- Use SysBench to test the read and write performance of the PolarDB for MySQL cluster that contains one primary node and one read-only node. It requires 10 minutes
to perform the test.
sysbench --db-driver=mysql --mysql-host=XXX --mysql-port=XXX --mysql-user=XXX --mysql-password=XXX --mysql-db=sbtest --table_size=250000 --tables=25 --events=0 --time=600 oltp_read_write prepare
##Prepare test data.
sysbench --db-driver=mysql --mysql-host=XXX --mysql-port=XXX --mysql-user=XXX --mysql-password=XXX --mysql-db=sbtest --table_size=250000 --tables=25 --events=0 --time=600 --threads=XXX --percentile=95 --report-interval=1 oltp_read_write run
##Run workloads.
sysbench --db-driver=mysql --mysql-host=XXX --mysql-port=XXX --mysql-user=XXX --mysql-password=XXX --mysql-db=sbtest --table_size=250000 --tables=25 --events=0 --time=600 --threads=XXX --percentile=95 oltp_read_write cleanup
##Clear the data.
- Use SysBench to test the read-only performance of the PolarDB for MySQL clusters that contain one primary node and multiple read-only nodes. The clusters
that include one primary node and one to eight read-only nodes are tested in sequence.
sysbench --db-driver=mysql --mysql-host=XXX --mysql-port=XXX --mysql-user=XXX --mysql-password=XXX --mysql-db=sbtest --table_size=25000 --tables=250 --events=0 --time=600 oltp_read_only prepare
##Prepare test data.
sysbench --db-driver=mysql --mysql-host=XXX --mysql-port=XXX --mysql-user=XXX --mysql-password=XXX --mysql-db=sbtest --table_size=25000 --tables=250 --events=0 --time=600 --threads=XXX --percentile=95 --report-interval=1 oltp_read_only --db-ps-mode=disable --skip-trx=1 run
##Run workloads.
sysbench --db-driver=mysql --mysql-host=XXX --mysql-port=XXX --mysql-user=XXX --mysql-password=XXX --mysql-db=sbtest --table_size=25000 --tables=250 --events=0 --time=600 --threads=XXX --percentile=95 oltp_read_only cleanup
##Clear the data.