This topic describes how to use SysBench to test the online transaction processing (OLTP) performance of ApsaraDB PolarDB MySQL-compatible edition 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 Computing Service (ECS) instance and the ApsaraDB PolarDB MySQL-compatible edition cluster to be tested must be deployed in the same zone of the same region. In this example, Hangzhou Zone I in the China (Hangzhou) region is used.
  • The network type is virtual private cloud (VPC).
    Note Make sure that the ECS instance and the ApsaraDB PolarDB MySQL-compatible edition cluster are in the same VPC.
  • The following ApsaraDB PolarDB MySQL-compatible edition clusters are used in the test:
    • When you use Cluster Edition clusters,
      • The cluster of two nodes (one primary node and one read-only node) is used to test the read-only, write-only, and read/write performance. To test the performance of multiple read-only nodes, you can use the clusters that contain one primary node and one to eight read-only nodes in sequence.
      • The cluster endpoint is used as the connection string. For more information about how to view cluster endpoints of a ApsaraDB PolarDB MySQL-compatible edition cluster, see View or apply for an endpoint.
    • When you use Archive Database clusters,
      • The Archive Database cluster contains only one node, you can test the read-only, write-only, and read/write performance on the node.
      • The cluster endpoint is used as the connection string. For more information about how to view cluster endpoints of a ApsaraDB PolarDB MySQL-compatible edition cluster, see View or apply for an endpoint.
  • The following ECS instance is used in the test:
    • The instance type is ecs.c5.4xlarge.
    • The image of the instance is 64-bit CentOS 7.0.
    • The number of ECS instances varies based on the specifications of the PolarDB cluster. For example, you can use one ECS instance for the cluster of 8-core CPU and two ECS instances for the cluster of 16-core CPU.

Test scenario

  • Cluster Edition

    Perform OLTP performance tests on the read-only, write-only, read/write performance for ApsaraDB PolarDB MySQL-compatible edition clusters of different specifications (one primary node and one read-only node. Perform OLTP performance tests on the read-only performance for the cluster that contains one primary node and multiple read-only nodes.

  • Archive Database

    Perform OLTP performance tests on the read-only, write-only, read/write performance for ApsaraDB PolarDB MySQL-compatible edition clusters of different specifications.

The following metrics are used to measure the performance:

  • 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. By default, two CPU cores are used. This minimizes the number of times for switching cross 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 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 method

Note The ApsaraDB PolarDB MySQL-compatible edition Cluster Edition clusters are used in the example.
  1. Obtain the cluster endpoint and port number of your ApsaraDB PolarDB MySQL-compatible edition cluster. For more information, see View or apply for an endpoint.
  2. Disable the Primary Node Accepts Read Requests feature of ApsaraDB PolarDB MySQL-compatible edition 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 ApsaraDB PolarDB MySQL-compatible edition 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 ApsaraDB PolarDB MySQL-compatible edition cluster.
    Parameter Description
    -h The cluster endpoint of the ApsaraDB PolarDB MySQL-compatible edition cluster.
    -P The port number of the ApsaraDB PolarDB MySQL-compatible edition cluster.
    -u The username of the ApsaraDB PolarDB MySQL-compatible edition cluster.
    -p The password of the account.
  4. Use SysBench to test the read-only performance of the ApsaraDB PolarDB MySQL-compatible edition 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=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 ApsaraDB PolarDB MySQL-compatible edition 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=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 ApsaraDB PolarDB MySQL-compatible edition 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=sbtest --table_size=25000 --tables=250 --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=25000 --tables=250 --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=25000 --tables=250 --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 ApsaraDB PolarDB MySQL-compatible edition 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=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.

What to do next