Tpc-h測試greenplum效能

叢集環境

主機名	ip	記憶體	磁碟
Node1	192.168.71.11	2G	80G
Node2	192.168.71.12	1.5G	80G
Node3	192.168.71.13	1.5G	80G

 

首先一點忠告：tpch不要到官網下，下不下來

建議到csdn下載：tpch-dbgen.tar.gz

然後TPCH是什麼？

TPC-H包括8張表（表上有些約束等需要滿足，參見TPC-H規範，不再羅列），如下：
PART：表示零件的資訊
SUPPLIER：表示供貨商的資訊
PARTSUPP：表示供貨商的零件的資訊
CUSTOMER：表示消費者的資訊
ORDERS：表示訂單的資訊
LINEITEM：表示線上商品的資訊
NATION：表示國家的資訊
REGION：表示地區的資訊
這8張表之間的關係，如圖所示

 

1.    
安裝tpch並生成10G測試資料

Tar -zxvf tpch-dbgen.tar.gz

解壓玩安裝包之後，會多出一個dbgen資料夾，執行cd dbgen到degen目錄下

cp makefile.sute Makefile

vim Makefile

修改如下：

CC = gcc

DATABASE= ORACLE
MACHINE = LINUX
WORKLOAD = TPCH

然後執行make，生成tpch的模擬資料，dbgen下面會多了8個表名命名的.tbl檔案

注意:有的安裝包如果發現包含makefile檔案的內容已修改，可直接make，按上面的操作反而會報錯。

 

執行下面的命令生成10G模擬資料： ./dbgen -s 10 -f（依照機器效能的不同所需時間不同，大概幾分鐘）

 

然後將測試資料轉化為GP能夠識別的格式，刪除末尾的分隔符|。

for i in ls
*.tbl; do sed `s/|$//` $i > ${i/tbl/csv}; done

 

2.    
下載pg_tpch並關聯tpch

wget https://github.com/tvondra/pg_tpch/archive/master.zip

解壓安裝

在他的dss目錄下面有載入tpch資料到gp的指令碼

其中tpch-load.sql是列式儲存，tpch-load_pg.sql是行儲存，具體的優化熟悉gp用法之後自行修改優化。其他幾個指令碼是建立表的指令碼。

 

將pg_tpch的檔案逗拷貝到dbgen下面：

