OLTP performance test

Test tool

Sysbench is a modular, cross-platform, and multi-threaded benchmark tool that can be used to evaluate the performance of a heavily loaded database system based on core metrics. Sysbench allows you to test the performance of a database without the need to configure complicated benchmark settings or install the database engine.

Environment

• The Elastic Compute Service (ECS) instance and the PolarDB for MySQL cluster used in the test are deployed in the same zone of the same region.

• The network type of the ECS instance and the PolarDB for MySQL cluster is Virtual Private Cloud (VPC).

  Note

  The ECS instance and the PolarDB for MySQL cluster must belong to the same VPC.

• The following items list information about the PolarDB for MySQL cluster 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 the endpoints of a PolarDB for MySQL cluster, see Manage the endpoints of a cluster.

  • 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 items list 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.

Precautions

• Before the test, you can specify the warm-up time by adding the --warmup=xxx option to ensure that the test performance is stable.

• When you compare the performance of different clusters, you can ping the database IP address to check the network latency from the ECS to the database. This helps you avoid high network latency when a cross-zone connection is used, which affects the database performance in low-concurrency scenarios.

• During a high concurrency test, the error message "can't create more than max_prepared_stmt_count statements" may be returned and the test may fail. To solve this issue, you can specify the max_prepared_stmt_count parameter to allow more prepared statements. For more information, see Configure cluster and node parameters.

Testing scenarios

• 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 consist of one primary node and one read-only node. The tests also evaluate the read-only performance of the clusters that consist of 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

1. Run the following commands on the ECS instance 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

2. 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"

Procedure

1. Obtain the cluster endpoint and port number of the PolarDB for MySQL cluster. For more information, see Configure PolarProxy.

2. Set Primary Node Accepts Read Requests of the PolarDB for MySQL cluster endpoint to Yes. For more information, see Configure PolarProxy.

3. 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 of 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 PolarDB for MySQL cluster.

4. Use Sysbench to test the read-only performance of the PolarDB for MySQL Cluster Edition 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=testdb --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=testdb --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=testdb --table_size=25000 --tables=250 --events=0 --time=600   --threads=XXX --percentile=95 --range_selects=0 oltp_read_only cleanup
   ## Clear the data.

5. Use Sysbench to test the write performance of the PolarDB for MySQL Cluster Edition 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=testdb --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=testdb --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=testdb --table_size=25000 --tables=250 --events=0 --time=600   --threads=XXX --percentile=95  oltp_write_only cleanup
   ## Clear the data.

6. Use Sysbench to test the read and write performance of the PolarDB for MySQL Cluster Edition 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=testdb --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=testdb --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=testdb --table_size=250000 --tables=25 --events=0 --time=600   --threads=XXX --percentile=95  oltp_read_write cleanup
   ## Clear the data.

7. Use Sysbench to test the read-only performance of the PolarDB for MySQL Cluster Edition 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=testdb --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=testdb --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=testdb --table_size=25000 --tables=250 --events=0 --time=600   --threads=XXX --percentile=95  oltp_read_only cleanup
   ## Clear the data.