PolarDB for PostgreSQL 11 - PolarDB - Alibaba Cloud Documentation Center

PostgreSQL (compatible with Oracle) provides pgbench, a lightweight stress testing tool. pgbench can run benchmark tests on PostgreSQL. Benchmark tests are performed by running the same sequence of SQL commands repeatedly across multiple concurrent database sessions. This topic describes how to use pgbench to test the peak performance of PolarDB for PostgreSQL clusters.

Test environments

All tests must be conducted in the China (Qingdao) region. Your PolarDB cluster must be located in the same zone as your ECS instance.
ECS instance type: ecs.g5.16xlarge (64 vCPUs, 256 GiB)
ECS storage: 200 GiB local SSDs
Network type: virtual private cloud (VPC). Your PolarDB cluster and ECS instance are in the same VPC.
OS: 64-bit version of CentOS 7.6
Number of nodes in your PolarDB cluster: one primary node and one read-only node

Note CentOS 6 does not support PostgreSQL 11.

Test metrics

Read-only queries per second (QPS)
The number of SELECT statements executed per second when read-only operations are performed on the database.
Read and write QPS
The number of SELECT, INSERT, and UPDATE statements executed per second when read/write operations are performed on the database.

Before you begin

Install PostgreSQL 11.

Run the following commands to install PostgreSQL 11 on your ECS instance.

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install -y postgresql11

Configure parameters for your PolarDB cluster.
On your PolarDB cluster, configure the following parameters:
```
log_statement = 'none'
enable_hashjoin=off
enable_mergejoin=off
enable_bitmapscan=off
```
Note You can configure only the log_statement parameter in the PolarDB console. For more information about how to configure cluster parameters, see Configure cluster parameters.
After you configure the parameters, restart your PolarDB cluster for the configuration to take effect.

Test methods

Run the following commands to configure environment variables:

export PGHOST=<The internal endpoint of your PolarDB cluster>
export PGPORT=<The internal port number of your PolarDB cluster>
export PGDATABASE=postgres
export PGUSER=<The username to log on to your PolarDB>
export PGPASSWORD=<The password of your PolarDB account>

Note For more information about how to view the endpoints of PolarDB for PostgreSQL clusters, see View or apply for an endpoint.

Initialize test data based on the size of the target database.
- To initialize 1 billion data records:
```
/usr/pgsql-11/bin/pgbench -i -s 10000
```
- To initialize 500 million data records:
```
/usr/pgsql-11/bin/pgbench -i -s 5000
```
- To initialize 100 million data records:
```
/usr/pgsql-11/bin/pgbench -i -s 1000
```

Create test scripts for read-only operations and read/write operations.

Create a script to test read-only operations. Name this script ro.sql.
1. Run the vim ro.sql command.
2. Press the I key to enter the edit mode.
3. In the editor, specify the following scripts:
```
\set aid random_gaussian(1, :range, 10.0)
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
```
4. Press the Esc key to exit the edit mode, and enter :wq to save the file and exit the editor.

Create a script to test read/write operations. Name this script rw.sql.

Run the vim rw.sql command.
Press the I key to enter the edit mode.

In the editor, specify the following scripts:

\set aid random_gaussian(1, :range, 10.0)  
\set bid random(1, 1 * :scale)  
\set tid random(1, 10 * :scale)  
\set delta random(-5000, 5000)  
BEGIN;  
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;  
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;  
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;  
UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;  
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);  
END;

Press the Esc key to exit the edit mode, and enter :wq to save the file and exit the editor.

Run the following commands to test operations:

Run the following commands to test read-only operations:

88C 710 GB(polar.pg.x8.12xlarge)
Total data volume: 1 billion records, hot data: 100 million records
/usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 256 -j 128 -T 120 -D scale=10000 -D range=100000000
Total data volume: 1 billion records, hot data: 500 million records
/usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 256 -j 128 -T 120 -D scale=10000 -D range=500000000
Total data volume: 1 billion records, hot data: 1 billion records
/usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 256 -j 128 -T 120 -D scale=10000 -D range=1000000000

