簡介：本文將會介紹在Hologres中如何基于TPCH數據集做性能測試，并提供測試結果參考，方便您進行產品規格選型。

背景信息

TPC-H（商業智能計算測試）是美國交易處理效能委員會（TPC,Transaction Processing Performance Council）組織制定的用來模擬決策支持類應用的一個測試集。目前在學術界和工業界普遍采用它來評價決策支持技術方面應用的性能。TPC-H 是根據真實的生產運行環境來建模，模擬了一套銷售系統的數據倉庫。其共包含 8 張表，數據量可設定從 1G~3T 不等。其基準測試共包含了22個查詢，主要評價指標各個查詢的響應時間，即從提交查詢到結果返回所需時間。其測試結果可綜合反映系統處理查詢時的能力。詳情參考TPCH 文檔。

數據集介紹

該數據集包含如下 8 張表，互相間的關系如下圖所示。

測試詳情

測試數據量說明

測試數據量會直接影響測試結果，TPC-H 的生成工具中使用 SF ( scale factor ) 控制生成數據的數據量的大小，1 SF 對應 1 GB。

注意：以上提及的數據量僅僅為原始數據的數據量，不包括索引等空間占用，所以準備環境時，需要預留更多的空間。

測試環境

本次測試使用了獨享實例（按量付費）的實例，由于僅為測試示意使用，所以計算資源配置選擇了8核32G。

測試場景

本測試場景主要包含3部分：

OLAP查詢場景測試，主要使用列存表，直接使用TPCH測試中的22條查詢；

Key/Value點查場景測試，主要使用行存表，針對orders使用行存表后，進行主鍵過濾的點查；

基礎環境準備

• 該步驟主要用于準備OLAP查詢場景和Key/Value點查場景所需的數據；

基礎環境準備

1. 創建 ECS 實例

登陸阿里云，創建一個 ECS 實例，用于數據生成、向 Hologres 導入數據、客戶端測試。建議規格：

• ecs.g6.4xlarge 規格
• CentOS 7.9 系統
• ESSD 數據盤，具體數據容量根據需要測試的數據量大小決定
• 建議 ECS 與 Hologres 實例用相同 Region 和 VPC 網絡

2. 創建 Hologres 實例

• 登陸阿里云，進入 Hologres 產品控制臺，點擊新增引擎實例
• 選擇配置，并填寫實例名稱，詳細說明請參考官方文檔。

3. 創建測試數據庫

• 在創建實例后，您需要登陸您創建的 Hologres 實例，創建一個數據庫，本測試中命名數據庫為tpch_1sf，詳細操作步驟請參考官方文檔

生成 TPC-H 數據

1. 準備數據生成工具

• 遠程鏈接 ECS 實例
• 更新所有庫

yum update

• 安裝 git

yum install git

• 安裝gcc

yum install gcc

• 下載 TPC-H 數據生成代碼

git clone https://github.com/gregrahn/tpch-kit.git

• 進入數據生成工具代碼目錄

cd tpch-kit/dbgen

• 編譯數據生成工具代碼

make

2. 生成數據

• 編譯成功后，您可以使用如下代碼查看代碼生成工具的相關參數。

./dbgen --help

• 本次測試僅生成 1 GB 數據，所以運行如下代碼生成數據。

./dbgen -vf -s 1 如您需要生成更多數據量的數據，可以調整 SF 的參數，例如您可以使用如下代碼生成 1 T 數據 ./dbgen -vf -s 1000

• 一般情況下，32CU 可以跑 TPCH SF10，256CU 可以跑 TPCH SF50
• 數據生成后，您可以使用如下代碼查看生成的文件。可以看到生成工具生成了 8 個數據文件，每個數據文件都對應一張數據集中的表。

ls | grep '.*.tbl'

OLAP查詢場景測試

準備數據

1. 創建表

• 由于本文主要使用 psql 進行數據導入操作，需要先在 ECS 中運行如下命令安裝 psql

yum install postgresql-server

• 安裝 psql 后，您可以使用如下命令登陸 Hologres 實例

PGUSER=<AccessID> PGPASSWORD=<AccessKey> psql -p <Port> -h <Endpoint> -d <Database>

• 使用psql連接Hologres后，您可以使用如下建表語句創建數據庫表

