PolarDB:Performance test method (OLTP)

Test tool

Sysbench is an open source, cross-platform performance test tool. It is primarily used for database benchmarks, such as for MySQL, and system performance tests, including CPU, memory, I/O, and threads. Sysbench supports multi-threaded testing and uses Lua scripts to flexibly control test logic, making it suitable for database performance evaluation and stress testing.

Test environment

• The test ECS instance and the PolarDB for MySQL cluster must be in the same region, zone, and VPC network.

  Note

  The ECS instance and the PolarDB for MySQL cluster must be in the same VPC. This ensures that the PING latency from the ECS instance is less than 0.2 ms, which guarantees a valid performance evaluation.

• The PolarDB for MySQL cluster used for testing is as follows:

  • Read-only, write-only, and read/write performance tests use a two-node cluster (one primary node and one read-only node).

  • The endpoints used include the primary endpoint, cluster endpoint, and custom endpoints. During the test, the combination of connection strings is adjusted as needed to maximize the use of database cluster resources.

• The ECS instance information for the test is as follows:

  • Instance type: ecs.c5.4xlarge (16 CPU cores, 32 GB memory).

  • Image: CentOS 7.0 64-bit.

  • The number of instances depends on the PolarDB cluster. For an 8-core compute node, use one ECS instance. For a 16-core instance type, use two ECS instances, and so on.

• PolarDB for MySQL cluster parameter settings:

  • During high concurrency testing, the can't create more than max_prepared_stmt_count statements error may occur, causing the test to fail. Increase the max_prepared_stmt_count parameter to allow more prepared statements. For information about parameter settings, see Set cluster parameters and node parameters.

  • In large-scale, high-concurrency scenarios, even setting the max_prepared_stmt_count parameter to its maximum value may not be sufficient. To ensure the performance test runs smoothly, add the --db-ps-mode=disable parameter to the test command to disable the Prepared Statement feature.

  • For extra-large clusters (88 cores and above), additional parameter tuning is required to fully utilize cluster resources. The specific tuning parameters depend on the instance type and business scenario on the test page. Before implementing optimizations, perform a thorough performance evaluation and fine-tune the parameters based on actual results.

Notes

The test uses `tables=250` and `table-size=25000`. The `threads` value is increased incrementally. Each performance test product page displays only the peak results.

Test scenarios

This test measures the OLTP performance for read-only, write-only, and read/write scenarios on a two-node cluster (one primary and one read-only node) across different Dedicated instance types.

Metrics

• TPS (Transactions Per Second): The number of transactions the database executes per second, based on the number of successful COMMITs.

• QPS (Queries Per Second): The number of SQL statements (including INSERT, SELECT, UPDATE, and DELETE) the database executes per second.

Install Sysbench

1. Run the following commands on the ECS instance to install Sysbench.

   git clone https://github.com/akopytov/sysbench.git
   ## Download Sysbench from Git
   
   cd sysbench
   ## Open the Sysbench directory
   
   ./autogen.sh
   ## Run autogen.sh
   
   ./configure
   
   make -j
   ## Compile
   
   make install

2. Run the following commands to configure the Sysbench client. This allows the kernel to use all CPU cores to process data packets (the default is 2 cores) and reduces context switching between CPU cores.

   sudo sh -c 'for x in /sys/class/net/eth0/queues/rx-*; do echo ffff>$x/rps_cpus; done'

   Note

   ffff indicates that 16 cores are used. Modify this value based on your configuration. For example, if your ECS instance has 8 cores, enter ff. If it has 16 cores, enter ffff, and so on.

   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 endpoint and port of the PolarDB for MySQL cluster. For more information, see Manage connection addresses.

2. Set Primary Node Accepts Read Requests to Yes for the PolarDB for MySQL cluster endpoint. For more information, see Configure a database proxy.

3. Log on to the database

   Run the following command on the ECS instance to create a database named testdb in the PolarDB for MySQL cluster.

   mysql -h XXX -P XXX -u XXX -p XXX -e 'create database testdb'

   Note

   Replace XXX in this command and subsequent commands with the cluster endpoint, port number, username, and password of your PolarDB for MySQL cluster. The parameters are described as follows.

   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 for the PolarDB for MySQL cluster.
   -p	The password for the username.

