AnalyticDB:SSB benchmark performance testing

Prerequisites

• An AnalyticDB for PostgreSQL instance and an Elastic Compute Service (ECS) instance are created in the same virtual private cloud (VPC). For more information, see Create an instance and Create an instance on the Custom Launch tab.

• An Object Storage Service (OSS) bucket is created in the same region as the AnalyticDB for PostgreSQL instance. For more information, see Create a bucket.

• The private IP address of the ECS instance is added to an IP address whitelist of the AnalyticDB for PostgreSQL instance. For more information, see Configure an IP address whitelist.

• psql is installed on the ECS instance. For more information, see psql.

• An Alibaba Cloud account or a Resource Access Management (RAM) user that has the AliyunGPDBFullAccess, AliyunECSFullAccess, and AliyunOSSFullAccess permissions is used. An AccessKey ID and an AccessKey secret are created for the Alibaba Cloud account or the RAM user. For more information, see Overview of RAM users and Create an AccessKey pair.

Test environment

Generate test data

This test uses the toolkit provided by SSB to generate SSB data.

1. Complete the tool compilation.

   git clone https://github.com/vadimtk/ssb-dbgen.git
   cd ssb-dbgen
   make

2. Generate 100 GB of test data.

   FOR((i=1;i<=16;i++));
   DO
   ./dbgen -s 100 -S $i -C 16 -f -T l &
   IF [ $(($i%16)) -eq 0 ]
   THEN
           wait
           echo $i
   FI
   DONE
   
   ./dbgen -s 100 -T p
   ./dbgen -s 100 -T c
   ./dbgen -s 100 -T s

3. Use ossutil to upload the test data to an OSS bucket. For information about how to install and use the ossutil tool, see ossutil 1.0.

   ./ossutil64 cp -r <path of test data> <path of OSS bucket> --exclude "*.tbl*"

   Example:

   ./ossutil64 cp -r /mnt/dbqgen/ oss://testBucketName --exclude "*.tbl*"

4. Create data tables.

   -- Create test tables.
   CREATE TABLE customer      --The customer information table.
   (
     C_CUSTKEY       int,
     C_NAME          text,
     C_ADDRESS       text,
     C_CITY          text,
     C_NATION        text,
     C_REGION        text,
     C_PHONE         text,
     C_MKTSEGMENT    text
   )
   USING beam
   WITH(compresstype=auto, compresslevel=1)
   DISTRIBUTED BY(C_CUSTKEY)
   ORDER BY (C_CUSTKEY);
   
   CREATE TABLE lineorder     --The order details table.
   (
     LO_ORDERKEY             bigint,
     LO_LINENUMBER           int,
     LO_CUSTKEY              int,
     LO_PARTKEY              int,
     LO_SUPPKEY              int,
     LO_ORDERDATE            Date,
     LO_ORDERPRIORITY        text,
     LO_SHIPPRIORITY         int,
     LO_QUANTITY             int,
     LO_EXTENDEDPRICE        int,
     LO_ORDTOTALPRICE        int,
     LO_DISCOUNT             int,
     LO_REVENUE              int,
     LO_SUPPLYCOST           int,
     LO_TAX                  int,
     LO_COMMITDATE           Date,
     LO_SHIPMODE             text
   )
   USING beam
   WITH(compresstype=auto,compresslevel=1)
   DISTRIBUTED BY(LO_ORDERKEY)
   ORDER BY (LO_ORDERDATE, LO_ORDERKEY);
   
   CREATE TABLE part         --The part information table.
   (
     P_PARTKEY       int,
     P_NAME          text,
     P_MFGR          text,
     P_CATEGORY      text,
     P_BRAND         text,
     P_COLOR         text,
     P_TYPE          text,
     P_SIZE          int,
     P_CONTAINER     text
   )
   USING beam
   WITH(compresstype=auto,compresslevel=1)
   DISTRIBUTED BY(P_PARTKEY)
   ORDER BY (P_PARTKEY);
   
   CREATE TABLE supplier      --The supplier information table.
   (
     S_SUPPKEY       int,
     S_NAME          text,
     S_ADDRESS       text,
     S_CITY          text,
     S_NATION        text,
     S_REGION        text,
     S_PHONE         text
   )
   USING beam
   WITH(compresstype=auto,compresslevel=1)
   DISTRIBUTED BY(S_SUPPKEY)
   ORDER BY (S_SUPPKEY);

