PolarDB:Performance White Paper

Test environments

• All tests must be conducted in the China (Qingdao) region. Your PolarDB cluster must be located in the same zone as your ECS instance.
• ECS instance type: ecs.g5.16xlarge (64 vCPUs, 256 GiB)
• ECS storage: 200 GiB local SSDs
• Network type: virtual private cloud (VPC). Your PolarDB cluster and ECS instance are in the same VPC.
• OS: 64-bit version of CentOS 7.6
• Number of nodes in your PolarDB cluster: one primary node and one read-only node

Test metrics

• 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 SELECT, INSERT, and UPDATE statements executed per second when read/write operations are performed on the database.

Before you begin

• Install PostgreSQL 11.
  Run the following commands to install PostgreSQL 11 on your ECS instance.

  sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
  sudo yum install -y postgresql11

• Configure parameters for your PolarDB cluster.
  On your PolarDB cluster, configure the following parameters:

  log_statement = 'none'
  enable_hashjoin=off
  enable_mergejoin=off
  enable_bitmapscan=off

  Note You can configure only the log_statement parameter in the PolarDB console. For more information about how to configure cluster parameters, see Configure cluster parameters.

  After you configure the parameters, restart your PolarDB cluster for the configuration to take effect.

Test methods

1. Run the following commands to configure environment variables:

   export PGHOST=<The internal endpoint of your PolarDB cluster>
   export PGPORT=<The internal port number of your PolarDB cluster>
   export PGDATABASE=postgres
   export PGUSER=<The username used to log on to your PolarDB database>
   export PGPASSWORD=<The password of your PolarDB account>

   Note For more information about how to view the endpoints of PolarDB for PostgreSQL(Compatible with Oracle) clusters, see View endpoints and ports.
2. Initialize test data based on the size of the database that you want to test.
   • Run the following command to initialize 1 billion data records:

     /usr/pgsql-11/bin/pgbench -i -s 10000

   • Run the following command to initialize 500 million data records:

     /usr/pgsql-11/bin/pgbench -i -s 5000

   • Run the following command to initialize 100 million data records:

     /usr/pgsql-11/bin/pgbench -i -s 1000

3. Create test scripts for read-only operations and read/write operations.
   • Create a script to test read-only operations. Set the name of this script to ro.sql.
     1. Run the vim ro.sql command.
     2. Press the I key to enter the edit mode.
     3. In the editor, specify the following content:

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

     4. Press the Esc key to exit the edit mode, and enter :wq to save the file and exit the editor.
   • Create a script to test read/write operations. Set the name of this script to rw.sql.
     1. Run the vim rw.sql command.
     2. Press the I key to enter the edit mode.
     3. In the editor, specify the following content:

        \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. Press the Esc key to exit the edit mode, and enter :wq to save the file and exit the editor.
