Running 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:

The created Terraform script is shown in the figure:

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

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:

The following figure shows the concurrently generated data:

The generated data occupies 106 GB.

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>

View the imported data in OSS:

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

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

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

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

Configure password-free login operations for AnalyticDB for PostgreSQL.

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

Create a table

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

Script content:

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:

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

Step 7: View the data loaded from OSS

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

Download the SQL statements required for the query:

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

View the results generated by the benchmark query.

log.tar.gz

Community

Running TPC-H Benchmark on AnalyticDB for PostgreSQL

Step 1: Use the Terraform script to initialize resources

Step 2: Generate 100 GB of data through the script

Step 3: Import data to OSS

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

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

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

Step 7: View the data loaded from OSS

Step 8: Run the script to perform the benchmark query

Read previous post:

Read next post:

ApsaraDB

You may also like

Comments

ApsaraDB

Related Products

PolarDB for PostgreSQL

AnalyticDB for PostgreSQL

ApsaraDB RDS for PostgreSQL

Database for FinTech Solution