5. Create OSS foreign tables. Use the OSS foreign tables to import test data from OSS to the tables created in Step 4.

   • The bucket parameter specifies the OSS bucket in which the test data is stored. The endpoint parameter specifies the endpoint of the OSS bucket. For information about how to obtain the OSS bucket and the endpoint, see Obtain the OSS bucket information.

   • The ID and Key parameters specify the AccessKey ID and the AccessKey secret of your Alibaba Cloud account or RAM user. For information about how to obtain the AccessKey ID and the AccessKey secret, see Create an AccessKey pair.

   CREATE SERVER oss_serv
       FOREIGN DATA WRAPPER oss_fdw
       OPTIONS (
           endpoint 'oss-cn-hang****.aliyuncs.com', 
           bucket 'testBucketName'
     );
   CREATE USER MAPPING FOR PUBLIC SERVER oss_serv OPTIONS ( id 'STS.****************', key 'yourAccessKeySecret' );
   
   CREATE FOREIGN TABLE ext_customer
   (
     C_CUSTKEY       int,
     C_NAME          text,
     C_ADDRESS       text,
     C_CITY          text,
     C_NATION        text,
     C_REGION        text,
     C_PHONE         text,
     C_MKTSEGMENT    text
   )
   server oss_serv
       options (
           filepath 'ssb100g/customer.tbl',
           format 'csv',
           DELIMITER '|'
       );
   
   CREATE FOREIGN TABLE ext_lineorder
   (
     LO_ORDERKEY             bigint,
     LO_LINENUMBER           int,
     LO_CUSTKEY              int,
     LO_PARTKEY              int,
     LO_SUPPKEY              int,
     LO_ORDERDATE            Date,
     LO_ORDERPRIORITY        text,
     LO_SHIPPRIORITY         int,
     LO_QUANTITY             int,
     LO_EXTENDEDPRICE        int,
     LO_ORDTOTALPRICE        int,
     LO_DISCOUNT             int,
     LO_REVENUE              int,
     LO_SUPPLYCOST           int,
     LO_TAX                  int,
     LO_COMMITDATE           Date,
     LO_SHIPMODE             text
   )
   server oss_serv
       options (
           prefix 'ssb100g/lineorder_',
           format 'csv',
           DELIMITER '|'
       );
   
   CREATE FOREIGN TABLE ext_part
   (
     P_PARTKEY       int,
     P_NAME          text,
     P_MFGR          text,
     P_CATEGORY      text,
     P_BRAND         text,
     P_COLOR         text,
     P_TYPE          text,
     P_SIZE          int,
     P_CONTAINER     text
   )
   server oss_serv
       options (
           filepath 'ssb100g/part.tbl',
           format 'csv',
           DELIMITER '|'
       );
   
   CREATE FOREIGN TABLE ext_supplier
   (
     S_SUPPKEY       int,
     S_NAME          text,
     S_ADDRESS       text,
     S_CITY          text,
     S_NATION        text,
     S_REGION        text,
     S_PHONE         text
   )
   server oss_serv
       options (
           filepath 'ssb100g/supplier.tbl',
           format 'csv',
           DELIMITER '|'
       );
   
   --Import data.
   INSERT INTO part SELECT * FROM ext_part;
   INSERT INTO supplier SELECT * FROM ext_supplier;
   INSERT INTO customer SELECT * FROM ext_customer;
   INSERT INTO lineorder SELECT * FROM ext_lineorder;
   
   --Delete foreign tables.
   DROP EXTERNAL TABLE ext_part;
   DROP EXTERNAL TABLE ext_supplier;
   DROP EXTERNAL TABLE ext_customer;
   DROP EXTERNAL TABLE ext_lineorder;