4. Run the following commands to test operations:
   • Run the following commands to test read-only operations:

     88C 710 GB(polar.o.x8.12xlarge)
     Total data volume: 1 billion records, hot data: 100 million records
     /usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 256 -j 128 -T 120 -D scale=10000 -D range=100000000
     Total data volume: 1 billion records, hot data: 500 million records
     /usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 256 -j 128 -T 120 -D scale=10000 -D range=500000000
     Total data volume: 1 billion records, hot data: 1 billion records
     /usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 256 -j 128 -T 120 -D scale=10000 -D range=1000000000
     
     64C 512 GB(polar.o.x8.8xlarge)
     Total data volume: 1 billion records, hot data: 100 million records
     /usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 256 -j 128 -T 120 -D scale=10000 -D range=100000000
     Total data volume: 1 billion records, hot data: 500 million records
     /usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 256 -j 128 -T 120 -D scale=10000 -D range=500000000
     Total data volume: 1 billion records, hot data: 1 billion records
     /usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 256 -j 128 -T 120 -D scale=10000 -D range=1000000000
     
     32C 256 GB(polar.o.x8.4xlarge)
     Total data volume: 1 billion records, hot data: 100 million records
     /usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 128 -j 128 -T 120 -D scale=10000 -D range=100000000
     Total data volume: 1 billion records, hot data: 500 million records
     /usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 128 -j 128 -T 120 -D scale=10000 -D range=500000000
     Total data volume: 1 billion records, hot data: 1 billion records
     /usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 128 -j 128 -T 120 -D scale=10000 -D range=1000000000
     
     16cC 128 GB(polar.o.x8.2xlarge)
     Total data volume: 1 billion records, hot data: 100 million records
     /usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=100000000
     Total data volume: 1 billion records, hot data: 500 million records
     /usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=500000000
     Total data volume: 1 billion records, hot data: 1 billion records
     /usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=1000000000
     
     8C 64 GB(polar.o.x8.xlarge)
     Total data volume: 1 billion records, hot data: 100 million records
     /usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 64 -j 32 -T 120 -D scale=10000 -D range=100000000
     Total data volume: 1 billion records, hot data: 500 million records
     /usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 64 -j 32 -T 120 -D scale=10000 -D range=500000000
     Total data volume: 1 billion records, hot data: 1 billion records
     /usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 64 -j 32 -T 120 -D scale=10000 -D range=1000000000
     
     8C 32 GB(polar.o.x4.xlarge)
     Total data volume: 1 billion records, hot data: 100 million records
     /usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 64 -j 32 -T 120 -D scale=10000 -D range=100000000
     Total data volume: 1 billion records, hot data: 500 million records
     /usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 64 -j 32 -T 120 -D scale=10000 -D range=500000000
     Total data volume: 1 billion records, hot data: 1 billion records
     /usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 64 -j 32 -T 120 -D scale=10000 -D range=1000000000
     
     4C 16 GB(polar.o.x4.large)
     Total data volume: 500 million records, hot data: 50 million records
     /usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 32 -j 32 -T 120 -D scale=5000 -D range=50000000
     Total data volume: 500 million records, hot data: 100 million records
     /usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 32 -j 32 -T 120 -D scale=5000 -D range=100000000
     
     2C 4 GB(polar.o.x4.medium)
     Total data volume: 100 million records, hot data: 50 million records
     /usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 16 -j 32 -T 120 -D scale=1000 -D range=50000000
     Total data volume: 100 million records, hot data: 100 million records
     /usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 16 -j 32 -T 120 -D scale=1000 -D range=100000000

   • Run the following commands to test read/write operations:

     88C 710 GB(polar.o.x8.12xlarge)
     Total data volume: 1 billion records, hot data: 100 million records
     /usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 256 -j 256 -T 120 -D scale=10000 -D range=100000000
     Total data volume: 1 billion records, hot data: 500 million records
     /usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 256 -j 256 -T 120 -D scale=10000 -D range=500000000
     Total data volume: 1 billion records, hot data: 1 billion records
     /usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 256 -j 256 -T 120 -D scale=10000 -D range=1000000000
     
     64C 512 GB(polar.o.x8.8xlarge)
     Total data volume: 1 billion records, hot data: 100 million records
     /usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 256 -j 256 -T 120 -D scale=10000 -D range=100000000
     Total data volume: 1 billion records, hot data: 500 million records
     /usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 256 -j 256 -T 120 -D scale=10000 -D range=500000000
     Total data volume: 1 billion records, hot data: 1 billion records
     /usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 256 -j 256 -T 120 -D scale=10000 -D range=1000000000
     
     32C 256 GB(polar.o.x8.4xlarge)
     Total data volume: 1 billion records, hot data: 100 million records
     /usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 128 -j 128 -T 120 -D scale=10000 -D range=100000000
     Total data volume: 1 billion records, hot data: 500 million records
     /usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 128 -j 128 -T 120 -D scale=10000 -D range=500000000
     Total data volume: 1 billion records, hot data: 1 billion records
     /usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 128 -j 128 -T 120 -D scale=10000 -D range=1000000000
     
     16C 128 GB(polar.o.x8.2xlarge)
     Total data volume: 1 billion records, hot data: 100 million records
     /usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=100000000
     Total data volume: 1 billion records, hot data: 500 million records
     /usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=500000000
     Total data volume: 1 billion records, hot data: 1 billion records
     /usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=1000000000
     
     8C 64 GB(polar.o.x8.xlarge)
     Total data volume: 1 billion records, hot data: 100 million records
     /usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=100000000
     Total data volume: 1 billion records, hot data: 500 million records
     /usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=500000000
     Total data volume: 1 billion records, hot data: 1 billion records
     /usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 64 -j 64 -T 120 -D scale=10000 -D range=1000000000
     
     8C 32 GB(polar.o.x4.xlarge)
     Total data volume: 1 billion records, hot data: 100 million records
     /usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=100000000
     Total data volume: 1 billion records, hot data: 500 million records
     /usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=500000000
     Total data volume: 1 billion records, hot data: 1 billion records
     /usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 32 -j 32 -T 120 -D scale=10000 -D range=1000000000
     
     4C 16 GB(polar.o.x4.large)
     Total data volume: 500 million records, hot data: 50 million records
     /usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 16 -j 16 -T 120 -D scale=5000 -D range=50000000
     Total data volume: 500 million records, hot data: 100 million records
     /usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 16 -j 16 -T 120 -D scale=5000 -D range=100000000
     
     2C 8 GB(polar.o.x4.medium)
     Total data volume: 100 million records, hot data: 50 million records
     /usr/pgsql-11/bin/pgbench -M prepared -v -r -P 1 -f ./rw.sql -c 8 -j 8 -T 120 -D scale=1000 -D range=50000000
     Total data volume: 100 million records, hot data: 100 million records
     /usr/pgsql-11/bin/pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 8 -j 8 -T 120 -D scale=1000 -D range=100000000

   Note

   • scale: The value of scale multiplied by 100,000 indicates the number of test data records.
   • range: specifies the number of hot data records.
   • -c: specifies the number of connections in the test. The value of this parameter does not indicate the maximum number of connections supported by this type of cluster. For more information, see Specifications of compute nodes.

Test results