Test Environment

• Region: CN North-Beijing1
• AZ: AZ1

• ECS: generral computing | c3.2xlarge.2 | 8 vCPUs | 16 GB, CentOS7.4 64 bit image Bind an EIP to the ECS because additional compilation tools need to be installed on stress testing tools.
  Figure 1 ECS configuration
  

Test Tool

PostgreSQL provides a lightweight stress testing tool: pgbench. pgbench is a simple program for running benchmark tests on PostgreSQL. It runs the same sequence of SQL commands over and over, possibly in multiple concurrent database sessions.

1. Use a remote connection tool, such as PuTTY, to connect to the ECS through an EIP. For details, see Login Overview.
2. Download, compile, and install the PostgreSQL tool.

   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-redhat-repo-latest.noarch.rpm

   yum install -y postgresql11*

3. Configure environment variables for the postgres user.

   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

   export PGHOST=XXXXXX

   export PGPORT= XXXXXX

   export PGDATABASE=postgres

   export PGUSER= XXXXXX

   export PGPASSWORD= XXXXXX

   Set the values of PGHOST, PGPORT, PGUSER, and PGPASSWORD to the IP address, port number, login username, and password of RDS, respectively.

Test Procedure

Replace the database name, connection IP address, and user password based on the site requirements.

1. Modify RDS instance parameters and then reboot the PostgreSQL DB instance for the modification to take effect.

   Table 1 Parameter configuration

   Synchronize Model	Asynchronous
   wal_compression	on
   bgwriter_delay	500
   bgwriter_flush_after	256
   bgwriter_lru_maxpages	1000
   bgwriter_lru_multiplier	10
   checkpoint_timeout	1800
   random_page_cost	1.1
   max_wal_size	1/2 of the memory size of the PostgreSQL DB instance (change it based on the site requirements)
   min_wal_size	1/8 of the memory size of the PostgreSQL DB instance (change it based on the site requirements)
   shared_buffers	3/5 of the memory size of the PostgreSQL DB instance (change it based on the site requirements)

2. Prepare for the test.
   1. Create a test database.

      createdb ${dataname}

   2. Initialize the test data based on the size of the destination database.
      • To initialize 5 billion of data: pgbench -i -s 50000
      • To initialize 10 billion of data: pgbench -i -s 10000
      • To initialize 0.5 billion of data: pgbench -i -s 5000
      • To initialize 0.1 million of data: pgbench -i -s 1000
   3. Create read-only and read/write test scripts.

      Create the read-only script ro.sql:

      \set aid random_gaussian(1, :range, 10.0)
      SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

      Create the read/write script rw.sql:

      \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;

3. Perform the test.

   Table 2 Test methods

   Instance Class	Command
   2 vCPUs | 8 GB	Read-only: pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 8 -j 8 -T 120 -D scale=1000 -D range=50000000 pgbench pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 8 -j 8 -T 120 -D scale=1000 -D range=100000000 pgbench Read/Write: pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 8 -j 8 -T 120 -D scale=1000 -D range=50000000 pgbench pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 8 -j 8 -T 120 -D scale=1000 -D range=100000000 pgbench
   2 vCPUs | 16 GB	Read-only: pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 8 -j 8 -T 120 -D scale=1000 -D range=50000000 pgbench pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 8 -j 8 -T 120 -D scale=1000 -D range=100000000 pgbench Read/Write: pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 8 -j 8 -T 120 -D scale=1000 -D range=50000000 pgbench pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 8 -j 8 -T 120 -D scale=1000 -D range=100000000 pgbench
   4 vCPUs | 16 GB	Read-only: pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 16 -j 16 -T 120 -D scale=5000 -D range=100000000 pgbench pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 16 -j 16 -T 120 -D scale=5000 -D range=500000000 pgbench Read/Write: pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 16 -j 16 -T 120 -D scale=5000 -D range=100000000 pgbench pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 16 -j 16 -T 120 -D scale=5000 -D range=500000000 pgbench
   4 vCPUs | 32 GB	Read-only: pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 16 -j 16 -T 120 -D scale=5000 -D range=100000000 pgbench pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 16 -j 16 -T 120 -D scale=5000 -D range=500000000 pgbench Read/Write: pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 16 -j 16 -T 120 -D scale=5000 -D range=100000000 pgbench pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 16 -j 16 -T 120 -D scale=5000 -D range=500000000 pgbench
   8 vCPUs | 32 GB	Read-only: pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=100000000 pgbench pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=500000000 pgbench pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=1000000000 pgbench Read/Write: pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=100000000 pgbench pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=500000000 pgbench pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=1000000000 pgbench
   8 vCPUs | 64 GB	Read-only: pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=100000000 pgbench pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=500000000 pgbench pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=1000000000 pgbench Read/Write: pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=100000000 pgbench pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=500000000 pgbench pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=1000000000 pgbench
   16 vCPUs | 64 GB	Read-only: pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=100000000 pgbench pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=500000000 pgbench pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=1000000000 pgbench Read/Write: pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=100000000 pgbench pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=500000000 pgbench pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=1000000000 pgbench
   16 vCPUs | 128 GB	Read-only: pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=100000000 pgbench pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=500000000 pgbench pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=1000000000 pgbench Read/Write: pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=100000000 pgbench pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=500000000 pgbench pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=1000000000 pgbench

   

   • Scale multiplied by 100,000: indicates the test data volume.
   • range: indicates the amount of active data.
   • -c: indicates the number of test connections. The number of test connections is not equal to the maximum number of connections supported by the instance class. The maximum number of connections is not fixed.

Test Metric

• TPS refers to the number of transactions executed per second by a database. Each transaction contains 18 SQL statements.