This topic describes how to use SysBench to test the online transaction processing (OLTP) performance of a PolarDB for MySQL cluster.

Test tool

SysBench is a modular, cross-platform, and multi-threaded benchmark tool that you can use to evaluate the core parameters of a system that runs a heavily loaded database. SysBench provides a quick method to test the performance of a database. You do not need to specify complex benchmark settings or install the database engine to test the database performance.

Test environment

  • The Elastic Computing Service (ECS) instance and the PolarDB for MySQL cluster that you use for testing must be deployed in the same zone of a region. In this test, the ECS instance and the PolarDB for MySQL cluster are deployed in Zone I of the China (Hangzhou) region.
  • The VPC network type is selected for the ECS instance and the PolarDB for MySQL cluster.
    Note Make sure that the ECS instance and the PolarDB for MySQL cluster are connected to the same virtual private cloud (VPC).
  • In this test, the PolarDB for MySQL cluster uses the following specifications:
    • The node type is polar.mysql.x4.large that provides 4 CPU cores and 16 GB memory.
    • The read-only, write-only, or read and write performance is tested by using a two-node cluster that consists of one primary node and one read-only node. The performance of the multi-node cluster is tested by using the two- to nine-node cluster that consists of one primary node and one to eight read-only nodes.
    • Use PolarDB for MySQL cluster endpoints to connect to the cluster. For information about how to view the cluster endpoints, see View or apply for an endpoint.
  • In this test, the ECS instance uses the following specifications:
    • The instance type is ecs.c5.4xlarge.
    • The image used for the instance is 64-bit CentOS 7.0.

Test scenario

Test the online transaction processing (OLTP) performance of the PolarDB for MySQL cluster that has different specifications. The tests evaluate the read-only performance, write-only performance, and read and write performance of the cluster that contains only one read-only node. The tests also evaluate the read-only performance of the cluster that contains multiple read-only nodes.

The following performance metrics are used to measure the performance:

  • Transactions per second (TPS): the number of transactions that are performed per second in the PolarDB for MySQL cluster. Only the number of committed transactions is counted.
  • Queries per second (QPS): the number of SQL statements that are executed per second in the PolarDB for MySQL cluster, including INSERT, SELECT, UPDATE, and DELETE statements.

Install SysBench

  1. Run the following commands on your 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 the SysBench software package from GitHub.
    
    cd sysbench
    ##Open the SysBench directory.
    
    git checkout 1.0.18
    ##Switch to SysBench 1.0.18.
    
    ./autogen.sh
    ##Run the autogen.sh script.
    
    ./configure --prefix=/usr --mandir=/usr/share/man
    
    make
    ##Compile SysBench.
    
    make install
  2. Run the following commands to configure the SysBench client. Based on the configuration, the client can use all the available CPU cores to process data. This minimizes the number of cross-core context switches. By default, two CPU cores are used.
    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 for data processing. Modify the command based on your business requirements. For example, use ff if your ECS instance has eight 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 procedure

  1. Obtain the endpoint and the port number of your PolarDB for MySQL cluster. For more information, see View or apply for an endpoint.
  2. Disable the Offload Reads from Primary Node feature of PolarDB for MySQL cluster endpoints. For more information, see Modify a cluster endpoint.
  3. Run the following command on your ECS instance 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 four XXXs in the preceding command and the subsequent commands with the cluster endpoint, port number, username, and 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 of the username.
  4. Use SysBench to test the read-only performance of the two-node PolarDB for MySQL cluster. The entire process lasts for 10 minutes.
    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 generated data.
  5. Use SysBench to test the write-only performance of the two-node PolarDB for MySQL cluster. The entire process lasts for 10 minutes.
    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 generated data.
  6. Use SysBench to test the read and write performance of two-node PolarDB for MySQL cluster. The entire process lasts for 10 minutes.
    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 generated data.
  7. Use SysBench to test the read-only performance of 2-node, 3-node, 4-node, 5-node, 6-node, 7-node, 8-node, and 9-node PolarDB for MySQL clusters in sequence.
    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_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=250000 --tables=25 --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=250000 --tables=25 --events=0 --time=600   --threads=XXX --percentile=95  oltp_read_only cleanup
    ##Clear the generated data.

What to do next