DROP TABLE IF EXISTS LINEITEM;BEGIN; CREATE TABLE LINEITEM (L_ORDERKEY INT NOT NULL,L_PARTKEY INT NOT NULL,L_SUPPKEY INT NOT NULL,L_LINENUMBER INT NOT NULL,L_QUANTITY DECIMAL(15,2) NOT NULL,L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,L_DISCOUNT DECIMAL(15,2) NOT NULL,L_TAX DECIMAL(15,2) NOT NULL,L_RETURNFLAG TEXT NOT NULL,L_LINESTATUS TEXT NOT NULL,L_SHIPDATE TIMESTAMPTZ NOT NULL,L_COMMITDATE TIMESTAMPTZ NOT NULL,L_RECEIPTDATE TIMESTAMPTZ NOT NULL,L_SHIPINSTRUCT TEXT NOT NULL,L_SHIPMODE TEXT NOT NULL,L_COMMENT TEXT NOT NULL,PRIMARY KEY (L_ORDERKEY,L_LINENUMBER) ); CALL set_table_property('LINEITEM', 'clustering_key', 'L_SHIPDATE,L_ORDERKEY'); CALL set_table_property('LINEITEM', 'segment_key', 'L_SHIPDATE'); CALL set_table_property('LINEITEM', 'distribution_key', 'L_ORDERKEY'); CALL set_table_property('LINEITEM', 'bitmap_columns', 'L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_LINENUMBER,L_RETURNFLAG,L_LINESTATUS,L_SHIPINSTRUCT,L_SHIPMODE,L_COMMENT'); CALL set_table_property('LINEITEM', 'dictionary_encoding_columns', 'L_RETURNFLAG,L_LINESTATUS,L_SHIPINSTRUCT,L_SHIPMODE,L_COMMENT'); CALL set_table_property('LINEITEM', 'time_to_live_in_seconds', '31536000'); COMMIT;DROP TABLE IF EXISTS ORDERS;BEGIN; CREATE TABLE ORDERS (O_ORDERKEY INT NOT NULL PRIMARY KEY,O_CUSTKEY INT NOT NULL,O_ORDERSTATUS TEXT NOT NULL,O_TOTALPRICE DECIMAL(15,2) NOT NULL,O_ORDERDATE timestamptz NOT NULL,O_ORDERPRIORITY TEXT NOT NULL,O_CLERK TEXT NOT NULL,O_SHIPPRIORITY INT NOT NULL,O_COMMENT TEXT NOT NULL ); CALL set_table_property('ORDERS', 'segment_key', 'O_ORDERDATE'); CALL set_table_property('ORDERS', 'distribution_key', 'O_ORDERKEY'); CALL set_table_property('ORDERS', 'colocate_with', 'LINEITEM'); CALL set_table_property('ORDERS', 'bitmap_columns', 'O_ORDERKEY,O_CUSTKEY,O_ORDERSTATUS,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY,O_COMMENT'); CALL set_table_property('ORDERS', 'dictionary_encoding_columns', 'O_ORDERSTATUS,O_ORDERPRIORITY,O_CLERK,O_COMMENT'); CALL set_table_property('ORDERS', 'time_to_live_in_seconds', '31536000'); COMMIT;DROP TABLE IF EXISTS PARTSUPP;BEGIN; CREATE TABLE PARTSUPP (PS_PARTKEY INT NOT NULL,PS_SUPPKEY INT NOT NULL,PS_AVAILQTY INT NOT NULL,PS_SUPPLYCOST DECIMAL(15,2) NOT NULL,PS_COMMENT TEXT NOT NULL,PRIMARY KEY(PS_PARTKEY,PS_SUPPKEY) ); CALL set_table_property('PARTSUPP', 'distribution_key', 'PS_PARTKEY'); CALL set_table_property('PARTSUPP', 'colocate_with', 'LINEITEM'); CALL set_table_property('PARTSUPP', 'bitmap_columns', 'PS_PARTKEY,PS_SUPPKEY,PS_AVAILQTY,PS_COMMENT'); CALL set_table_property('PARTSUPP', 'dictionary_encoding_columns', 'PS_COMMENT'); CALL set_table_property('PARTSUPP', 'time_to_live_in_seconds', '31536000'); COMMIT;DROP TABLE IF EXISTS PART;BEGIN; CREATE TABLE PART (P_PARTKEY INT NOT NULL PRIMARY KEY,P_NAME TEXT NOT NULL,P_MFGR TEXT NOT NULL,P_BRAND TEXT NOT NULL,P_TYPE TEXT NOT NULL,P_SIZE INT NOT NULL,P_CONTAINER TEXT NOT NULL,P_RETAILPRICE DECIMAL(15,2) NOT NULL,P_COMMENT TEXT NOT NULL ); CALL set_table_property('PART', 'distribution_key', 'P_PARTKEY'); CALL set_table_property('PART', 'colocate_with', 'LINEITEM'); CALL set_table_property('PART', 'bitmap_columns', 'P_PARTKEY,P_SIZE,P_NAME,P_MFGR,P_BRAND,P_TYPE,P_CONTAINER,P_COMMENT'); CALL set_table_property('PART', 'dictionary_encoding_columns', 'P_NAME,P_MFGR,P_BRAND,P_TYPE,P_CONTAINER,P_COMMENT'); CALL set_table_property('PART', 'time_to_live_in_seconds', '31536000'); COMMIT;DROP TABLE IF EXISTS CUSTOMER; BEGIN; CREATE TABLE CUSTOMER (C_CUSTKEY INT NOT NULL PRIMARY KEY,C_NAME TEXT NOT NULL,C_ADDRESS TEXT NOT NULL,C_NATIONKEY INT NOT NULL,C_PHONE TEXT NOT NULL,C_ACCTBAL DECIMAL(15,2) NOT NULL,C_MKTSEGMENT TEXT NOT NULL,C_COMMENT TEXT NOT NULL ); CALL set_table_property('CUSTOMER', 'distribution_key', 'C_CUSTKEY'); CALL set_table_property('CUSTOMER', 'colocate_with', 'LINEITEM'); CALL set_table_property('CUSTOMER', 'bitmap_columns', 'C_CUSTKEY,C_NATIONKEY,C_NAME,C_ADDRESS,C_PHONE,C_MKTSEGMENT,C_COMMENT'); CALL set_table_property('CUSTOMER', 'dictionary_encoding_columns', 'C_NAME,C_ADDRESS,C_PHONE,C_MKTSEGMENT,C_COMMENT'); CALL set_table_property('CUSTOMER', 'time_to_live_in_seconds', '31536000'); COMMIT;DROP TABLE IF EXISTS SUPPLIER;BEGIN; CREATE TABLE SUPPLIER (S_SUPPKEY INT NOT NULL PRIMARY KEY,S_NAME TEXT NOT NULL,S_ADDRESS TEXT NOT NULL,S_NATIONKEY INT NOT NULL,S_PHONE TEXT NOT NULL,S_ACCTBAL DECIMAL(15,2) NOT NULL,S_COMMENT TEXT NOT NULL ); CALL set_table_property('SUPPLIER', 'distribution_key', 'S_SUPPKEY'); CALL set_table_property('SUPPLIER', 'colocate_with', 'LINEITEM'); CALL set_table_property('SUPPLIER', 'bitmap_columns', 'S_SUPPKEY,S_NAME,S_ADDRESS,S_NATIONKEY,S_PHONE,S_COMMENT'); CALL set_table_property('SUPPLIER', 'dictionary_encoding_columns', 'S_NAME,S_ADDRESS,S_PHONE,S_COMMENT'); CALL set_table_property('SUPPLIER', 'time_to_live_in_seconds', '31536000'); COMMIT;DROP TABLE IF EXISTS NATION;BEGIN; CREATE TABLE NATION(N_NATIONKEY INT NOT NULL PRIMARY KEY,N_NAME text NOT NULL,N_REGIONKEY INT NOT NULL,N_COMMENT text NOT NULL ); CALL set_table_property('NATION', 'distribution_key', 'N_NATIONKEY'); CALL set_table_property('NATION', 'colocate_with', 'LINEITEM'); CALL set_table_property('NATION', 'bitmap_columns', 'N_NATIONKEY,N_NAME,N_REGIONKEY,N_COMMENT'); CALL set_table_property('NATION', 'dictionary_encoding_columns', 'N_NAME,N_COMMENT'); CALL set_table_property('NATION', 'time_to_live_in_seconds', '31536000'); COMMIT;DROP TABLE IF EXISTS REGION;BEGIN; CREATE TABLE REGION (R_REGIONKEY INT NOT NULL PRIMARY KEY,R_NAME TEXT NOT NULL,R_COMMENT TEXT ); CALL set_table_property('REGION', 'distribution_key', 'R_REGIONKEY'); CALL set_table_property('REGION', 'colocate_with', 'LINEITEM'); CALL set_table_property('REGION', 'bitmap_columns', 'R_REGIONKEY,R_NAME,R_COMMENT'); CALL set_table_property('REGION', 'dictionary_encoding_columns', 'R_NAME,R_COMMENT'); CALL set_table_property('REGION', 'time_to_live_in_seconds', '31536000'); COMMIT;

