Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog Running TPC-H Benchmark on AnalyticDB for PostgreSQL

Running TPC-H Benchmark on AnalyticDB for PostgreSQL

This article introduces how to set up resources with Terraform, load 100 GB of data, and run the TPC-H benchmark on AnalyticDB for PostgreSQL.

Step 1: Use the Terraform script to initialize resources

Use the Terraform script to initialize resources and create an ECS instance and AnalyticDB for PostgreSQL.

The following figures show the command:

1
2
3

The created Terraform script is shown in the figure:

4

Initialize another hard disk and mount the hard disk as shown in the figure:

5

Step 2: Generate 100 GB of data through the script

Generate 100 GB of data by using the script provided on GitHub.

Script link: https://github.com/alibabacloud-howto/solution-adbpg-labs/blob/master/benchmark-tpc-h/data_gen_100gb.sh?spm=a2c65.11461447.0.0.5a0a76e6qrVOep&file=data_gen_100gb.sh

The script is shown in the following figures:

6
7

The following figure shows the concurrently generated data:

8

The generated data occupies 106 GB.

9

Step 3: Import data to OSS

Create a bucket to import data.

Execute the script to import data to OSS.

Script link: https://github.com/alibabacloud-howto/solution-adbpg-labs/blob/master/benchmark-tpc-h/upload_tpch_oss.sh

You must modify the following parameters:

export OSS_ENDPOINT=oss-cn-hongkong-internal.aliyuncs.com
export OSS_BUCKET=oss://adbpg-tpch-bechmark-hongkong
export AK_ID=<Access Key>
export AK_SECRET=<Access Secret>

10
11
12

View the imported data in OSS:

13
14

Step 4: Install the PostgreSQL client program on the ECS instance

Link: https://www.alibabacloud.com/help/en/analyticdb-for-postgresql/user-guide/psql?spm=a2c63.p38356.0.0.51fe4fa28PBD4C

15

PostgreSQL client.

wget https://help-static-aliyun-doc.aliyuncs.com/file-manage-files/en-US/20230202/kqvv/adbpg7_client_package.el7.x86_64.tar.gz

Switch to the bin directory and run the following command to log in to AnalyticDB for PostgreSQL, indicating that it functions normally. If not, the dependency package is missing:

There's a pitfall! ! ! ! Some operating systems lack the dependency library software.

Check whether the dependency library rpm -q compat-openssl10 is missing.
yum install -y compat-openssl10

16

The missing dependency package requires the following libraries to be installed:

17
18

Step 5: Use the script to create a table in AnalyticDB for PostgreSQL

19

Configure password-free login operations for AnalyticDB for PostgreSQL.

gp-3ns3xq7em6bifhoc0-master.gpdb.rds.aliyuncs.com:5432:adbpg:adbpg:N1cetest

20
21

Create a table

Script link: https://github.com/alibabacloud-howto/solution-adbpg-labs/blob/master/benchmark-tpc-h/tpch-ddl.sql

Script content:

22
23

Step 6: Use the script to load data from OSS to AnalyticDB for PostgreSQL

Script link: https://github.com/alibabacloud-howto/solution-adbpg-labs/blob/master/benchmark-tpc-h/load_tpch_oss_data.sql?spm=a2c65.11461447.0.0.5a0a76e6qrVOep&file=load_tpch_oss_data.sql

The script needs to be modified in the following parts:

24

Run the import script. It takes a long time to import data:

./psql -h<AnalyticDB PostgreSQL connection string> -Uadbpg adbpg -f /mnt/load_tpch_oss_data.sql

25

Step 7: View the data loaded from OSS

26
27

Step 8: Run the script to perform the benchmark query

Script link: https://github.com/alibabacloud-howto/solution-adbpg-labs/blob/master/benchmark-tpc-h/query.sh?spm=a2c65.11461447.0.0.5a0a76e6qrVOep&file=query.sh

The following parts need to be modified:

export ADB_PG_URL=
export ADB_PG_USER=adbpg

28

Download the SQL statements required for the query:

Link: https://github.com/alibabacloud-howto/solution-adbpg-labs/tree/master/benchmark-tpc-h/tpch_query

29
30

View the results generated by the benchmark query.

31
32

log.tar.gz

PostgreSQL Database Terraform Developers Tutorials AnalyticDB for PostgreSQL TPC-H
0 1 0
Share on

Read previous post:

Parallel DDL Accelerates the Creation of Secondary Indexes

Read next post:

Full Edition of GDN Performance Testing

ApsaraDB

514 posts | 166 followers

You may also like

Comments

ApsaraDB

514 posts | 166 followers

Related Products

More Posts by ApsaraDB

See All