Use sysbench to test the OLTP performance - PolarDB - Alibaba Cloud Documentation Center

This topic describes how to use sysbench to test the online transaction processing (OLTP) performance of a PolarDB for PostgreSQL 14 cluster. You can perform a test based on the instructions in this topic to gain insights into the performance of your cluster.

Test tool

Sysbench is a modular, cross-platform, and multi-threaded benchmark tool that you can use to evaluate the performance of a heavily loaded database system based on core metrics. Sysbench allows you to quickly test the performance of a database without complex benchmark settings.

Test environment

The Elastic Compute Service (ECS) instance and the PolarDB for PostgreSQL cluster used in the test are deployed in the same region and zone.
The network type of the ECS instance and the PolarDB for PostgreSQL cluster is Virtual Private Cloud (VPC).
Note
The ECS instance and the PolarDB for PostgreSQL cluster run in the same VPC.
The following items list information about the PolarDB for PostgreSQL cluster used in the test:
- The cluster has one primary node, one read-only node, and one standby node.
- The primary endpoint of the cluster is used to connect to the cluster. For more information about how to view the endpoints of a PolarDB for PostgreSQL cluster, see View or apply for an endpoint.
Information about the ECS instance used in the test:
- The instance type is ecs.c5.4xlarge.
- The instance runs the 64-bit CentOS 7.6 operating system.

Test scenarios

Test the OLTP performance of a PolarDB for PostgreSQL 14 cluster in the read, write, and read/write scenarios. The cluster has one primary node, one read-only node, and one standby node.

Metrics

Transactions per second (TPS): the number of transactions that are performed per second in the database. Only committed transactions are counted.

Install sysbench

Quick installation method:

Debian/Ubuntu
```
sudo apt -y install sysbench
```
RHEL/CentOS
```
sudo yum -y install sysbench
```

Source code-based installation method:

In the following example, sysbench is installed on the RHEL or CentOS system. You must install sysbench by using root permissions.

## Install related dependencies.
yum -y install make automake libtool pkgconfig libaio-devel git

## Install related dependencies.
yum -y install mariadb-devel openssl-devel

## Install PostgreSQL dependencies.
yum -y install postgresql-devel

## Download sysbench from GitHub.
git clone https://github.com/akopytov/sysbench.git

## Access the sysbench directory.
cd sysbench

## Run autogen.sh.
./autogen.sh

## Configure the build process and add support for PostgreSQL.
./configure --with-pgsql

## Make.
make -j

## Install.
make install

Run the following shell command to verify the installation. If pgsql options are included in the returned result, the installation is successful.

sysbench --help

Sample returned pgsql options:

Note

In the earlier versions of sysbench, the --pgsql-sslmode option may not be included in pgsql options, which does not affect the test result.

pgsql options:
  --pgsql-host=STRING     PostgreSQL server host [localhost]
  --pgsql-port=N          PostgreSQL server port [5432]
  --pgsql-user=STRING     PostgreSQL user [sbtest]
  --pgsql-password=STRING PostgreSQL password []
  --pgsql-db=STRING       PostgreSQL database name [sbtest]
  --pgsql-sslmode=STRING  PostgreSQL SSL mode (disable, allow, prefer, require, verify-ca, verify-full) [prefer]

Note

For information about the binary installation commands, see sysbench documentation.

Test procedure

Prepare the test environment

Add the IP address of the ECS instance to a whitelist of the PolarDB cluster. For more information, see Set whitelists for a cluster.
Create a database account. For more information, see Create a database account. We recommend that you create a privileged account.
Create a database named testdb in the PolarDB console and use the database account that you created in the previous step as the database owner. For more information, see Create a database.
Obtain the primary endpoint of the cluster. For more information, see View or apply for an endpoint.

Use sysbench to test the OLTP performance of the PolarDB for PostgreSQL cluster.

The following table describes the parameters.

Note

Replace the parameter values with the endpoint and port of the cluster used in the test.

Parameter	Description
--pgsql-user	The database account that is used to log on to the PolarDB for PostgreSQL cluster.
--pgsql-password	The password of the database account that is used to log on to the PolarDB for PostgreSQL cluster.
--pgsql-host	The primary endpoint of the PolarDB for PostgreSQL cluster.
--pgsql-port	The port associated with the primary endpoint of the PolarDB for PostgreSQL cluster.

Quick test

Note

In this example, eight test tables are imported to the database. Each table contains 250,000 rows of data. The estimated total size of the database is 1 GB.
The test runs with 256 concurrent threads. The test duration of each scenario is 5 minutes.

Prepare data.

## Clean up data.
sysbench --pgsql-user=XXX --pgsql-password=XXX --pgsql-host=XXX --pgsql-port=XXX --report-interval='1' --tables='8' --db-driver='pgsql' --events='0' --histogram='on' --percentile='95' --threads='256' --table-size='500000' --time='300' --rand-type='uniform' --pgsql-db=testdb oltp_insert cleanup
## Import data.
sysbench --pgsql-user=XXX --pgsql-password=XXX --pgsql-host=XXX --pgsql-port=XXX --report-interval='1' --tables='8' --db-driver='pgsql' --events='0' --histogram='on' --percentile='95' --threads='256' --table-size='500000' --time='300' --rand-type='uniform' --pgsql-db=testdb oltp_insert prepare