• 創建完畢后，您能在 psql 中使用如下代碼查看是否創建成功

tpch_1sf=# \dt

• 若成功，現實效果如下

tpch_1sf=# \dtList of relationsSchema | Name | Type | Owner --------+----------+-------+--------------------public | customer | table | tpch_1sf_developerpublic | lineitem | table | tpch_1sf_developerpublic | nation | table | tpch_1sf_developerpublic | orders | table | tpch_1sf_developerpublic | part | table | tpch_1sf_developerpublic | partsupp | table | tpch_1sf_developerpublic | region | table | tpch_1sf_developerpublic | supplier | table | tpch_1sf_developer (8 rows)

2. 導入數據

• 本測試方案主要使用 COPY FROM STDIN?的方式導入數據詳細可以參考官方文檔。此處會將此前生成的 tbl 數據文件導入 Hologres 中創建的表中。
• 您可以在數據生成工具的目錄中參考如下 shell腳本導入數據

for i in `ls *.tbl`; doecho $i;name=`echo $i| cut -d'.' -f1`;PGUSER=<AccessID> PGPASSWORD=<AccessKey> psql -p <Port> -h <Endpoint> -d <Database> -c "COPY $name from stdin with delimiter '|' csv;" < $i; done

• 至此您已完成數據導入

