This topic describes how to use SysBench to test the online transaction processing (OLTP) performance of PolarDB for MySQL clusters. You can follow the instructions in this topic to perform tests to understand the performance of database systems.

Test tool

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

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 the same region. In this test, the zone is Zone I of the China (Hangzhou) region.
  • The network type is a virtual private cloud (VPC).
    Note Make sure that the ECS instance and the PolarDB for MySQL cluster are in the same VPC.
  • The following PolarDB for MySQL cluster is used for testing:
    • The node type is polar.mysql.x4.large (4 cores and 16 GB).
    • The read-only, write-only, and 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 multiple read-only nodes is tested by using the clusters that consist of one primary node and one to eight read-only nodes in sequence.
    • The used connection string is a cluster endpoint. For more information about how to view the PolarDB for MySQL cluster endpoints, see View or apply for an endpoint.
  • The following information about the ECS instance that is used for testing is provided:
    • The instance type is ecs.c5.4xlarge.
    • The image that is used by the instance is 64-bit CentOS 7.0.

Test scenario

Test the OLTP performance of PolarDB for MySQL 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.

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 the number of committed transactions is 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 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 Git.
    
    cd sysbench
    ##Open the directory of SysBench.
    
    git checkout 1.0.18
    ##Switch to SysBench 1.0.18.
    
    ./autogen.sh
    ##Execute 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 so that the kernel can use all the available CPU cores to process packets. 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. Modify the command 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

  1. Obtain the cluster endpoint and 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 XXX parts 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 PolarDB for MySQL cluster that consists of one primary node and one read-only node. 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 PolarDB for MySQL cluster that consists of one primary node and one read-only node. 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 the PolarDB for MySQL cluster that consists of one primary node and one read-only node. 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 the PolarDB for MySQL cluster that consists of one primary node and multiple read-only nodes. The clusters that consist of 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=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