64C 512 GB(polar.pg.x8.8xlarge)
Total data volume: 1 billion records, hot data: 100 million records
/usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 256 -j 256 -T 120 -D scale=10000 -D range=100000000
Total data volume: 1 billion records, hot data: 500 million records
/usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 256 -j 128 -T 120 -D scale=10000 -D range=500000000
Total data volume: 1 billion records, hot data: 1 billion records
/usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 256 -j 128 -T 120 -D scale=10000 -D range=1000000000

32C 256 GB(polar.pg.x8.4xlarge)
Total data volume: 1 billion records, hot data: 100 million records
/usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 128 -j 128 -T 120 -D scale=10000 -D range=100000000
Total data volume: 1 billion records, hot data: 500 million records
/usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 128 -j 128 -T 120 -D scale=10000 -D range=500000000
Total data volume: 1 billion records, hot data: 1 billion records
/usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 128 -j 128 -T 120 -D scale=10000 -D range=1000000000

16C 128 GB(polar.pg.x8.2xlarge)
Total data volume: 1 billion records, hot data: 100 million records
/usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=100000000
Total data volume: 1 billion records, hot data: 500 million records
/usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=500000000
Total data volume: 1 billion records, hot data: 1 billion records
/usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=1000000000

8C 64 GB(polar.pg.x8.xlarge)
Total data volume: 1 billion records, hot data: 100 million records
/usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 64 -j 32 -T 120 -D scale=10000 -D range=100000000
Total data volume: 1 billion records, hot data: 500 million records
/usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 64 -j 32 -T 120 -D scale=10000 -D range=500000000
Total data volume: 1 billion records, hot data: 1 billion records
/usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 64 -j 32 -T 120 -D scale=10000 -D range=1000000000

8C 32 GB(polar.pg.x4.xlarge)
Total data volume: 1 billion records, hot data: 100 million records
/usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 64 -j 32 -T 120 -D scale=10000 -D range=100000000
Total data volume: 1 billion records, hot data: 500 million records
/usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 64 -j 32 -T 120 -D scale=10000 -D range=500000000
Total data volume: 1 billion records, hot data: 1 billion records
/usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 64 -j 32 -T 120 -D scale=10000 -D range=1000000000

4C 16 GB(polar.pg.x4.large)
Total data volume: 500 million records, hot data: 50 million records
/usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 16 -j 16 -T 120 -D scale=5000 -D range=50000000
Total data volume: 500 million records, hot data: 100 million records
/usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 16 -j 16 -T 120 -D scale=5000 -D range=100000000

2C 8 GB(polar.pg.x4.medium)
Total data volume: 100 million records, hot data: 50 million records
/usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 16 -j 32 -T 120 -D scale=1000 -D range=50000000
Total data volume: 100 million records, hot data: 100 million records
/usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 16 -j 32 -T 120 -D scale=1000 -D range=100000000

Run the following commands to test read/write operations:

88C 710 GB(polar.pg.x8.12xlarge)
Total data volume: 1 billion records, hot data: 100 million records
/usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 128 -j 128 -T 120 -D scale=10000 -D range=100000000
Total data volume: 1 billion records, hot data: 500 million records
/usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 128 -j 128 -T 120 -D scale=10000 -D range=500000000
Total data volume: 1 billion records, hot data: 1 billion records
/usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 128 -j 128 -T 120 -D scale=10000 -D range=1000000000

64C 512 GB(polar.pg.x8.8xlarge)
Total data volume: 1 billion records, hot data: 100 million records
/usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 128 -j 128 -T 120 -D scale=10000 -D range=100000000
Total data volume: 1 billion records, hot data: 500 million records
/usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 128 -j 128 -T 120 -D scale=10000 -D range=500000000
Total data volume: 1 billion records, hot data: 1 billion records
/usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 128 -j 128 -T 120 -D scale=10000 -D range=1000000000