3. 收集統計信息

• 為了更好的執行查詢，可以在 psql 中使用如下語句，使 Hologres 收集各張表特征信息。

vacuum region; vacuum nation; vacuum supplier; vacuum customer; vacuum part; vacuum partsupp; vacuum orders; vacuum lineitem;analyze nation; analyze region; analyze lineitem; analyze orders; analyze customer; analyze part; analyze partsupp; analyze supplier;

執行查詢

• 為了方便統計查詢信息，需要使用pgbench工具，您可以使用如下命令安裝pgbench（如果測試機上已有pgbench，請確保版本大于9.6以上，最好大版本是13以上，否則以下測試會遇到各種不兼容）

yum install postgresql-contrib

• 為了方便查詢，您可以直接通過以下連接，下載所需的22條SQL

tpch_data_tpch_query.zip

• 然后上傳至ECS
• 進入ECS，并進入上傳文件的目錄，使用如下shell命令解壓縮文件

unzip tpch_data_tpch_query

• 至此，您已經完成了準備工作，即可使用pgbench進行測試，您可以使用如下命令執行單條查詢

PGUSER=<AccessID> PGPASSWORD=<AccessKey> pgbench -h <Endpoint> -p <Port> -d <Database> -c <Client_Num> -t <Query_Num> -n -f xxx.sql

• 參數解釋

配置項參數說明

-h	Hologres實例的endpoint	在Hologres管控臺查看
-p	Hologres實例的端口地址	在Hologres管控臺查看
-d	Hologres指定實例中的數據庫名
-c	客戶端數目(并發度)	示例：1，由于該測試僅測試查詢性能，不測試并發，所以并發度置為1即可
-t	每個客戶端需要執行的壓測query數目	50
-f	壓測的sql	示例：6.sql

• 也可以直接執行如下 shell?腳本，直接批量執行22條查詢，并將結果輸出到文件hologres_tpch_test.out中

rm -f hologres_tpch_test.out echo `date +"%Y-%m-%d %H:%M:%S"` begin >> ./hologres_tpch_test.out for i in {1..22} doPGUSER=<AccessID> PGPASSWORD=<AccessKey> pgbench -h <Endpoint> -p <Port> -d <Database> -c <Client_Num> -t <Query_Num> -n -f ./tpch_data_tpch_query/${i}.sql >> ./hologres_tpch_test.out done

• 查看hologres_tpch_test.out即可得到查詢結果，樣例如下

  • transaction type：說明了執行的具體的SQL文件
  • latency average：記錄了對應SQL文件的3次查詢的平均時間

2021-03-23 03:50:54 begin pghost: hgpostcn-cn-oew21c935002-cn-hangzhou.hologres.aliyuncs.com pgport: 80 nclients: 1 nxacts: 3 dbName: tpch_100 transaction type: ./tpch_data_tpch_query/1.sql scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 number of transactions per client: 3 number of transactions actually processed: 3/3 latency average = 76.936 ms tps = 12.997850 (including connections establishing) tps = 15.972757 (excluding connections establishing) ...

TPCH 22條查詢語句

Q1

selectl_returnflag,l_linestatus,sum(l_quantity) as sum_qty,sum(l_extendedprice) as sum_base_price,sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,avg(l_quantity) as avg_qty,avg(l_extendedprice) as avg_price,avg(l_discount) as avg_disc,count(*) as count_order fromlineitem wherel_shipdate <= date '1998-12-01' - interval '90' day group byl_returnflag,l_linestatus order byl_returnflag,l_linestatus;

Q2

selects_acctbal,s_name,n_name,p_partkey,p_mfgr,s_address,s_phone,s_comment frompart,supplier,partsupp,nation,region wherep_partkey = ps_partkeyand s_suppkey = ps_suppkeyand p_size = 15and p_type like '%BRASS'and s_nationkey = n_nationkeyand n_regionkey = r_regionkeyand r_name = 'EUROPE'and ps_supplycost = (selectmin(ps_supplycost)frompartsupp,supplier,nation,regionwherep_partkey = ps_partkeyand s_suppkey = ps_suppkeyand s_nationkey = n_nationkeyand n_regionkey = r_regionkeyand r_name = 'EUROPE') order bys_acctbal desc,n_name,s_name,p_partkey limit 100;

Q3

selectl_orderkey,sum(l_extendedprice * (1 - l_discount)) as revenue,o_orderdate,o_shippriority fromcustomer,orders,lineitem wherec_mktsegment = 'BUILDING'and c_custkey = o_custkeyand l_orderkey = o_orderkeyand o_orderdate < date '1995-03-15'and l_shipdate > date '1995-03-15' group byl_orderkey,o_orderdate,o_shippriority order byrevenue desc,o_orderdate limit 10;

Q4

