PolarDB PostgreSQL Performance White Paper

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

Comparison with ApsaraDB RDS for PostgreSQL