cp -r
pg_tpch-master/*       /dbgen

 

建立一個queries目錄，用於存放轉換後的tpc-h 測試SQL。

mkdir dss/queries

 

生成測試SQL

for q in `seq 1 22`

do

   
DSS_QUERY=dss/templates ./qgen -s $SF $q > dss/queries/$q.sql

   
sed `s/^select/explain select/` dss/queries/$q.sql >
dss/queries/$q.explain.sql

done

3.    
Load資料到GP

在greenplum資料庫中建立資料庫和使用者（也可以不建立，只要有就可以）

 

配置pg_hba.conf

$ vi
$MASTER_DATA_DIRECTORY/pg_hba.conf

host all all 127.0.0.1/32 trust

$ gpstop -u

設定幾個引數:

gpconfig -c enable_nestloop -v off

gpconfig -c work_mem -v 256MB

gpstop -u

測試，使用digoal使用者連線到postgres資料庫，結果輸出到./results目錄：
自動建立表，載入資料。詳見tpch.sh指令碼

./tpch.sh ./results tpch-db tpch-user (機器效能不同所需時間不同，大概需要半小時)

結束後，可以使用以下方法生成CSV報告。

php process.php ./results output.csv

4.    
基於mondrian-web測試gp效能

首先將tpch所有的表join到一張大表中（tpch_join），一個大sql來jion的話本機的gp太慢，分為幾個小表分別join,最後再合到一起。見下圖和sql。

 

create
table oc_join as select o.* ,c .* from orders o left join customer c on
o.o_custkey=c.c_custkey distributed by (o_orderkey)

 

create
table nr_join as select n.* ,r.* from nation n left join region r on
n.n_regionkey=r.r_regionkey distributed by (n_nationkey)

 

 

create
table snr_join as select s.*,nr.* from supplier s left join nr_join nr on
s.s_nationkey=nr.n_nationkey distributed by (s_suppkey)

 

create
table pps_join as select ps.*,p.*,snr.* from partsupp ps left join part p on
ps.ps_partkey=p.p_partkey left join snr_join snr on ps.ps_suppkey=snr.s_suppkey
distributed by (ps_partkey,ps_suppkey)

 

列式儲存

create
table tpch_join  with (appendonly=true, compresstype=quicklz,
compresslevel=1, orientation=column) as select lt.*,pps.*,oc.* from lineitem lt left join
pps_join pps on lt.l_partkey=pps.ps_partkey and lt.l_suppkey=pps.ps_suppkey
left join oc_join oc on lt.l_orderkey=oc.o_orderkey distributed by
(l_linenumber,l_orderkey)

 

行式儲存

create
table tpch_join  with (appendonly=true,
compresstype=quicklz, compresslevel=1) as select lt.*,pps.*,oc.* from lineitem
lt left join pps_join pps on lt.l_partkey=pps.ps_partkey and
lt.l_suppkey=pps.ps_suppkey left join oc_join oc on lt.l_orderkey=oc.o_orderkey
distributed by (l_linenumber,l_orderkey)

 

檢視錶空間

select
pg_size_pretty(pg_relation_size(`tpch_join`));

select
pg_size_pretty(pg_total_relation_size(`tpch_join`));

select
pg_size_pretty(pg_database_size(`testtpch`));

 

建立主鍵和索引

alter
table tpch_join add primary key(l_linenumber,l_orderkey);

create
index indx_tpch_join_linenumber on tpch_join (l_linenumber);

create
index indx_tpch_join_orderkey on tpch_join (l_orderkey);

create
index indx_tpch_join_partkey on tpch_join (l_partkey);

create
index indx_tpch_join_suppkey on tpch_join (l_suppkey);

 

注意：

       最後生成的tpch_join大表如果使用行式儲存會有80幾個G，加上主鍵和索引之後同時優化資料庫配置，儘管最簡單的查詢速度回巨慢無比，所以後來改為列式儲存。

資料庫優化配置如下（配置完之後對查詢效率的提升沒有太大的效果，對mondrian查詢提升只有十幾秒，可能受限於我的虛擬機器的資源的問題）：

       編輯$MASTER_DATA_DIRECTORY/postgresql.conf

       shared_buffers：剛開始可以設定一個較小的值，比如總記憶體的15%，然後逐漸增加，過程中監控效能提升和swap的情況

       effective_cache_size : 這個引數告訴PostgreSQL的優化器有多少記憶體可以被用來快取資料，以及幫助決定是否應該使用索引。這個數值越大，優化器使用索引的可能性也越大。
因此這個數值應該設定成shared_buffers加上可用作業系統快取兩者的總量。通常這個數值會超過系統記憶體總量的50%

       temp_buffers: 即臨時緩衝區，擁有資料庫訪問臨時資料，GP中預設值為1M，在訪問比較到大的臨時表時，對效能提升有很大幫助。

 

轉用列式儲存之後最後的大表是22G左右，能夠支援mondrian對gp的操作，相應時間1-2分鐘，但是遇到group by 後面的欄位將全表資料全部掃出來的時候，叢集記憶體會爆掉。

gp行列儲存的查詢效能對比

儲存方式	Tpch_join大小	壓縮方式	主鍵	索引	MDX	Mondrian生成查詢語句	查詢時間
行式	84G	無	L_linenumber, l_orderkey	indx_tpch_join_linenumber, indx_tpch_join_orderkey, indx_tpch_join_partkey, indx_tpch_join_suppkey	select{[Lineitem].[LineitemInfo].[l_linenumber].members} on columns, [measures].[l_extendedprice] on rows from [Lineitem]	Select “lineitem”.”l_linenumber” as “c0”, sum(“lineitem”.”l_extendedprice”) as “m0” from “lineitem” as “lineitem” group by “c0”	—-
行式	36G	quicklz	同上	同上	同上	同上	13min24s
列式	22G	quicklz	同上	同上	同上	同上	75s

 

所以使用gp和mondrian對於某個業務做多維分析的時候，join出來的大表首先一定要使用列式儲存，在保證查詢的前提下，再去根據自身叢集的資源配置gp的資料庫系統引數來提升查詢效能。