selecto_orderpriority,count(*) as order_count fromorders whereo_orderdate >= date '1993-07-01'and o_orderdate < date '1993-07-01' + interval '3' monthand exists (select*fromlineitemwherel_orderkey = o_orderkeyand l_commitdate < l_receiptdate) group byo_orderpriority order byo_orderpriority;

Q5

selectn_name,sum(l_extendedprice * (1 - l_discount)) as revenue fromcustomer,orders,lineitem,supplier,nation,region wherec_custkey = o_custkeyand l_orderkey = o_orderkeyand l_suppkey = s_suppkeyand c_nationkey = s_nationkeyand s_nationkey = n_nationkeyand n_regionkey = r_regionkeyand r_name = 'ASIA'and o_orderdate >= date '1994-01-01'and o_orderdate < date '1994-01-01' + interval '1' year group byn_name order byrevenue desc;

Q6

selectsum(l_extendedprice * l_discount) as revenue fromlineitem wherel_shipdate >= date '1994-01-01'and l_shipdate < date '1994-01-01' + interval '1' yearand l_discount between 6 - 1 and 6 + 1and l_quantity < 2400

Q7

set hg_experimental_enable_double_equivalent=on; selectsupp_nation,cust_nation,l_year,sum(volume) as revenue from(selectn1.n_name as supp_nation,n2.n_name as cust_nation,extract(year from l_shipdate) as l_year,l_extendedprice * (1 - l_discount) as volumefromsupplier,lineitem,orders,customer,nation n1,nation n2wheres_suppkey = l_suppkeyand o_orderkey = l_orderkeyand c_custkey = o_custkeyand s_nationkey = n1.n_nationkeyand c_nationkey = n2.n_nationkeyand ((n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY')or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE'))and l_shipdate between date '1995-01-01' and date '1996-12-31') as shipping group bysupp_nation,cust_nation,l_year order bysupp_nation,cust_nation,l_year;

Q8

set hg_experimental_enable_double_equivalent=on; selecto_year,sum(casewhen nation = 'BRAZIL' then volumeelse 0end) / sum(volume) as mkt_share from(selectextract(year from o_orderdate) as o_year,l_extendedprice * (1 - l_discount) as volume,n2.n_name as nationfrompart,supplier,lineitem,orders,customer,nation n1,nation n2,regionwherep_partkey = l_partkeyand s_suppkey = l_suppkeyand l_orderkey = o_orderkeyand o_custkey = c_custkeyand c_nationkey = n1.n_nationkeyand n1.n_regionkey = r_regionkeyand r_name = 'AMERICA'and s_nationkey = n2.n_nationkeyand o_orderdate between date '1995-01-01' and date '1996-12-31'and p_type = 'STANDARD POLISHED TIN') as all_nations group byo_year order byo_year;

Q9

set hg_experimental_enable_double_equivalent=on; selectnation,o_year,sum(amount) as sum_profit from(selectn_name as nation,extract(year from o_orderdate) as o_year,l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amountfrompart,supplier,lineitem,partsupp,orders,nationwheres_suppkey = l_suppkeyand ps_suppkey = l_suppkeyand ps_partkey = l_partkeyand p_partkey = l_partkeyand o_orderkey = l_orderkeyand s_nationkey = n_nationkeyand p_name like '%green%') as profit group bynation,o_year order bynation,o_year desc;

Q10

selectc_custkey,c_name,sum(l_extendedprice * (1 - l_discount)) as revenue,c_acctbal,n_name,c_address,c_phone,c_comment fromcustomer,orders,lineitem,nation wherec_custkey = o_custkeyand l_orderkey = o_orderkeyand o_orderdate >= date '1993-10-01'and o_orderdate < date '1993-10-01' + interval '3' monthand l_returnflag = 'R'and c_nationkey = n_nationkey group byc_custkey,c_name,c_acctbal,c_phone,n_name,c_address,c_comment order byrevenue desc limit 20;

Q11

selectps_partkey,sum(ps_supplycost * ps_availqty) as value frompartsupp,supplier,nation whereps_suppkey = s_suppkeyand s_nationkey = n_nationkeyand n_name = 'GERMANY' group byps_partkey havingsum(ps_supplycost * ps_availqty) > (selectsum(ps_supplycost * ps_availqty) * 0.0000010000frompartsupp,supplier,nationwhereps_suppkey = s_suppkeyand s_nationkey = n_nationkeyand n_name = 'GERMANY') order byvalue desclimit 100;

Q12

selectl_shipmode,sum(casewhen o_orderpriority = '1-URGENT'or o_orderpriority = '2-HIGH'then 1else 0end) as high_line_count,sum(casewhen o_orderpriority <> '1-URGENT'and o_orderpriority <> '2-HIGH'then 1else 0end) as low_line_count fromorders,lineitem whereo_orderkey = l_orderkeyand l_shipmode in ('MAIL', 'SHIP')and l_commitdate < l_receiptdateand l_shipdate < l_commitdateand l_receiptdate >= date '1994-01-01'and l_receiptdate < date '1994-01-01' + interval '1' year group byl_shipmode order byl_shipmode;

