The transaction processing of AnalyticDB for PostgreSQL V6.0 is greatly improved over AnalyticDB for PostgreSQL V4.3. This topic describes the test method of the transaction processing capability of an AnalyticDB for PostgreSQL V6.0 instance by using TPC-B.

Introduction

TPC Benchmark B (TPC-B) is approved by the Transaction Processing Performance Council (TPC) to measure the number of concurrent transactions a system can process per second. TPC-B does not simulate a specific transaction scenario in real life in the same way as TPC-C does. The non-semantic transactions are composed of simple SQL statements to perform insert, update, and query operations on large tables and small tables. Transactions are not separated by intervals to simulate human behaviors as TPC-C does. Therefore, TPC-B is used as a database stress test regarding transaction performance. TCP-B uses a performance metric Transactions per Second (TPS) to measure the number of transactions processed per second.

Test environment

  • Test data

Use open source pgbench of PostgreSQL to generate test data. Set the fill factor to 100 and scale factor to 11424. The following table displays the number of data entries in each table.

Table name Data entries
pgbench_accounts 1142400000
pgbench_branches 11424
pgbench_history 0
pgbench_tellers 114240
  • Test instance

Use an AnalyticDB for PostgreSQL V6.0 instance that contains 16 compute nodes. Each node contains four CPU cores, 32 GB of memory, and uses the high-performance SSD storage. For more information about instance specifications, see Instance specifications. Create an ECS instance within the same region and VPC as the AnalyticDB for PostgreSQL instance. Run pgbench on the ECS instance to generate TPC-B load and send requests to the AnalyticDB for PostgreSQL instance.

  • Instance parameters

To obtain high transaction processing performance, execute the following statements to modify related parameters. If you cannot modify certain parameters, contact the AnalyticDB for PostgreSQL personnel on duty.

SQL statement Description
set optimizer = off Disable the Orca optimizer used for analytical processing scenarios to ensure higher transaction processing performance.
set shared_buffers = 8GB Increase the memory size used for shared buffers to 8 GB. You must restart the instance to validate the modification.
set wal_buffers = 256MB Increase the memory size used for write-ahead logging (WAL) data that has not been written to disk to 256 MB. You must restart the instance to validate the modification.
set log_statement = none Disable SQL statement logging.
set random_page_cost = 10 Reduce the cost of random access to 10 so that the system prefers index scans during queries.
set gp_resqueue_priority = off

set resource_scheduler = off 

Disable the resource queue of the AnalyticDB for PostgreSQL instance. You must restart the instance to validate the modification.

Test results

The following figure shows the performance of the AnalyticDB for PostgreSQL V6.0 instance under different concurrency levels. The X axis indicates the concurrency level and the Y axis indicates the performance (unit: TPS). pgbench is also used to test the performance in read-only, update-only, and insert-only scenarios. The following figures display the test performance. The TPC-B performance of the instance can reach 5,923 TPS, the read-only performance can reach 150,084 TPS, the update-only performance can reach 31,023 TPS, and the insert-only performance can reach 60,367 TPS.

  • TPC-B performance
Concurrency 1 8 16 32 48 64 96 128 168 192
TPS 199 1481 2031 2568 2795 3236 3622 5923 5037 5211
RT(ms) 5.02 5.4 7.88 12.5 17.2 19.8 26.5 21.6 33.3 36.8

  • Read-only performance
Concurrency 1 8 16 32 48 64 96 128 160 192
TPS 1875 14226 26784 51115 72767 92370 125708 143297 150084 139637
RT(ms) 0.53 0.56 0.6 0.63 0.66 0.69 0.76 0.89 1.07 1.37

  • Update-only performance
Concurrency 1 8 16 32 48 64 96 128 160 192
TPS 787 6486 12127 19333 21083 24199 31023 27464 24362 23600
RT(ms) 1.27 1.23 1.32 1.66 2.28 2.64 3.09 4.66 6.57 8.14

  • Insert-only performance
Concurrency 1 8 16 32 48 64 96 128 160 192
TPS 1497 11463 21771 40543 56887 59967 60367 59365 53375 48927
RT(ms) 0.69 0.7 0.73 0.79 0.84 1.07 1.59 2.16 3.0 3.9

Use pgbench for TPC-B

  • Download and install the tool

Use one of the following methods to install pgbench:

  1. Use the source code: Download the source code of the open source PostgreSQL database, find the pgbench directory, and compile pgbench to obtain the executable binary file.
  2. Use the binary package: Run the yum install postgresql-server command to install PostgreSQL. pgbench is automatically installed during this process.
  • Load data

Run the following command to generate and load data to the AnalyticDB for PostgreSQL instance.

# The -F option indicates the fill factor and -s indicates the scale factor.
./pgbench -i -F 100 -s 11424 -p port -h con_addr -U user_name db_name
  • Start the test

Test the TPC-B load

# Use -c to specify the number of clients of the database to be connected and -j to specify the number of threads used to create connections. We recommend that you set the same value for the two options.
# Use -T to specify the running time of the test. Unit: seconds.
./pgbench -h con_addr -p port -r -n -c 96 -j 96 -T 120 -f all.sql -U user_name db_name

The all.sql file contains the following content to generate the TPC-B load:

\set scale 11424
\set nbranches 1 * :scale
\set ntellers 10 * :scale
\set naccounts 100000 * :scale
\setrandom aid 1 :naccounts
\setrandom bid 1 :nbranches
\setrandom tid 1 :ntellers
\setrandom delta -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;

  • Test the read-only load
# Use -c to specify the number of clients of the database to be connected and -j to specify the number of threads used to create connections. We recommend that you set the same value for the two options.
# Use -T to specify the running time of the test. Unit: seconds.
./pgbench -h con_addr -p port -r -n -c 96 -j 96 -T 120 -f select.sql -U user_name db_name

The select.sql file contains the following content to generate the read-only load:

\set scale 11424
\set naccounts 100000 * :scale
\setrandom aid 1 :naccounts

SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
				
  • Test the update-only load
# Use -c to specify the number of clients of the database to be connected and -j to specify the number of threads used to create connections. We recommend that you set the same value for the two options.
# Use -T to specify the running time of the test. Unit: seconds.
./pgbench -h con_addr -p port -r -n -c 96 -j 96 -T 120 -f update.sql -U user_name db_name

The update.sql file contains the following content to generate the update-only load:

\set scale 11424
\set naccounts 100000 * :scale
\setrandom aid 1 :naccounts
\setrandom delta -5000 5000
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
  • Test the insert-only load
# Use -c to specify the number of clients of the database to be connected and -j to specify the number of threads used to create connections. We recommend that you set the same value for the two options.
# Use -T to specify the running time of the test. Unit: seconds.
./pgbench -h con_addr -p port -r -n -c 96 -j 96 -T 120 -f insert.sql -U user_name db_name

The insert.sql file contains the following content to generate the insert-only load:

\set scale 11424
\set nbranches 1 * :scale
\set ntellers 10 * :scale
\set naccounts 100000 * :scale
\setrandom aid 1 :naccounts
\setrandom bid 1 :nbranches
\setrandom tid 1 :ntellers
\setrandom delta -5000 5000
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);