6. Create a table named lineorder_flat by joining the preceding four data tables.

   CREATE TABLE lineorder_flat
   (
     LO_ORDERKEY             bigint NOT NULL,
     LO_LINENUMBER           int NOT NULL,
     LO_CUSTKEY              int NOT NULL,
     LO_PARTKEY              int NOT NULL,
     LO_SUPPKEY              int NOT NULL,
     LO_ORDERDATE            Date NOT NULL,
     LO_ORDERPRIORITY        text NOT NULL,
     LO_SHIPPRIORITY         int NOT NULL,
     LO_QUANTITY             int NOT NULL,
     LO_EXTENDEDPRICE        int NOT NULL,
     LO_ORDTOTALPRICE        int NOT NULL,
     LO_DISCOUNT             int NOT NULL,
     LO_REVENUE              int NOT NULL,
     LO_SUPPLYCOST           int NOT NULL,
     LO_TAX                  int NOT NULL,
     LO_COMMITDATE           Date NOT NULL,
     LO_SHIPMODE             text NOT NULL,
     C_NAME                  text NOT NULL,
     C_ADDRESS               text NOT NULL,
     C_CITY                  text NOT NULL,
     C_NATION                text NOT NULL,
     C_REGION                text NOT NULL,
     C_PHONE                 text NOT NULL,
     C_MKTSEGMENT            text NOT NULL,
     S_NAME                  text NOT NULL,
     S_ADDRESS               text NOT NULL,
     S_CITY                  text NOT NULL,
     S_NATION                text NOT NULL,
     S_REGION                text NOT NULL,
     S_PHONE                 text NOT NULL,
     P_NAME                  text NOT NULL,
     P_MFGR                  text NOT NULL,
     P_CATEGORY              text NOT NULL,
     P_BRAND                 text NOT NULL,
     P_COLOR                 text NOT NULL,
     P_TYPE                  text NOT NULL,
     P_SIZE                  int NOT NULL,
     P_CONTAINER             text NOT NULL
   )
   USING beam
   WITH(compresstype=auto, compresslevel=1)
   DISTRIBUTED BY(LO_ORDERKEY)
   ORDER BY(LO_ORDERDATE, LO_ORDERKEY);
   
   INSERT INTO lineorder_flat
   SELECT
       l.LO_ORDERKEY AS LO_ORDERKEY,
       l.LO_LINENUMBER AS LO_LINENUMBER,
       l.LO_CUSTKEY AS LO_CUSTKEY,
       l.LO_PARTKEY AS LO_PARTKEY,
       l.LO_SUPPKEY AS LO_SUPPKEY,
       l.LO_ORDERDATE AS LO_ORDERDATE,
       l.LO_ORDERPRIORITY AS LO_ORDERPRIORITY,
       l.LO_SHIPPRIORITY AS LO_SHIPPRIORITY,
       l.LO_QUANTITY AS LO_QUANTITY,
       l.LO_EXTENDEDPRICE AS LO_EXTENDEDPRICE,
       l.LO_ORDTOTALPRICE AS LO_ORDTOTALPRICE,
       l.LO_DISCOUNT AS LO_DISCOUNT,
       l.LO_REVENUE AS LO_REVENUE,
       l.LO_SUPPLYCOST AS LO_SUPPLYCOST,
       l.LO_TAX AS LO_TAX,
       l.LO_COMMITDATE AS LO_COMMITDATE,
       l.LO_SHIPMODE AS LO_SHIPMODE,
       c.C_NAME AS C_NAME,
       c.C_ADDRESS AS C_ADDRESS,
       c.C_CITY AS C_CITY,
       c.C_NATION AS C_NATION,
       c.C_REGION AS C_REGION,
       c.C_PHONE AS C_PHONE,
       c.C_MKTSEGMENT AS C_MKTSEGMENT,
       s.S_NAME AS S_NAME,
       s.S_ADDRESS AS S_ADDRESS,
       s.S_CITY AS S_CITY,
       s.S_NATION AS S_NATION,
       s.S_REGION AS S_REGION,
       s.S_PHONE AS S_PHONE,
       p.P_NAME AS P_NAME,
       p.P_MFGR AS P_MFGR,
       p.P_CATEGORY AS P_CATEGORY,
       p.P_BRAND AS P_BRAND,
       p.P_COLOR AS P_COLOR,
       p.P_TYPE AS P_TYPE,
       p.P_SIZE AS P_SIZE,
       p.P_CONTAINER AS P_CONTAINER
   FROM lineorder AS l
   INNER JOIN customer AS c ON c.C_CUSTKEY = l.LO_CUSTKEY
   INNER JOIN supplier AS s ON s.S_SUPPKEY = l.LO_SUPPKEY
   INNER JOIN part AS p ON p.P_PARTKEY = l.LO_PARTKEY;

   After you execute the preceding statement, the system collects statistics for the created table in the background. You can start test queries after the statistics collection is complete. If the following statement does not return any results, it indicates that the statistics collection is complete.

   SELECT datname, query, state FROM pg_stat_activity 
   WHERE state <> 'idle' AND backend_type LIKE '%autovacuum%';

Test queries

\timing
--Q1.1
SELECT SUM(lo_extendedprice * lo_discount) AS revenue
FROM lineorder_flat
WHERE lo_orderdate >= '1993-01-01' AND lo_orderdate <= '1993-12-31' 
AND lo_discount BETWEEN 1 AND 3 AND lo_quantity < 25;
 
--Q1.2
SELECT SUM(lo_extendedprice * lo_discount) AS revenue 
FROM lineorder_flat
WHERE lo_orderdate >= '1994-01-01' AND lo_orderdate <= '1994-01-31' 
AND lo_discount BETWEEN 4 AND 6 AND lo_quantity BETWEEN 26 AND 35;
 
--Q1.3
SELECT SUM(lo_extendedprice * lo_discount) AS revenue
FROM lineorder_flat
WHERE extract(week FROM lo_orderdate) = 6 AND lo_orderdate >= '1994-01-01' 
AND lo_orderdate <= '1994-12-31' AND lo_discount BETWEEN 5 AND 7 
AND lo_quantity BETWEEN 26 AND 35;
 
