PostgreSQL技術大講堂 - 第46講：poc-tpch測試

PostgreSQL從小白到專家，是從入門逐漸能力提升的一個系列教程，內容包括對PG基礎的認知、包括安裝使用、包括角色許可權、包括維護管理、、等內容，希望對熱愛PG、學習PG的同學們有幫助，歡迎持續關注CUUG PG技術大講堂。

第46講： POC-TPCH 測試

內容1：TPC-H介紹

內容2：TPC-H測試部署

內容3：TPC-H報告

TPC背景介紹

TPC組織：

事務處理效能測試委員會TPC(Transaction process performance Council)是一個專門負責制定計算機事務處理能力測試標準並監督其執行的組織，其總部位於美國，針對資料庫不同的使用場景TPC組織釋出了多項測試標準，其中被業界廣泛使用的有TPC-C 、TPC-E，TPC-H和TPC-DS，前兩者應用到OLTP，後兩者應用到OLAP場景。

OLTP與OLAP區別

聯機事務處理OLTP(on-line transaction processing) 主要是執行基本日常的事務處理，比如資料庫記錄的增刪查改。比如在銀行的一筆交易記錄，就是一個典型的事務。高併發，高效能，且滿足事務的ACID特性。

聯機分析處理OLAP(On-Line Analytical Processing) 是資料倉儲系統的主要應用，支援複雜的分析操作，側重決策支援，並且提供直觀易懂的查詢結果。典型的應用就是複雜的動態的報表系統。對實時性要求不高，資料量大

測試標準-OLAP

隨著開源Hapdoop、Spark、HDFS、HBASE等技術的商用化，大資料管理技術得到了突飛猛進的發展，為了更客觀地比較不同資料管理系統，TPC組織牽頭制定了大資料測試基準TPC-H，TPC-DS，後者是TPC組織在TPC-H基礎上的升級版本，下面介紹一下兩者差異以及TPC-DS的SQL覆蓋

TPC-H測試簡介

TPC-H是事務處理效能委員會( Transaction ProcessingPerformance Council )制定的基準程式之一。

TPC- H 主要目的是評測資料庫系統在統計分析、資料探勘、分析處理等決策支援方面的能力。

該基準模擬了決策支援系統中的資料庫操作，測試資料庫系統複雜查詢的響應時間，以每小時執行的查詢數(TPC-H QphH@Siz)作為度量指標。

TPC-H基準模型中定義了一個資料庫模型，容量可以在1GB~10000GB的8個級別中進行選擇。資料庫模型包括CUSTOMER、LINEITEM、NATION、ORDERS、PART、PARTSUPP、REGION和SUPPLIER 8張資料表。

模擬商品零售業決策支援系統的 22 個查詢，涉及22條複雜的select查詢流語句和2條帶有insert和delete程式段的更新流語句。SQL涵蓋了統計分組、排序、聚集操作、子查詢、多表關聯等複雜操作，可以測試各個查詢的響應時間。

TPC-H查詢語句簡介

Q1語句是查詢lineItems的一個定價總結報告

在單個表lineitem上查詢某個時間段內，對已經付款的、已經運送的等各類商品進行統計，包括業務量的計費、發貨、折扣、稅、平均價格等資訊。

Q1語句的特點是：帶有分組、排序、聚集操作並存的單表查詢操作。這個查詢會導致表上的資料有95%到97%行被讀取到。

Q2語句是查詢最小代價供貨商查詢

Q2語句查詢獲得最小代價的供貨商。得到給定的區域內，對於指定的零件(某一型別和大小的零件)，哪個供應者能以最低的價格供應它，就可以選擇哪個供應者來訂貨。

Q2語句的特點是：帶有排序、聚集操作、子查詢並存的多表查詢操作。查詢語句沒有從語法上限制返回多少條元組，但是TPC-H標準規定，查詢結果只返回前100行(通常依賴於應用程式實現)。

Q3語句是查詢運送優先順序

Q3語句查詢得到收入在前10位的尚未運送的訂單。在指定的日期之前還沒有運送的訂單中具有最大收入的訂單的運送優先順序(訂單按照收入的降序排序)和潛在的收入(潛在的收入為l_extendedprice * (1-l_discount)的和)。

Q3語句的特點是：帶有分組、排序、聚集操作並存的三表查詢操作。查詢語句沒有從語法上限制返回多少條元組，但是TPC-H標準規定，查詢結果只返回前10行(通常依賴於應用程式實現)。

TPC-H測試部署

1、軟體安裝

$ unzip tpc-h-tools-2.17.3.zip

$ cd 2.17.3/

$ cd dbgen/

$ cp makefile.suite makefile

#編輯makefile (修改如下內容 (第 109行左右),下面內容區別大小寫)

CC=gcc

DATABASE=ORACLE

MACHINE=LINUX

WORKLOAD=TPCH

#編譯

$ make

2、產生資料模板

讓我們使用dbgen工具生成資料-有一個重要的引數“scale”影響資料量。它大致相當於原始資料的GB數，所以要生成5GB的資料，注意當前是否有足夠的空間。

./dbgen -s 5

它以類似於Oracle的CSV格式建立一堆.tbl檔案，ls *.tbl檢視。

