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 ApsaraDB PolarDB PostgreSQL-compatible edition clusters.

Test environment

  • 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 should be in the same VPC.
  • Operating system: 64-bit version of CentOS 7.6
Note CentOS 6 does not support PostgreSQL 11.

Test metrics

  • Read-only queries per second (QPS)

    The number of SELECT commands run per second when read-only operations are performed on the database.

  • Read and write QPS

    The number of SELECT, INSERT, and UPDATE commands run per second when read/write operations are performed on the database.

Preparation

  • 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/10/redhat/rhel-7.8-x86_64/pgdg-redhat-repo-42.0-11.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 on the PolarDB console. For more information about how to configure cluster parameters, see Configure cluster parameters .
    • Other parameters cannot be configured directly on the console. You need to Submit a ticket to contact technical support.

    After you configure the parameters, restart your PolarDB cluster for the configuration to take effect.

Test methods

  1. 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 ApsaraDB PolarDB PostgreSQL-compatible edition clusters, see View or apply for an endpoint.
  2. 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
  3. 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.
      1. Run the vim rw.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)  
\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. Press the Esc key to exit the edit mode, and enter :wq to save the file and exit the editor.
  4. 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
100 million 78393.56 46281.85

polar.pg.x4.medium

2 cores, 8 GB

 100 million 50 million 26383.91 18983.55
100 million 27821.49 17986.46
Note
  • Specification code: the code of ApsaraDB PolarDB PostgreSQL-compatible edition 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.
4c8g4c16g8c32g8c64g16c128g32c256g64c512g88c710g