--Q2.1
SELECT SUM(lo_revenue), extract(YEAR FROM lo_orderdate) AS YEAR, p_brand 
FROM lineorder_flat
WHERE p_category = 'MFGR#12' AND s_region = 'AMERICA'
GROUP BY YEAR, p_brand
ORDER BY YEAR, p_brand
LIMIT 100;
 
--Q2.2
SELECT SUM(lo_revenue), extract(YEAR FROM lo_orderdate) AS YEAR, p_brand 
FROM lineorder_flat
WHERE p_brand >= 'MFGR#2221' AND p_brand <= 'MFGR#2228' AND s_region = 'ASIA'
GROUP BY YEAR, p_brand
ORDER BY YEAR, p_brand
LIMIT 100;
 
--Q2.3
SELECT SUM(lo_revenue), extract(YEAR FROM lo_orderdate) AS YEAR, p_brand 
FROM lineorder_flat
WHERE p_brand = 'MFGR#2239' AND s_region = 'EUROPE'
GROUP BY YEAR, p_brand
ORDER BY YEAR, p_brand
LIMIT 100;
 
--Q3.1 
SELECT c_nation, s_nation, extract(YEAR FROM lo_orderdate) AS YEAR, 
SUM(lo_revenue) AS revenue 
FROM lineorder_flat
WHERE c_region = 'ASIA' AND s_region = 'ASIA' AND lo_orderdate >= '1992-01-01' 
AND lo_orderdate <= '1997-12-31'
GROUP BY c_nation, s_nation, YEAR
ORDER BY YEAR ASC, revenue DESC
LIMIT 100;
 
--Q3.2 
SELECT c_city, s_city, extract(YEAR FROM lo_orderdate) AS YEAR,
 SUM(lo_revenue) AS revenue
FROM lineorder_flat
WHERE c_nation = 'UNITED STATES' AND s_nation = 'UNITED STATES' 
AND lo_orderdate >= '1992-01-01' AND lo_orderdate <= '1997-12-31'
GROUP BY c_city, s_city, YEAR
ORDER BY YEAR ASC, revenue DESC
LIMIT 100;
 
--Q3.3
SELECT c_city, s_city, extract(YEAR FROM lo_orderdate) AS YEAR, 
SUM(lo_revenue) AS revenue
FROM lineorder_flat
WHERE c_city IN ( 'UNITED KI1' ,'UNITED KI5') 
AND s_city IN ( 'UNITED KI1' ,'UNITED KI5') 
AND lo_orderdate >= '1992-01-01' AND lo_orderdate <= '1997-12-31'
GROUP BY c_city, s_city, YEAR
ORDER BY YEAR ASC, revenue DESC
LIMIT 100;
 
--Q3.4
SELECT c_city, s_city, extract(YEAR FROM lo_orderdate) AS YEAR, 
SUM(lo_revenue) AS revenue
FROM lineorder_flat
WHERE c_city IN ('UNITED KI1', 'UNITED KI5') 
AND s_city IN ( 'UNITED KI1', 'UNITED KI5') 
AND lo_orderdate >= '1997-12-01' AND lo_orderdate <= '1997-12-31'
GROUP BY c_city, s_city, YEAR
ORDER BY YEAR ASC, revenue DESC
LIMIT 100;
 
--Q4.1
SELECT EXTRACT(YEAR FROM lo_orderdate) AS YEAR, c_nation, 
SUM(lo_revenue - lo_supplycost) AS profit
FROM lineorder_flat
WHERE c_region = 'AMERICA' AND s_region = 'AMERICA' 
AND p_mfgr IN ('MFGR#1', 'MFGR#2')
GROUP BY YEAR, c_nation
ORDER BY YEAR ASC,c_nation ASC LIMIT 100;
 
--Q4.2
SELECT extract(YEAR FROM lo_orderdate) AS YEAR, s_nation, p_category, 
SUM(lo_revenue - lo_supplycost) AS profit
FROM lineorder_flat
WHERE c_region = 'AMERICA' AND s_region = 'AMERICA' 
AND lo_orderdate >= '1997-01-01' AND lo_orderdate <= '1998-12-31' 
AND p_mfgr IN ( 'MFGR#1' , 'MFGR#2')
GROUP BY YEAR, s_nation, p_category
ORDER BY YEAR ASC, s_nation ASC, p_category ASC
LIMIT 100;
 
--Q4.3
SELECT extract(YEAR FROM lo_orderdate) AS YEAR, s_city, p_brand, 
SUM(lo_revenue - lo_supplycost) AS profit
FROM lineorder_flat
WHERE s_nation = 'UNITED STATES' AND lo_orderdate >= '1997-01-01' 
AND lo_orderdate <= '1998-12-31' AND p_category = 'MFGR#14'
GROUP BY YEAR, s_city, p_brand
ORDER BY YEAR ASC, s_city ASC, p_brand ASC
LIMIT 100;

Test results

The following table describes the amount of time required to execute the test queries.