4. The test commands for specific scenarios are as follows:

   Read-only scenario test (OLTP Read Only)

   Note

   In this read-only scenario, --range-selects=0 is set, which is equivalent to testing the oltp_point_selects scenario.

   1. Prepare data

      sysbench --db-driver=mysql --mysql-host=<host> --mysql-port=<port> --mysql-user=<user> --mysql-password=<password> --mysql-db=testdb --range-selects=0 --table_size=25000 --tables=250 --events=0 --time=600 --rand-type=uniform --threads=XXX oltp_read_only prepare

   2. Run the read-only test

      sysbench --db-driver=mysql --mysql-host=<host> --mysql-port=<port> --mysql-user=<user> --mysql-password=<password> --mysql-db=testdb --range-selects=0 --table_size=25000 --tables=250 --events=0 --time=600 --rand-type=uniform --threads=XXX oltp_read_only run

   3. Data cleanup

      sysbench --db-driver=mysql --mysql-host=<host> --mysql-port=<port> --mysql-user=<user> --mysql-password=<password> --mysql-db=testdb --range-selects=0 --table_size=25000 --tables=250 --events=0 --time=600 --rand-type=uniform --threads=XXX oltp_read_only cleanup

   The fields in the test statements are described as follows:

   Parameter category	Displayed content	Description
   tables	Number of data tables	The total number of data tables in the test.
   table_size	Number of rows in data table	The number of records in each table.
   	Data volume size	The data volume of the table, in units such as MB or GB.
   threads	Number of concurrent threads	The currently configured number of threads.
   	Thread status	Lets you view the running status of threads in real time.

   Mixed read/write test (OLTP Read Write)

   Note

   • This simulates a typical mixed read/write business workload.

   • Core parameters such as `tables`, `table-size`, and `threads` are displayed on a one-to-one basis on the test data page.

   1. Prepare data

      sysbench --db-driver=mysql --mysql-host=<host> --mysql-port=<port> --mysql-user=<user> --mysql-password=<password> --mysql-db=testdb --table_size=25000 --tables=250 --events=0 --time=600 --rand-type=uniform --threads=XXX oltp_read_write prepare

   2. Run the read/write test

      sysbench --db-driver=mysql --mysql-host=<host> --mysql-port=<port> --mysql-user=<user> --mysql-password=<password> --mysql-db=testdb --table_size=25000 --tables=250 --events=0 --time=600 --rand-type=uniform --threads=XXX oltp_read_write run

   3. Data cleaning

      sysbench --db-driver=mysql --mysql-host=<host> --mysql-port=<port> --mysql-user=<user> --mysql-password=<password> --mysql-db=testdb --table_size=25000 --tables=250 --events=0 --time=600 --rand-type=uniform --threads=XXX oltp_read_write cleanup

   The fields in the test statements are described as follows:

   Parameter category	Displayed content	Description
   tables	Number of data tables	The total number of data tables in the test.
   table_size	Number of rows in data table	The number of records in each table.
   	Data volume size	The data volume of the table, in units such as MB or GB.
   threads	Number of concurrent threads	The currently configured number of threads.
   	Thread status	Lets you view the running status of threads in real time.

   Write-only test (OLTP Write Only)

   Note

   Core parameters such as `tables`, `table-size`, and `threads` are displayed on a one-to-one basis on the test data page.

   1. Prepare data

      sysbench --db-driver=mysql --mysql-host=<host> --mysql-port=<port> --mysql-user=<user> --mysql-password=<password> --mysql-db=testdb --table_size=25000 --tables=250 --events=0 --time=600 --rand-type=uniform --threads=XXX oltp_write_only prepare

   2. Perform a write test

      sysbench --db-driver=mysql --mysql-host=<host> --mysql-port=<port> --mysql-user=<user> --mysql-password=<password> --mysql-db=testdb --table_size=25000 --tables=250 --events=0 --time=600 --rand-type=uniform --threads=XXX oltp_write_only run

   3. Clean data

      sysbench --db-driver=mysql --mysql-host=<host> --mysql-port=<port> --mysql-user=<user> --mysql-password=<password> --mysql-db=testdb --table_size=25000 --tables=250 --events=0 --time=600 --rand-type=uniform --threads=XXX oltp_write_only cleanup

   The fields in the test statements are described as follows:

   Parameter category	Displayed content	Description
   tables	Number of data tables	The total number of data tables in the test.
   table_size	Number of rows in data table	The number of records in each table.
   	Data volume size	The data volume of the table, in units such as MB or GB.
   threads	Number of concurrent threads	The currently configured number of threads.
   	Thread status	Lets you view the running status of threads in real time.