Describes the steps to test the performance of RDS for PPAS instance.

  1. Before the test, submit a ticket to modify the postgresql.auto.conf files in your primary and secondary ApsaraDB RDS for PPAS instances. This topic uses PPAS 10 as an example.
    edb_redwood_date = on
edb_redwood_greatest_least = on
edb_redwood_strings = on
edb_redwood_raw_names = on
edb_stmt_level_tx = off
db_dialect = 'redwood'
optimizer_mode = choose
edb_early_lock_release = on
datestyle = 'iso, ymd'
default_with_oids = off    
default_with_rowids = off  
vacuum_cost_delay = 0 
bgwriter_delay = 10ms
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 10.0
effective_io_concurrency = 0
max_worker_processes = 128
max_parallel_workers_per_gather = 0
synchronous_commit = off
wal_compression = on
wal_writer_delay = 10ms
wal_writer_flush_after = 1MB
checkpoint_timeout = 30min
max_wal_size = 64GB    # 1/2 of the memory size for the current ApsaraDB RDS for PPAS instance. 
min_wal_size = 16GB    # 1/8 of the memory size for the current ApsaraDB RDS for PPAS instance. 
checkpoint_completion_target = 0.2
hot_standby_feedback = off
random_page_cost = 1.1
log_checkpoints = on
log_statement = 'ddl'
log_autovacuum_min_duration = 0
autovacuum_freeze_max_age = 1500000000
autovacuum_multixact_freeze_max_age = 1600000000
autovacuum_vacuum_cost_delay = 0ms
vacuum_freeze_table_age = 1450000000
vacuum_multixact_freeze_table_age = 1450000000
log_min_duration_statement=5s
  2. After modifying the configuration files, restart the primary and secondary ApsaraDB RDS for PPAS instances to make the configurations take effect.
  3. Initialize the test data based on the data size of the target database.
    • To initialize 5 billion data records, run the pgbench -i -s 50000 command.
    • To initialize 1 billion data records, run the pgbench -i -s 10000 command.
    • To initialize 500 million data records, run the pgbench -i -s 5000 command.
    • To initialize 100 million data records, run the pgbench -i -s 1000 command.
  4. Run the following commands to configure environment variables:
    export PGHOST=<The internal endpoint of your primary ApsaraDB RDS for PPAS instance>
export PGPORT=<The internal port number of your primary ApsaraDB RDS for PPAS instance>
export PGDATABASE=postgres
export PGUSER=<The username to log on to the target database in your primary ApsaraDB RDS for PPAS instance>
export PGPASSWORD=<The password to log on to the target database in your primary ApsaraDB RDS for PPAS instance>
  5. Create the scripts for testing read-only operations and read/write operations.
    • Create a script file named ro.sql to test read-only operations. The file needs to include the following code:
      \set aid random_gaussian(1, :range, 10.0) 
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
    • Create a script file named rw.sql to test read/write operations. The file needs to include the following code:
      \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;
  6. Test the read-only performance and read/write performance.
    • Run the following commands to test the read-only performance:
      rds.ppas.st.h43 with a total of 5 billion data records including 100 million hot data records:

pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 480 -j 480 -T 120 -D scale=50000 -D range=100000000

rds.ppas.st.h43 with a total of 5 billion data records including 500 million hot data records:

pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 480 -j 480 -T 120 -D scale=50000 -D range=500000000

rds.ppas.st.h43 with a total of 5 billion data records including 1 billion hot data records:

pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 480 -j 480 -T 120 -D scale=50000 -D range=1000000000

rds.ppas.st.h43 with a total of 5 billion data records that are all hot data records:

pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 480 -j 480 -T 120 -D scale=50000 -D range=5000000000

ppas.x8.4xlarge.2 with a total of 1 billion data records including 100 million hot data records:

pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 320 -j 320 -T 120 -D scale=10000 -D range=100000000

ppas.x8.4xlarge.2 with a total of 1 billion data records including 500 million hot data records:

pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 320 -j 320 -T 120 -D scale=10000 -D range=500000000

ppas.x8.4xlarge.2 with a total of 1 billion data records that are all hot data records:

pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 320 -j 320 -T 120 -D scale=10000 -D range=1000000000