32C 256 GB(polar.pg.x8.4xlarge)
Total data volume: 1 billion records, hot data: 100 million records
/usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 128 -j 128 -T 120 -D scale=10000 -D range=100000000
Total data volume: 1 billion records, hot data: 500 million records
/usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 128 -j 128 -T 120 -D scale=10000 -D range=500000000
Total data volume: 1 billion records, hot data: 1 billion records
/usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 128 -j 128 -T 120 -D scale=10000 -D range=1000000000

16C 128 GB(polar.pg.x8.2xlarge)
Total data volume: 1 billion records, hot data: 100 million records
/usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=100000000
Total data volume: 1 billion records, hot data: 500 million records
/usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=500000000
Total data volume: 1 billion records, hot data: 1 billion records
/usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=1000000000

8C 64 GB(polar.pg.x8.xlarge)
Total data volume: 1 billion records, hot data: 100 million records
/usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=100000000
Total data volume: 1 billion records, hot data: 500 million records
/usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=500000000
Total data volume: 1 billion records, hot data: 1 billion records
/usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=1000000000

8C 32 GB(polar.pg.x4.xlarge)
Total data volume: 1 billion records, hot data: 100 million records
/usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=100000000
Total data volume: 1 billion records, hot data: 500 million records
/usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=500000000
Total data volume: 1 billion records, hot data: 1 billion records
/usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=1000000000

4C 16 GB(polar.pg.x4.large)
Total data volume: 500 million records, hot data: 50 million records
/usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 16 -j 16 -T 120 -D scale=5000 -D range=50000000
Total data volume: 500 million records, hot data: 100 million records
/usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 16 -j 16 -T 120 -D scale=5000 -D range=100000000

2C 4 GB(polar.pg.x4.medium)
Total data volume: 100 million records, hot data: 50 million records
/usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 8 -j 8 -T 120 -D scale=1000 -D range=50000000
Total data volume: 100 million records, hot data: 100 million records
/usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 8 -j 8 -T 120 -D scale=1000 -D range=100000000

Note

scale: The value of scale multiplied by 100,000 indicates the number of test data records.
range: Indicates the number of hot data records.
-c: Indicates the number of connections to test. This number does not represent the maximum number of connections supported by this type of cluster. For more information, see Specifications of compute nodes.

Test results


Specification code	Number of data records to test	Number of hot (active) data records	Read-only QPS	Read and write QPS
polar.pg.x8.12xlarge 88 cores, 710 GB	1 billion	100 million	628863.87	259862.37
		500 million	612612.86	243871.65
		1 billion	607162.83	217592.26
polar.pg.x8.8xlarge 64 cores, 512 GB	1 billion	100 million	587612.46	227352.23
		500 million	539371.54	209782.29
		1 billion	498728.28	199821.76
polar.pg.x8.4xlarge 32 cores, 256 GB	1 billion	100 million	487138.21	237280.67
		500 million	442339.25	215081.79
		1 billion	420348.28	198341.34
polar.pg.x8.2xlarge 16 cores, 128 GB	1 billion	100 million	269781.83	168612.27
		500 million	249271.32	131725.26
		1 billion	233219.96	109826.82
polar.pg.x8.xlarge 8 cores, 64 GB	1 billion	100 million	148621.38	71787.83
		500 million	130862.86	59298.44
		1 billion	123151.90	52324.72
polar.pg.x4.xlarge 8 cores, 32 GB	1 billion	100 million	137366.92	59738.33
		500 million	114932.64	52873.87
		1 billion	109248.29	48993.82
polar.pg.x4.large 4 cores, 16 GB	500 million	50 million	67289.76	40221.21
polar.pg.x4.large 4 cores, 16 GB	500 million	100 million	78393.56	46281.85
polar.pg.x4.medium 2 cores, 8 GB	100 million	50 million	26383.91	18983.55
polar.pg.x4.medium 2 cores, 8 GB	100 million	100 million	27821.49	17986.46

Note

Specification code: the code of PolarDB for PostgreSQL specifications.
Number of data records to test: the number of data records for the test.
Number of hot (active) data records: the number of records for querying and updating SQL commands in the test.
Read-only QPS: the number of read-only requests processed per second.
Read and write QPS: the number of read/write requests processed per second.