Describes the steps to test the performance of RDS for PPAS instance.
- 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
- After modifying the configuration files, restart the primary and secondary ApsaraDB RDS for PPAS instances to make the configurations take effect.
- 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.
- To initialize 5 billion data records, run the
- 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>
- 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;
- Create a script file named ro.sql to test read-only operations. The file needs to
include the following code:
- 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.
- Run the following commands to test the read-only performance: