OLTP performance tests

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

1. 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

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"

Test methods

1. 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.
2. Disable the Primary Node Accepts Read Requests feature of PolarDB for MySQL cluster endpoints. 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 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.

4. 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.

5. 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.

6. 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.

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