ppas.x4.4xlarge.2 with a total of 1 billion data records including 100 million hot data records:

pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 320 -j 320 -T 120 -D scale=10000 -D range=100000000

ppas.x4.4xlarge.2 with a total of 1 billion data records including 500 million hot data records:

pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 320 -j 320 -T 120 -D scale=10000 -D range=500000000

ppas.x4.4xlarge.2 with a total of 1 billion data records that are all hot data records:

pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 320 -j 320 -T 120 -D scale=10000 -D range=1000000000

ppas.x8.2xlarge.2 with a total of 1 billion data records including 100 million hot data records:

pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=100000000

ppas.x8.2xlarge.2 with a total of 1 billion data records including 500 million hot data records:

pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=500000000

ppas.x8.2xlarge.2 with a total of 1 billion data records that are all hot data records:

pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=1000000000

ppas.x8.xlarge.2 with a total of 1 billion data records including 100 million hot data records:

pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=100000000

ppas.x8.xlarge.2 with a total of 1 billion data records including 500 million hot data records:

pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=500000000

ppas.x8.xlarge.2 with a total of 1 billion data records that are all hot data records:

pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=1000000000

ppas.x8.large.2 with a total of 500 million data records including 100 million hot data records:

pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 16 -j 16 -T 120 -D scale=5000 -D range=100000000

ppas.x8.large.2 with a total of 500 million data records that are all hot data records:

pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 16 -j 16 -T 120 -D scale=5000 -D range=500000000
    • Run the following commands to test the read/write performance:
      rds.ppas.st.h43 with a total of 5 billion data records including 100 million hot data records:

pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 480 -j 480 -T 120 -D scale=50000 -D range=100000000

rds.ppas.st.h43 with a total of 5 billion data records including 500 million hot data records:

pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 480 -j 480 -T 120 -D scale=50000 -D range=500000000

rds.ppas.st.h43 with a total of 5 billion data records including 1 billion hot data records:

pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 480 -j 480 -T 120 -D scale=50000 -D range=1000000000

rds.ppas.st.h43 with a total of 5 billion data records that are all hot data records:

pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 480 -j 480 -T 120 -D scale=50000 -D range=5000000000

ppas.x8.4xlarge.2 with a total of 1 billion data records including 100 million hot data records:

pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 320 -j 320 -T 120 -D scale=10000 -D range=100000000

ppas.x8.4xlarge.2 with a total of 1 billion data records including 500 million hot data records:

pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 320 -j 320 -T 120 -D scale=10000 -D range=500000000

ppas.x8.4xlarge.2 with a total of 1 billion data records that are all hot data records:

pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 320 -j 320 -T 120 -D scale=10000 -D range=1000000000

ppas.x4.4xlarge.2 with a total of 1 billion data records including 100 million hot data records:

pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 320 -j 320 -T 120 -D scale=10000 -D range=100000000

ppas.x4.4xlarge.2 with a total of 1 billion data records including 500 million hot data records:

pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 320 -j 320 -T 120 -D scale=10000 -D range=500000000

ppas.x4.4xlarge.2 with a total of 1 billion data records that are all hot data records:

pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 320 -j 320 -T 120 -D scale=10000 -D range=1000000000

ppas.x8.2xlarge.2 with a total of 1 billion data records including 100 million hot data records:

pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=100000000

ppas.x8.2xlarge.2 with a total of 1 billion data records including 500 million hot data records:

pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=500000000

ppas.x8.2xlarge.2 with a total of 1 billion data records that are all hot data records:

pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=1000000000

ppas.x8.xlarge.2 with a total of 1 billion data records including 100 million hot data records:

pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=100000000

ppas.x8.xlarge.2 with a total of 1 billion data records including 500 million hot data records:

pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=500000000

ppas.x8.xlarge.2 with a total of 1 billion data records that are all hot data records:

pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=1000000000

ppas.x8.large.2 with a total of 500 million data records including 100 million hot data records:

pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 16 -j 16 -T 120 -D scale=5000 -D range=100000000

ppas.x8.large.2 with a total of 500 million data records that are all hot data records:

pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 16 -j 16 -T 120 -D scale=5000 -D range=500000000
    Note
    • 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 be tested. This number does not represent the maximum number of connections supported by this instance type. For more information, see Primary instance types.