Q13

selectc_count,count(*) as custdist from(selectc_custkey,count(o_orderkey)fromcustomer left outer join orders onc_custkey = o_custkeyand o_comment not like '%special%requests%'group byc_custkey) as c_orders (c_custkey, c_count) group byc_count order bycustdist desc,c_count desc;

Q14

select100.00 * sum(casewhen p_type like 'PROMO%'then l_extendedprice * (1 - l_discount)else 0end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue fromlineitem,part wherel_partkey = p_partkeyand l_shipdate >= date '1995-09-01'and l_shipdate < date '1995-09-01' + interval '1' month;

Q15

with revenue0(SUPPLIER_NO, TOTAL_REVENUE) as(selectl_suppkey,sum(l_extendedprice * (1 - l_discount))fromlineitemwherel_shipdate >= date '1995-12-01'and l_shipdate < date '1995-12-01' + interval '3' monthgroup byl_suppkey) selects_suppkey,s_name,s_address,s_phone,total_revenue fromsupplier,revenue0 wheres_suppkey = supplier_noand total_revenue = (selectmax(total_revenue)fromrevenue0) order bys_suppkey;

Q16

selectp_brand,p_type,p_size,count(distinct ps_suppkey) as supplier_cnt frompartsupp,part wherep_partkey = ps_partkeyand p_brand <> 'Brand#45'and p_type not like 'MEDIUM POLISHED%'and p_size in (49, 14, 23, 45, 19, 3, 36, 9)and ps_suppkey not in (selects_suppkeyfromsupplierwheres_comment like '%Customer%Complaints%') group byp_brand,p_type,p_size order bysupplier_cnt desc,p_brand,p_type,p_size;

Q17

selectsum(l_extendedprice) / 7.0 as avg_yearly fromlineitem,part wherep_partkey = l_partkeyand p_brand = 'Brand#23'and p_container = 'MED BOX'and l_quantity < (select0.2 * avg(l_quantity)fromlineitemwherel_partkey = p_partkeyand l_partkey in(select p_partkey from part where p_brand = 'Brand#23' and p_container = 'MED BOX'));

Q18

selectc_name,c_custkey,o_orderkey,o_orderdate,o_totalprice,sum(l_quantity) fromcustomer,orders,lineitem whereo_orderkey in (selectl_orderkeyfromlineitemgroup byl_orderkey havingsum(l_quantity) > 300)and c_custkey = o_custkeyand o_orderkey = l_orderkey group byc_name,c_custkey,o_orderkey,o_orderdate,o_totalprice order byo_totalprice desc,o_orderdate limit 100;

Q19

selectsum(l_extendedprice* (1 - l_discount)) as revenue fromlineitem,part where(p_partkey = l_partkeyand p_brand = 'Brand#12'and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')and l_quantity >= 1 and l_quantity <= 1 + 10and p_size between 1 and 5and l_shipmode in ('AIR', 'AIR REG')and l_shipinstruct = 'DELIVER IN PERSON')or(p_partkey = l_partkeyand p_brand = 'Brand#23'and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')and l_quantity >= 10 and l_quantity <= 10 + 10and p_size between 1 and 10and l_shipmode in ('AIR', 'AIR REG')and l_shipinstruct = 'DELIVER IN PERSON')or(p_partkey = l_partkeyand p_brand = 'Brand#34'and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')and l_quantity >= 20 and l_quantity <= 20 + 10and p_size between 1 and 15and l_shipmode in ('AIR', 'AIR REG')and l_shipinstruct = 'DELIVER IN PERSON');

Q20

selects_name,s_address fromsupplier,nation wheres_suppkey in (selectps_suppkeyfrompartsuppwhereps_partkey in (selectp_partkeyfrompartwherep_name like 'forest%')and ps_availqty > (select0.5 * sum(l_quantity)fromlineitemwherel_partkey = ps_partkeyand l_suppkey = ps_suppkeyand l_shipdate >= date '1994-01-01'and l_shipdate < date '1994-01-01' + interval '1' year))and s_nationkey = n_nationkeyand n_name = 'CANADA' order bys_name;

Q21

selects_name,count(*) as numwait fromsupplier,lineitem l1,orders,nation wheres_suppkey = l1.l_suppkeyand o_orderkey = l1.l_orderkeyand o_orderstatus = 'F'and l1.l_receiptdate > l1.l_commitdateand exists (select*fromlineitem l2wherel2.l_orderkey = l1.l_orderkeyand l2.l_suppkey <> l1.l_suppkey)and not exists (select*fromlineitem l3wherel3.l_orderkey = l1.l_orderkeyand l3.l_suppkey <> l1.l_suppkeyand l3.l_receiptdate > l3.l_commitdate)and s_nationkey = n_nationkeyand n_name = 'SAUDI ARABIA' group bys_name order bynumwait desc,s_name limit 100;

Q22

selectcntrycode,count(*) as numcust,sum(c_acctbal) as totacctbal from(selectsubstring(c_phone from 1 for 2) as cntrycode,c_acctbalfromcustomerwheresubstring(c_phone from 1 for 2) in('13', '31', '23', '29', '30', '18', '17')and c_acctbal > (selectavg(c_acctbal)fromcustomerwherec_acctbal > 0.00and substring(c_phone from 1 for 2) in('13', '31', '23', '29', '30', '18', '17'))and not exists (select*fromorderswhereo_custkey = c_custkey)) as custsale group bycntrycode order bycntrycode;

Key/Value點查場景測試

準備數據

1. 創建表

• 繼續使用OLAP查詢場景創建的數據庫，我們會使用TPCH數據集中的orders表進行測試，使用psql連接Hologres后，您可以使用如下建表語句創建數據庫表；

注意：點查場景需要使用行存表，所以需要創建一張新表，不能使用OLAP查詢場景中使用的表 DROP TABLE IF EXISTS orders_row;BEGIN; CREATE TABLE public.orders_row ("o_orderkey" int8 NOT NULL,"o_custkey" int8,"o_orderstatus" bpchar(1),"o_totalprice" numeric(15,2),"o_orderdate" date,"o_orderpriority" bpchar(15),"o_clerk" bpchar(15),"o_shippriority" int8,"o_comment" varchar(79), PRIMARY KEY (o_orderkey) ); CALL SET_TABLE_PROPERTY('public.orders_row', 'orientation', 'row'); CALL SET_TABLE_PROPERTY('public.orders_row', 'clustering_key', 'o_orderkey'); CALL SET_TABLE_PROPERTY('public.orders_row', 'time_to_live_in_seconds', '3153600000'); CALL SET_TABLE_PROPERTY('public.orders_row', 'distribution_key', 'o_orderkey'); COMMIT;

2. COPY方式導入數據

• 本測試方案主要使用 COPY FROM STDIN 的方式導入數據詳細可以參考官方文檔。此處會將此前生成的 tbl 數據文件導入 Hologres 中創建的表中。
• 您可以在數據生成工具的目錄中參考如下命令導入數據

PGUSER=<AccessID> PGPASSWORD=<AccessKey> psql -p <Port> -h <Endpoint> -d <Database> -c "COPY public.orders_row from stdin with delimiter '|' csv;" < orders.tbl

3. INSERT INTO方式導入數據

• 由于OLAP場景時您已經導入了orders表的數據，您可以運行如下SQL語句導入數據

INSERT INTO public.orders_row SELECT * FROM public.orders;

查詢

1. 生成查詢語句

• Key/Value點查場景主要的查詢語句特征如下

SELECT column_a,column_b,...,column_x FROM table_x WHERE pk = value_x ;

或

SELECT column_a,column_b,...,column_x FROM table_x WHERE pk IN ( value_a, value_b,..., value_x ) ;

• 您可以使用如下腳本生成所需的sql，該腳本會生成2條sql

  • kv_query_single.sql 針對單值篩選的SQL
  • kv_query_in.sql 針對多值篩選的SQL，該腳本會隨機生成一個針對10個值篩選的SQL

rm -rf kv_query mkdir kv_query cd kv_query echo '\set column_values random(1,99999999) select O_ORDERKEY,O_CUSTKEY,O_ORDERSTATUS,O_TOTALPRICE,O_ORDERDATE,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY,O_COMMENT from public.orders_row WHERE o_orderkey =:column_values;' >> kv_query_single.sql echo '\set column_values1 random(1,99999999) \set column_values2 random(1,99999999) \set column_values3 random(1,99999999) \set column_values4 random(1,99999999) \set column_values5 random(1,99999999) \set column_values6 random(1,99999999) \set column_values7 random(1,99999999) \set column_values8 random(1,99999999) \set column_values9 random(1,99999999) \set column_values10 random(1,99999999) select O_ORDERKEY,O_CUSTKEY,O_ORDERSTATUS,O_TOTALPRICE,O_ORDERDATE,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY,O_COMMENT from public.orders_row WHERE o_orderkey in(:column_values1,:column_values2,:column_values3,:column_values4,:column_values5,:column_values6,:column_values7,:column_values8,:column_values9,:column_values10);' >> kv_query_in.sql

2. 進行查詢

• 查詢需要使用pgbench，您可以使用如下命令安裝pgbench

yum install postgresql-contrib

• 之后您即可使用pgbench進行壓測，針對單值篩選的場景

注意，請在生成SQL的目錄執行如下命令 PGUSER=<AccessID> PGPASSWORD=<AccessKey> pgbench -h <Endpoint> -p <Port> -d <Database> -c <Client_Num> -t <Query_Num> -n -f kv_query_single.sql

• 針對多值篩選的場景

注意，請在生成SQL的目錄執行如下命令 PGUSER=<AccessID> PGPASSWORD=<AccessKey> pgbench -h <Endpoint> -p <Port> -d <Database> -c <Client_Num> -t <Query_Num> -n -f kv_query_in.sql

• 參數解釋

配置項參數說明

-h	Hologres實例的endpoint	在Hologres管控臺查看
-p	Hologres實例的端口地址	在Hologres管控臺查看
-d	Hologres指定實例中的數據庫名
-c	客戶端數目(并發度)	示例：8
-t	每個客戶端需要執行的壓測query數目	50
-f	壓測的sql	示例：6.sql

測試結果參考

測試數據量：

• 本測試基于TPCH 100G的數據集進行測試，具體數據量如下表所示

表名行數

LINEITEM	600,037,902
ORDERS	150,000,000
PARTSUPP	80,000,000
PART	20,000,000
CUSTOMER	15,000,000
SUPPLIER	1,000,000
NATION	25
REGION	5

集群規格

計算資源存儲容量軟件版本備注

64 CU
(CPU：64 Core 內存：256 GB)	100 GB	r0.10.20	使用集群默認配置，Shard數量：40
128 CU
(CPU：128 Core 內存：512 GB)	100 GB	r0.10.20	使用集群默認配置，Shard數量：80

測試時間：2021年6月

測試結果

數據導入時間

• 數據導入執行時間以秒（s）為單位。
• 導入時間指將數據導入Hologres內表
• 在使用COPY方法導入數據時，一張表對應一個數據文件，并未使用并發導入方式。
• 具體數值如下表所示

說明：使用COPY方式導入時一張表對應一個數據文件，并未使用并發導入方式 表名行數數據量Hologres 64CU

使用COPY方式導入（公網網絡）	使用COPY方式導入（VPC網絡導入）	使用MaxCompute外表導入
LINEITEM	600,037,902	73.6GB	3,070.453	694.364	148.165
ORDERS	150,000,000	16.4GB	691.060	172.529	37.741
PARTSUPP	80,000,000	2.3GB	468.560	107.092	18.488
PART	20,000,000	11.3GB	96.342	24.020	8.083
CUSTOMER	15,000,000	2.3GB	95.190	22.937	10.363
SUPPLIER	1,000,000	132MB	5.057	1.803	1.503
NATION	25	2KB	0.580	0.584	0.747
REGION	5	0.375KB	0.168	0.153	0.430
Total	106G	4427.410	1023.482	225.52

• 下圖中藍色為使用COPY方式在公網條件下導入數據的時間，綠色為使用COPY方式在VPC網絡條件下導入數據的時間，灰色為使用MaxCompute外表方式導入的時間
• 縱坐標數值越低，表示導入速度越快
• 橫軸：表名。縱軸：數據導入時間(s)

• 可以看出，由于網絡帶寬影響，使用COPY方式導入本地文件數據時，使用VPC網絡導入數據時間明顯短于使用公網導入數據時間；使用MaxCompute導入數據時間明顯短于使用COPY方式導入本地文件數據時間。

查詢時間

• 查詢執行時間以秒（s）為單位。
• 查詢結果均基于Hologres內表
• 具體數值如下表所示

TPCH Query編號Hologres 64CUHologres 128CU

1	3.120	2.150
2	0.581	0.467
3	1.735	1.005
4	1.558	0.836
5	2.921	1.917
6	0.297	0.096
7	2.006	1.029
8	2.674	1.679
9	5.298	2.796
10	1.944	0.924
11	0.397	0.297
12	1.531	0.852
13	1.741	0.971
14	0.286	0.160
15	0.293	0.177
16	1.223	1.020
17	1.405	0.607
18	3.817	2.169
19	1.400	0.622
20	1.358	0.868
21	4.164	2.047
22	1.121	0.654
Total	40.870	23.343

• 下圖中藍色為64CU的實例的查詢結果，綠色為128CU實例的查詢結果
• 縱坐標數值越低，表示 TPC-H 性能越好。
• 可以看出隨著實例規模的成本增長，查詢時間也在成線性下降趨勢
• 橫軸：query在文檔中的編號。縱軸：query執行時間(s)

原文鏈接：https://developer.aliyun.com/article/785226?

版權聲明：本文內容由阿里云實名注冊用戶自發貢獻，版權歸原作者所有，阿里云開發者社區不擁有其著作權，亦不承擔相應法律責任。具體規則請查看《阿里云開發者社區用戶服務協議》和《阿里云開發者社區知識產權保護指引》。如果您發現本社區中有涉嫌抄襲的內容，填寫侵權投訴表單進行舉報，一經查實，本社區將立刻刪除涉嫌侵權內容。

總結

以上是生活随笔為你收集整理的Hologres基于TPCH的性能测试介绍的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。