This topic describes how to use pgbench to test the peak performance of the primary node of a PolarDB for PostgreSQL cluster.
pgbench is a lightweight stress testing tool for PostgreSQL. pgbench is a simple program for running benchmark tests on PostgreSQL. It runs the same sequence of SQL statements over and over, possibly in multiple concurrent database sessions.
Test environment
- All tests are completed locally by using IP addresses and ports.
- ECS instance type: ecs.g5.16xlarge (64 cores, 256 GiB).
- Network type: VPC.
- Operating system: 64-bit CentOS 7.6.
Note CentOS 6 does not support PostgreSQL 11.
Test indicators
- 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 INSERT, SELECT, and UPDATE statements executed per second when read and write operations are performed on the database.
Prerequisites
- Install PostgreSQL 11
Run the following commands to install PostgreSQL 11 on the ECS instance.
yum install -y https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm yum install -y https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm yum install -y postgresql11* su - postgres vi .bash_profile export PS1="$USER@`/bin/hostname -s`-> " export LANG=en_US.utf8 export PGHOME=/usr/pgsql-11 export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH export DATE=`date +"%Y%m%d%H%M"` export PATH=$PGHOME/bin:$PATH:. export MANPATH=$PGHOME/share/man:$MANPATH alias rm='rm -i' alias ll='ls -lh' unalias vi
- Modify parameters of the PolarDB for PostgreSQL cluster
You must submit a ticket to modify the YAML configuration file of the cluster because some parameters cannot be modified in the console. The configurations are as follows:
default_statistics_target: "100" max_wal_size: "64GB" #half mem size effective_cache_size: "96GB" #3/4 mem size max_parallel_workers_per_gather: "16" #half cpu core number maintenance_work_mem: "2GB" #1/32 mem, don't exceed 8GB checkpoint_completion_target: "0.9" max_parallel_workers: "32" #cpu core number,don't exceed 64 max_prepared_transactions: "2100" archive_mode: "off" work_mem: "64MB" #mem 1/2000,don't exceed 128MB wal_buffers: "16MB" min_wal_size: "64GB" #1/4 mem size, min size 3GB (3 wal files, 2 as preallocated) shared_buffers: "192GB" #75% mem size 8GB max_connections: "12900" polar_bulk_extend_size: "4MB" polar_xlog_record_buffers: "25GB" #10~15% mem size,min size 1GB hot_standby_feedback: "on" full_page_writes: "off" synchronous_commit: "on" polar_enable_async_pwrite: "off" polar_parallel_bgwriter_delay: "10ms" polar_max_non_super_conns: '12800' polar_parallel_new_bgwriter_threshold_lag: "6GB" polar_use_statistical_relpages: "on" polar_vfs.enable_file_size_cache: "on"
Note The preceding code snippet is used as an example to modify the configuration file of a 32-core 256 GB polar.pg.x8.4xlarge cluster. Set the same memory size for a PolarDB for PostgreSQL cluster and an ApsaraDB RDS for PostgreSQL cluster to compare their performance.After you modify the configurations, restart the PolarDB for PostgreSQL cluster for the configurations to take effect.
Test procedure
- Run the following commands to initialize the test data based on the size of the target
database:
- Initialize 5 billion records of data:
pgbench -i -s 50000
- Initialize 1 billion records of data:
pgbench -i -s 10000
- Initialize 500 million records of data:
pgbench -i -s 5000
- Initialize 100 million records of data:
pgbench -i -s 1000
- Initialize 5 billion records of data:
- Run the following commands to configure environment variables:
export PGHOST=<Private endpoint of the primary node of the PolarDB for PostgreSQL cluster> export PGPORT=<Private port of the primary node of the PolarDB for PostgreSQL cluster> export PGDATABASE=postgres export PGUSER=<Username to log on to the PolarDB for PostgreSQL database> export PGPASSWORD=<User password to log on to the PolarDB for PostgreSQL database>
- Create the test scripts to perform read-only operations and perform read and write
operations.
- Create script ro.sql including the following code to perform read-only operations:
\set aid random_gaussian(1, :range, 10.0) SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
- Create script rw.sql including the following code to perform read and write operations:
\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;
- Create script ro.sql including the following code to perform read-only operations:
- Run the following commands to perform the test.
- Perform read-only operations:
polar.o.x8.4xlarge, the total number of data records is 1 billion, the number of hot data records is 100 million. pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 128 -j 128 -T 120 -D scale=10000 -D range=100000000 polar.o.x8.4xlarge, the total number of data records is 1 billion, the number of hot data records is 500 million. pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 128 -j 128 -T 120 -D scale=10000 -D range=500000000 polar.o.x8.4xlarge, the total number of data records is 1 billion, the number of hot data records is 1 billion. pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 128 -j 128 -T 120 -D scale=10000 -D range=1000000000 polar.o.x8.2xlarge, the total number of data records is 1 billion, the number of hot data records is 100 million. pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=100000000 polar.o.x8.2xlarge, the total number of data records is 1 billion, the number of hot data records is 500 million. pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=500000000 polar.o.x8.2xlarge, the total number of data records is 1 billion, the number of hot data records is 1 billion. pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=1000000000 polar.o.x4.xlarge, the total number of data records is 1 billion, the number of hot data records is 100 million. pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=100000000 polar.o.x4.xlarge, the total number of data records is 1 billion, the number of hot data records is 500 million. pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=500000000 polar.o.x4.xlarge, the total number of data records is 1 billion, the number of hot data records is 1 billion. pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=1000000000 polar.o.x4.large, the total number of data records is 500 million, the number of hot data records is 100 million. pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 16 -j 16 -T 120 -D scale=5000 -D range=100000000 polar.o.x4.large, the total number of data records is 500 million, the number of hot data records is 500 million. pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 16 -j 16 -T 120 -D scale=5000 -D range=500000000 polar.o.x4.medium, the total number of data records is 100 million, the number of hot data records is 50 million. pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 8 -j 8 -T 120 -D scale=1000 -D range=50000000 polar.o.x4.medium, the total number of data records is 100 million, the number of hot data records is 100 million. pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 8 -j 8 -T 120 -D scale=1000 -D range=100000000
- Perform read and write operations:
polar.o.x8.4xlarge, the total number of data records is 1 billion, the number of hot data records is 100 million. pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 128 -j 128 -T 120 -D scale=10000 -D range=100000000 polar.o.x8.4xlarge, the total number of data records is 1 billion, the number of hot data records is 500 million. pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 128 -j 128 -T 120 -D scale=10000 -D range=500000000 polar.o.x8.4xlarge, the total number of data records is 1 billion, the number of hot data records is 1 billion. pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 128 -j 128 -T 120 -D scale=10000 -D range=1000000000 polar.o.x8.2xlarge, the total number of data records is 1 billion, the number of hot data records is 100 million. pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=100000000 polar.o.x8.2xlarge, the total number of data records is 1 billion, the number of hot data records is 500 million. pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=500000000 polar.o.x8.2xlarge, the total number of data records is 1 billion, the number of hot data records is 1 billion. pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=1000000000 polar.o.x4.xlarge, the total number of data records is 1 billion, the number of hot data records is 100 million. pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=100000000 polar.o.x4.xlarge, the total number of data records is 1 billion, the number of hot data records is 500 million. pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=500000000 polar.o.x4.xlarge, the total number of data records is 1 billion, the number of hot data records is 1 billion. pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=1000000000 polar.o.x4.large, the total number of data records is 500 million, the number of hot data records is 100 million. pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 16 -j 16 -T 120 -D scale=5000 -D range=100000000 polar.o.x4.large, the total number of data records is 500 million, the number of hot data records is 500 million. pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 16 -j 16 -T 120 -D scale=5000 -D range=500000000 polar.o.x4.medium, the total number of data records is 100 million, the number of hot data records is 50 million. pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 8 -j 8 -T 120 -D scale=1000 -D range=50000000 polar.o.x4.medium, the total number of data records is 100 million, the number of hot data records is 100 million. pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 8 -j 8 -T 120 -D scale=1000 -D range=100000000
Note- The value of scale multiplied by 100,000: the number of test data records.
- Range: the number of hot data records.
- -c: the number of connections for tests. This number does not represent the maximum number of connections supported by this type of cluster. For more information, see Specifications and pricing.
- Perform read-only operations:
Test results of various types
Type | Number of test data records | Number of hot (active) data records | Read-only QPS | Read and write QPS |
---|---|---|---|---|
polar.pg.x8.4xlarge 32 cores, 256 GB |
1 billion | 100 million | 522160 | 294915 |
polar.pg.x8.4xlarge 32 cores, 256 GB |
1 billion | 500 million | 514143 | 282645 |
polar.pg.x8.4xlarge 32 cores, 256 GB |
1 billion | 1 billion | 493321 | 268473 |
polar.pg.x8.2xlarge 16 cores, 128 GB |
1 billion | 100 million | 256998 | 156330 |
polar.pg.x8.2xlarge 16 cores, 128 GB |
1 billion | 500 million | 253937 | 133125 |
polar.pg.x8.2xlarge 16 cores, 128 GB |
1 billion | 1 billion | 243326 | 115915 |
polar.pg.x8.xlarge 8 cores, 64 GB |
1 billion | 100 million | 159323 | 71820 |
polar.pg.x8.xlarge 8 cores, 64 GB |
1 billion | 500 million | 155498 | 58140 |
polar.pg.x8.xlarge 8 cores, 64 GB |
1 billion | 1 billion | 152735 | 58555 |
polar.pg.x4.xlarge 8 cores, 32 GB |
1 billion | 100 million | 129323 | 64235 |
polar.pg.x4.xlarge 8 cores, 32 GB |
1 billion | 500 million | 115498 | 53682 |
polar.pg.x4.xlarge 8 cores, 32 GB |
1 billion | 1 billion | 102735 | 51555 |
polar.pg.x4.large 4 cores, 16 GB |
500 million | 100 million | 75729 | 48648 |
polar.pg.x4.large 4 cores, 16 GB |
500 million | 500 million | 63818 | 43343 |
polar.pg.x4.medium 2 cores, 8 GB |
100 million | 50 million | 34386 | 21383 |
pg.x8.medium.2 2 cores, 16 GB, and 250 GB |
100 million | 100 million | 33752 | 15974 |
Comparison with ApsaraDB RDS for PostgreSQL
- The test uses a non-standard PolarDB for PostgreSQL specification. The memory of the PolarDB for PostgreSQL cluster is the same as that of a dedicated ApsaraDB RDS for PostgreSQL cluster, to simplify comparing both clusters.
- The following table lists the instance types of ApsaraDB RDS for PostgreSQL.
Type |
Estimated number of data records storable in the default storage space (dynamic resizing) |
Number of test data records | Number of hot (active) data records | Read-only QPS | Read and write QPS |
---|---|---|---|---|---|
pg.x4.4xlarge.2 32 cores, 128 GB, and 2 TB |
10 billion | 1 billion | 100 million | 462190 | 254915 |
pg.x4.4xlarge.2 32 cores, 128 GB, and 2 TB |
10 billion | 1 billion | 500 million | 463176 | 228440 |
pg.x4.4xlarge.2 32 cores, 128 GB, and 2 TB |
10 billion | 1 billion | 1 billion | 473321 | 200250 |
pg.x8.2xlarge.2 16 cores, 128 GB, and 2 TB |
10 billion | 1 billion | 100 million | 256998 | 156330 |
pg.x8.2xlarge.2 16 cores, 128 GB, and 2 TB |
10 billion | 1 billion | 500 million | 253937 | 133125 |
pg.x8.2xlarge.2 16 cores, 128 GB, and 2 TB |
10 billion | 1 billion | 1 billion | 243326 | 115915 |
pg.x8.xlarge.2 8 cores, 64 GB, and 1 TB |
5 billion | 1 billion | 100 million | 155014 | 71820 |
pg.x8.xlarge.2 8 cores, 64 GB, and 1 TB |
5 billion | 1 billion | 500 million | 159878 | 58140 |
pg.x8.xlarge.2 8 cores, 64 GB, and 1 TB |
5 billion | 1 billion | 1 billion | 152917 | 58555 |
pg.x8.large.2 4 cores, 32 GB, and 500 GB |
2.5 billion | 500 million | 100 million | 79429 | 45110 |
pg.x8.large.2 4 cores, 32 GB, and 500 GB |
2.5 billion | 500 million | 500 million | 76268 | 36375 |
pg.x8.medium.2 2 cores, 16 GB, and 250 GB |
1.25 billion | 100 million | 50 million | 49733 | 24520 |
pg.x8.medium.2 2 cores, 16 GB, and 250 GB |
1.25 billion | 100 million | 100 million | 44093 | 19880 |
- Type: the PolarDB for PostgreSQL instance type. Submit a ticket to modify the memory size of the PolarDB for PostgreSQL instance to that of ApsaraDB RDS for PostgreSQL instance.
- Estimated number of data records storable in the default storage space: the number of data records that can be stored in the default storage space of an instance type.
- Number of test data records: the number of data records for the test.
- Number of hot (active) data records: the number of records for querying and updating SQL statements in the test.
- Read-only QPS: the result of the read-only test, indicating the number of requests per second.
- Read and write QPS: the result of the read and write test, indicating the number of requests per second.

