This topic describes how to test the online transaction processing (OLTP) performance of an ApsaraDB PolarDB MySQL cluster by using SysBench.

Test tool

SysBench is a modular, cross-platform, and multi-threaded benchmark tool used for evaluating the core operating parameters of a system that runs a heavily loaded database. SysBench is designed to test the performance of a database without the need to set complicated benchmark settings or install the database engine.

Test environment

  • An Elastic Compute Service (ECS) instance and ApsaraDB PolarDB MySQL clusters are used for testing, and they must be deployed in the same region and zone. In this test, they are both deployed in Zone I of the China (Hangzhou) region.
  • The VPC network type is selected for both the ECS instance and the ApsaraDB PolarDB MySQL clusters.
    Note Make sure that the ECS instance and the ApsaraDB PolarDB MySQL clusters are connected to the same VPC network.
  • The ApsaraDB PolarDB MySQL clusters for testing are as follows:
    • The node specification is polar.mysql.x4.large (4-core 16 GB).
    • The read-only, write-only, or read-write performance is tested by using a two-node cluster, which contains one primary node and one read-only node. The performance of multi-node clusters is tested by using two- to nine-node clusters, which contain one primary node and one to eight read-only nodes.
    • Use cluster endpoints to connect to the clusters. For information about how to query the cluster endpoints, see View or apply for an endpoint.
  • The ECS instance for testing is as follows:
    • The instance type is ecs.c5.4xlarge.
    • The image used by the instance is 64-bit CentOS 7.0.

Test scenario

Test the performance of ApsaraDB PolarDB MySQL clusters with different specifications in the following aspects: read-only performance, write-only performance, read-write performance, and read-only performance in the case of multiple read-only nodes.

The following metrics are used to measure the performance:

  • Transactions per second (TPS): the number of transactions executed per second in the ApsaraDB PolarDB MySQL cluster. Only the number of committed transactions is counted.
  • Queries per second (QPS): the number of SQL statements executed per second in the ApsaraDB PolarDB MySQL cluster, including 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 GitHub
    
    cd sysbench
    ## Open the SysBench directory
    
    git checkout 1.0.18
    ## Switch to the SysBench version 1.0.18
    
    ./autogen.sh
    ## Run autogen.sh
    
    ./configure --prefix=/usr --mandir=/usr/share/man
    
    make
    ## Compile SysBench.
    
    make install
  2. Run the following commands to configure the SysBench client, so that it can use all available CPU cores to process data with the minimum 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 as needed. For example, enter 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. Query the cluster endpoint and port of the ApsaraDB PolarDB MySQL cluster. For more information, see View or apply for an endpoint.
  2. Run the following command on your ECS instance to create a database named testdb in the ApsaraDB PolarDB MySQL cluster:
    mysql -h XXX -P XXX -u XXX -p XXX -e 'create database testdb'
    Note Replace the four XXXs in this command and the subsequent commands with the cluster endpoint, port number, username, and password of the ApsaraDB PolarDB MySQL cluster, respectively.
    Parameter Description
    -h The cluster endpoint of the ApsaraDB PolarDB MySQL cluster.
    -P The port number of the ApsaraDB PolarDB MySQL cluster.
    -u The username for connecting to the ApsaraDB PolarDB MySQL cluster.
    -p The password of the username.
  3. Use SysBench to test the read-only performance of the two-node ApsaraDB PolarDB MySQL cluster. The whole process takes 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 temporary data
  4. Use SysBench to test the write-only performance of the two-node ApsaraDB PolarDB MySQL cluster. The whole process takes 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 temporary data
  5. Use SysBench to test the read-write performance of the two-node ApsaraDB PolarDB MySQL cluster. The whole process takes 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 temporary data
  6. 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 ApsaraDB PolarDB MySQL clusters.
    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 temporary data

What to do next