3、要將它們轉換為與PostgreSQL相容的CSV格式，請執行以下操作

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

執行完成後可以把.tbl檔案刪除，否則佔用空間，現在我們有八個CSV檔案可以載入到資料庫中。但我們必須先創造它。

4、安裝pg_tpch_master軟體，提供測試指令碼：

unzip pg_tpch-master.zip

cd pg_tpch-master

cp -rf dss /soft/2.17.3/dbgen/ #/soft是存放tpch軟體的目錄，後面需要dss目錄下的檔案

5、建立資料庫並載入資料

儘管TPC-H規範描述了資料庫結構，但create指令碼不是包的一部分。PG準備了一個建立所有表的建立指令碼和一個建立外來鍵的alter指令碼(在填充資料庫之後)，需要先建立一個資料庫tpch然後再建立表。

$ cd /soft/2.17.3/dbgen/dss

$ psql tpch < tpch-create.sql

把前面由八個.tbl 轉化為CSV檔案mv到當前路徑的data目錄下，data目錄需要自己建立。

$ mkdir data

$ mv /soft/2.17.3/dbgen/*.csv data/

6、用生成的資料填充資料庫

在/soft/2.17.3/dbgen/dss目錄下建立指令碼load.sql，加入如下內容：

dir=`pwd`/data

opts="-h localhost tpch"

psql $opts -c "COPY part FROM '$dir/part.csv' WITH (FORMAT csv, DELIMITER '|')"

psql $opts -c "COPY region FROM '$dir/region.csv' WITH (FORMAT csv, DELIMITER '|')"

psql $opts -c "COPY nation FROM '$dir/nation.csv' WITH (FORMAT csv, DELIMITER '|')"

psql $opts -c "COPY supplier FROM '$dir/supplier.csv' WITH (FORMAT csv, DELIMITER '|')"

psql $opts -c "COPY customer FROM '$dir/customer.csv' WITH (FORMAT csv, DELIMITER '|')"

psql $opts -c "COPY partsupp FROM '$dir/partsupp.csv' WITH (FORMAT csv, DELIMITER '|')"

psql $opts -c "COPY orders FROM '$dir/orders.csv' WITH (FORMAT csv, DELIMITER '|')"

psql $opts -c "COPY lineitem FROM '$dir/lineitem.csv' WITH (FORMAT csv, DELIMITER '|') "

#執行該指令碼，匯入資料：

sh load.sql

7、最後建立約束

psql tpch < tpch-pkeys.sql --先建立主鍵

psql tpch < tpch-alter.sql --建立外來鍵

8、產生sql語句：

現在我們必須從TPC-H基準中指定的模板生成查詢。在tpch.org上提供的模板不適合PostgreSQL。因此，在“dss/templates”目錄中提供了稍加修改的查詢，應該將這些查詢放在“dss/querys”目錄中。進入到dbgen目錄下，(必須要在此目錄中)

$ cd /soft/2.17.3/dbgen

--建立存放sql語句的目錄：

$ mkdir dss/queries

--產生sql語句：

for q in `seq 1 22`

do

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

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

cat dss/queries/$q.sql >> dss/queries/$q.explain.sql;

done

8、產生sql語句(續)：

現在，dss/querys目錄中應該有44個檔案。其中22個將實際執行查詢，另外22個將生成查詢的解釋計劃(不實際執行)。

自此，資料載入完成，同時產生sql語句，可以呼叫裡面的sql語句執行。

9、產生工作負載集

--從查詢模板中把22個查詢集中產生一個壓力測試的sql指令碼：

DSS_QUERY=dss/queries ./qgen > dss/workload.sql

--指定某些查詢模板產生工作負載集：

DSS_QUERY=dss/queries ./qgen 1 2 3 > dss/1_2_3_workload.sql

9、產生工作負載集

--指定1-10的查詢模板產生工作負載集：

for r in `seq 1 10`

do

rn=$((`cat /dev/urandom|od -N3 -An -i` % 10000))

DSS_QUERY=dss/queries ./qgen -r $rn >> dss/1_to_10_workload.sql

done

10、執行壓力測試指令碼

前面我們以及生成了資料集，產生了壓力測試的指令碼，接下來需要去執行它，執行的方式有很多種，根據實際測試的要求去執行。

--簡單的執行測試指令碼，可以執行如下命令：

psql tpch < workload.sql

這個查詢只是產生工作量，沒有產生有關統計分析後的資訊。

10、執行壓力測試指令碼

--生成一組結果日誌，其中包含每個工作負載的秒數。從各種工具(iostat、vmstat等等)收集資料。

先準備workload-1.sql-workload-4.sql 4個指令碼，然後再寫一個指令碼，呼叫該指令碼：

DSS_QUERY=dss/queries ./qgen 1 > dss/workload-1.sql #根據這個命令產生1-4的壓力指令碼

#start the processes

for c in `seq 1 4`

do

/usr/bin/time -f "total=%e" -o result-$c.log \

psql tpch < workload-$c.sql > /dev/null 2>&1 &

done;

# wait for the processes

for p in `jobs -p`

do

wait $p;

done;

結合JeMeter產生測試報告

CUUG PostgreSQL技術大講堂系列公開課第46講-POC-TPCH測試的內容，往期影片及文件，請聯絡CUUG。