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 tests 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 operate in the same VPC.
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 is used to connect to the cluster. For 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.

Testing scenarios

Test the OLTP performance of a PolarDB for PostgreSQL 14 cluster in the read, write, and read and 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 or Ubuntu
```
sudo apt -y install sysbench
```
RHEL or 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 MySQL 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

## Compile sysbench.
make -j

## Install sysbench.
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 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 the "Create an account" section of the Create a database account topic. 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 the "Create a database" section of the Database management topic.
Obtain the primary endpoint of the cluster. For more information, see the "View endpoints and ports" section of the View or apply for an endpoint topic.

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

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 the 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 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 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