This topic describes how to use pgbench to test the peak performance of the primary node of a PolarDB 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 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 PostgreSQL cluster and an ApsaraDB RDS for PostgreSQL cluster to compare their performance.

    After you modify the configurations, restart the PolarDB PostgreSQL cluster for the configurations to take effect.

Test procedure

  1. 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
  2. Run the following commands to configure environment variables:
    export PGHOST=<Private endpoint of the primary node of the PolarDB PostgreSQL cluster>
    export PGPORT=<Private port of the primary node of the PolarDB PostgreSQL cluster>
    export PGDATABASE=postgres
    export PGUSER=<Username to log on to the PolarDB PostgreSQL database>
    export PGPASSWORD=<User password to log on to the PolarDB PostgreSQL database>
  3. 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;
  4. 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.

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

Note
  • The test uses a non-standard PolarDB PostgreSQL specification. The memory of the PolarDB 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
Note
  • Type: the PolarDB PostgreSQL instance type. Submit a ticket to modify the memory size of the PolarDB 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.
Figure 1. PostgreSQL performance comparison between PolarDB PostgreSQL and ApsaraDB RDS for PostgreSQL instances (with the same CPU and memory) in read-only mode
只读
Figure 2. PostgreSQL performance comparison between PolarDB PostgreSQL and ApsaraDB RDS for PostgreSQL instances (with the same CPU and memory) in read and write mode
读写