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

  1. 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.
  2. Run the following commands to configure environment variables:
    export PGHOST=<The internal endpoint of your primary ApsaraDB RDS for PostgreSQL instance>
    export PGPORT=<The internal port number of your primary ApsaraDB RDS for PostgreSQL instance>
    export PGDATABASE=postgres
    export PGUSER=<The username to log on to the target database>
    export PGPASSWORD=<The password to log on to the target database>
  3. 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;
  4. Test the read-only performance and read/write performance.
    • Run the following commands to test the read-only performance:
      rds.pg.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 240 -j 240 -T 120 -D scale=50000 -D range=100000000
      
      rds.pg.st.h43 with a total of 5 billion data records including 500 million hot data records:
      
      pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 240 -j 240 -T 120 -D scale=50000 -D range=500000000
      
      rds.pg.st.h43 with a total of 5 billion data records including 1 billion hot data records:
      
      pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 240 -j 240 -T 120 -D scale=50000 -D range=1000000000
      
      rds.pg.st.h43 with a total of 5 billion data records that are all hot data records:
      
      pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 240 -j 240 -T 120 -D scale=50000 -D range=5000000000
      
      pg.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 128 -j 128 -T 120 -D scale=10000 -D range=100000000
      
      pg.x4.4xlarge.2 with a total of 1 billion data records including 500 million hot data records:
      
      pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 128 -j 128 -T 120 -D scale=10000 -D range=500000000
      
      pg.x4.4xlarge.2 with a total of 1 billion data records that are all hot data records:
      
      pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 128 -j 128 -T 120 -D scale=10000 -D range=1000000000
      
      pg.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
      
      pg.x8.2xlarge.2 with a total of 1 billion data records including 500 million hot data records:
      
      pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=500000000
      
      pg.x8.2xlarge.2 with a total of 1 billion data records that are all hot data records:
      
      pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=1000000000
      
      pg.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
      
      pg.x8.xlarge.2 with a total of 1 billion data records including 500 million hot data records:
      
      pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=500000000
      
      pg.x8.xlarge.2 with a total of 1 billion data records that are all hot data records:
      
      pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=1000000000
      
      pg.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
      
      pg.x8.large.2 with a total of 500 million data records that are all hot data records:
      
      pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 16 -j 16 -T 120 -D scale=5000 -D range=500000000
      
      pg.x8.medium.2 with a total of 100 million data records including 50 million hot data records:
      
      pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 8 -j 8 -T 120 -D scale=1000 -D range=50000000
      
      pg.x8.medium.2 with a total of 100 million data records that are all hot data records:
      
      pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 8 -j 8 -T 120 -D scale=1000 -D range=100000000
    • Run the following commands to test the read/write performance:
      rds.pg.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 240 -j 240 -T 120 -D scale=50000 -D range=100000000
      
      rds.pg.st.h43 with a total of 5 billion data records including 500 million hot data records:
      
      pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 240 -j 240 -T 120 -D scale=50000 -D range=500000000
      
      rds.pg.st.h43 with a total of 5 billion data records including 1 billion hot data records:
      
      pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 240 -j 240 -T 120 -D scale=50000 -D range=1000000000
      
      rds.pg.st.h43 with a total of 5 billion data records that are all hot data records:
      
      pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 240 -j 240 -T 120 -D scale=50000 -D range=5000000000
      
      pg.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 128 -j 128 -T 120 -D scale=10000 -D range=100000000
      
      pg.x4.4xlarge.2 with a total of 1 billion data records including 500 million hot data records:
      
      pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 128 -j 128 -T 120 -D scale=10000 -D range=500000000
      
      pg.x4.4xlarge.2 with a total of 1 billion data records that are all hot data records:
      
      pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 128 -j 128 -T 120 -D scale=10000 -D range=1000000000
      
      pg.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
      
      pg.x8.2xlarge.2 with a total of 1 billion data records including 500 million hot data records:
      
      pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=500000000
      
      pg.x8.2xlarge.2 with a total of 1 billion data records that are all hot data records:
      
      pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=1000000000
      
      pg.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
      
      pg.x8.xlarge.2 with a total of 1 billion data records including 500 million hot data records:
      
      pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=500000000
      
      pg.x8.xlarge.2 with a total of 1 billion data records that are all hot data records:
      
      pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=1000000000
      
      pg.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
      
      pg.x8.large.2 with a total of 500 million data records that are all hot data records:
      
      pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 16 -j 16 -T 120 -D scale=5000 -D range=500000000
      
      pg.x8.medium.2 with a total of 100 million data records including 50 million hot data records:
      
      pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 8 -j 8 -T 120 -D scale=1000 -D range=50000000
      
      pg.x8.medium.2 with a total of 100 million data records that are all hot data records:
      
      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: 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 ApsaraDB RDS instance types.