PolarDB:OLTP performance testing

Test tool

SysBench is an open-source, cross-platform performance testing tool primarily used for database benchmarking (such as MySQL) and system performance testing (such as CPU, memory, I/O, and threads). It supports multi-threaded testing and flexibly controls test logic through Lua scripts, making it suitable for database performance evaluation, stress testing, and other scenarios.

Test environment

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

  Note

  The ECS instance and PolarDB for MySQL cluster must be in the same VPC. The PING latency of the ECS instance must be less than 0.2 ms to ensure the validity of the performance evaluation.

• The PolarDB for MySQL cluster used for testing:

  • A two-node cluster (one primary node and one read-only node) is used for read-only, write-only, and read/write performance testing.

  • The endpoints used include the primary endpoint, cluster endpoint, and custom endpoint. During actual testing, the combination of connection strings will be flexibly adjusted according to the situation to ensure maximum utilization of database cluster resources.

• Information about the ECS instance used in the test:

  • The instance type is ecs.c5.4xlarge (16 cores, 32 GB).

  • The image of the instance is 64-bit CentOS 7.0.

  • The number of instances depends on the PolarDB cluster. When the compute node has 8 cores, one ECS instance is used. When the compute node has 16 cores, two ECS instances are used, and so on.

• PolarDB for MySQL cluster parameter configuration:

  • During high concurrency testing, you may encounter the error can't create more than max_prepared_stmt_count statements, which causes the test to fail. You can increase the max_prepared_stmt_count parameter to allow more prepared statements. For information about how to set parameters, 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 meet the requirements. To ensure smooth performance testing, the test command will include the additional parameter --db-ps-mode=disable to disable the Prepared Statement feature.

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

Notes

This test consistently uses tables=250 and table-size=25000 for data volume. The threads parameter is incrementally increased from low to high during testing, and only peak results are displayed on each performance test details page.

Test scenario

Perform OLTP performance tests on the read-only, write-only, and read/write performance of clusters with different Dedicated instance types, each with one primary node and one read-only node.

Observe indicators

• 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 INSERT, SELECT, UPDATE, and DELETE statements.

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
   ##Change the current working directory to sysbench.
   
   ./autogen.sh
   ##Run autogen.sh.
   
   ./configure
   
   make -j
   ##Compile
   
   make install

2. Run the following commands to configure the SysBench client so that the kernel can use all CPU cores to process packets (the default setting is 2 cores) and reduce 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 the use of 16 cores. Modify this value based on your actual configuration. For example, if your ECS instance has 8 cores, enter ff. ffff is for 16 cores, 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 endpoints.

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

3. Connect to a database

   Run the following command on the ECS instance to create a database named PolarDB for MySQL testdb in the 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 following table describes the parameters.

   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 for the username.

4. The following are the test commands for specific scenarios:

   Read-only scenario test (OLTP Read Only)

   Note

   For the read-only scenario, --range-selects=0 is used, which is equivalent to testing the oltp_point_selects scenario.

   1. Data preparation

      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. Execute 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. Clean up 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 cleanup

   The following table describes the fields in the test statements:

   Parameter category	Display content	Description
   tables	Number of data tables	The number of all data tables participating in the test.
   table_size	Number of rows in data table	Displays the number of records in each table.
   	Data volume size	Displays the data volume of the table in storage space units (such as MB/GB).
   threads	The number of concurrent threads	Displays the number of currently configured threads.
   	thread status	Supports real-time viewing of thread running status.

   Mixed read/write test (OLTP Read Write)

   Note

   • Simulates typical business read/write mixed workloads.

   • The specific core parameters such as tables, table-size, and threads will be displayed in a one-to-one manner on the test data page.

   1. Data preparation

      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. Execute 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. Clean up 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 cleanup

   The following table describes the fields in the test statements:

   Parameter category	Display content	Description
   tables	Number of data tables	The number of all data tables participating in the test.
   table_size	Number of rows in data table	Displays the number of records in each table.
   	Data volume size	Displays the data volume of the table in storage space units (such as MB/GB).
   threads	The number of concurrent threads	Displays the number of currently configured threads.
   	thread status	Supports real-time viewing of thread running status.

   Write-only test (OLTP Write Only)

   Note

   The specific core parameters such as tables, table-size, and threads will be displayed in a one-to-one manner on the test data page.

   1. Data preparation

      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. Execute write-only 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 up 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 following table describes the fields in the test statements:

   Parameter category	Display content	Description
   tables	Number of data tables	The number of all data tables participating in the test.
   table_size	Number of rows in data table	Displays the number of records in each table.
   	Data volume size	Displays the data volume of the table in storage space units (such as MB/GB).
   threads	The number of concurrent threads	Displays the number of currently configured threads.
   	thread status	Supports real-time viewing of thread running status.