Test the OLTP performance of the database in different scenarios.

Test the OLTP performance of the database in mixed read and write scenarios.

## Run the oltp_read_write test.
sysbench --pgsql-user=XXX --pgsql-password=XXX --pgsql-host=XXX --pgsql-port=XXX --report-interval='1' --tables='8' --db-driver='pgsql' --events='0' --histogram='on' --percentile='95' --threads='256' --table-size='500000' --time='300' --rand-type='uniform' --pgsql-db=testdb oltp_read_write run

Test the OLTP performance of the database in write scenarios. The following section describes three write scenarios, each with a duration of 5 minutes.

Run the oltp_update_index test.

sysbench --pgsql-user=XXX --pgsql-password=XXX --pgsql-host=XXX --pgsql-port=XXX --report-interval='1' --tables='8' --db-driver='pgsql' --events='0' --histogram='on' --percentile='95' --threads='256' --table-size='500000' --time='300' --rand-type='uniform' --pgsql-db=testdb oltp_update_index run

Run the oltp_update_non_index test.

sysbench --pgsql-user=XXX --pgsql-password=XXX --pgsql-host=XXX --pgsql-port=XXX --report-interval='1' --tables='8' --db-driver='pgsql' --events='0' --histogram='on' --percentile='95' --threads='256' --table-size='500000' --time='300' --rand-type='uniform' --pgsql-db=testdb oltp_update_non_index run

Run the oltp_write_only test.

sysbench --pgsql-user=XXX --pgsql-password=XXX --pgsql-host=XXX --pgsql-port=XXX --report-interval='1' --tables='8' --db-driver='pgsql' --events='0' --histogram='on' --percentile='95' --threads='256' --table-size='500000' --time='300' --rand-type='uniform' --pgsql-db=testdb oltp_write_only run

Test the OLTP performance of the database in read scenarios. The following section describes two read scenarios, each with a duration of 5 minutes.

Run the oltp_point_select test.

sysbench --pgsql-user=XXX --pgsql-password=XXX --pgsql-host=XXX --pgsql-port=XXX --report-interval='1' --tables='8' --db-driver='pgsql' --events='0' --histogram='on' --percentile='95' --threads='256' --table-size='500000' --time='300' --rand-type='uniform' --pgsql-db=testdb oltp_point_select run

Run the oltp_read_only test.

sysbench --pgsql-user=XXX --pgsql-password=XXX --pgsql-host=XXX --pgsql-port=XXX --report-interval='1' --tables='8' --db-driver='pgsql' --events='0' --histogram='on' --percentile='95' --threads='256' --table-size='500000' --time='300' --rand-type='uniform' --pgsql-db=testdb oltp_read_only run

Note

The test runs with 256 concurrent threads. If you receive the connection creation failed error message, you can adjust the value of the threads parameter based on the maximum number of connections supported by the node specifications. You can use one of the following methods to confirm the maximum number of connections to the cluster:

View the maximum number of connections in the PolarDB console.
1. Log on to the PolarDB console. Click Clusters in the left-side navigation pane. Select a region in the upper-left corner and click the ID of the cluster that you want to manage to go to the Basic Information page.
2. In the Database Nodes section of the Basic Information page, click the icon in the upper-right corner of the section to switch views. Then, you can view the Default Maximum Connections column.
Execute the following statement to query the maximum number of connections:
```
SHOW max_connections;
```

IOBound and CPUBound tests

IOBound test: tests the OLTP performance of the database in I/O-intensive scenarios.
CPUBound test: tests the OLTP performance of the database in CPU-intensive scenarios.

The following tables describe the amounts of test data to be used for testing the performance of a PolarDB cluster that has 8 CPU cores and 64 GB of memory.

In most cases, an IOBound test requires test data that is twice the size of the buffer pool. Modify the related sysbench parameters as described in the following table. Set the other parameters to the values used in the test scenarios in Quick test.
Sysbench parameter
Description
Value
--tables
The number of tables.
8
--table-size
The number of rows.
64000000
In most cases, a CPUBound test requires test data that is half the size of the buffer pool. Modify the related sysbench parameters as described in the following table. Set the other parameters to the values used in the test scenarios in the Quick test.
Sysbench parameter
Description
Value
--tables
The number of tables.
8
--table-size
The number of rows.
12000000

References

For more information about the performance comparison between PolarDB for PostgreSQL and similar cloud-native PostgreSQL 14 databases, see Performance comparison with similar PostgreSQL cloud-native databases.
For more information about the performance comparison between PolarDB for PostgreSQL and open source PostgreSQL 14 databases, see Performance comparison with open-source PostgreSQL.

Sysbench parameter	Description	Value
--tables	The number of tables.	8
--table-size	The number of rows.	64000000