PostgreSQL從小白到專家，是從入門逐漸能力提升的一個系列教程，內(nèi)容包括對PG基礎(chǔ)的認知、包括安裝使用、包括角色權(quán)限、包括維護管理、、等內(nèi)容，希望對熱愛PG、學(xué)習(xí)PG的同學(xué)們有幫助，歡迎持續(xù)關(guān)注CUUG PG技術(shù)大講堂。

第46講：POC-TPCH測試

內(nèi)容1：TPC-H介紹

內(nèi)容2：TPC-H測試部署

內(nèi)容3：TPC-H報告

TPC背景介紹

TPC組織：

事務(wù)處理性能測試委員會TPC(Transaction process performance Council)是一個專門負責(zé)制定計算機事務(wù)處理能力測試標(biāo)準(zhǔn)并監(jiān)督其執(zhí)行的組織，其總部位于美國，針對數(shù)據(jù)庫不同的使用場景TPC組織發(fā)布了多項測試標(biāo)準(zhǔn)，其中被業(yè)界廣泛使用的有TPC-C 、TPC-E，TPC-H和TPC-DS，前兩者應(yīng)用到OLTP，后兩者應(yīng)用到OLAP場景。

OLTP與OLAP區(qū)別

聯(lián)機事務(wù)處理OLTP(on-line transaction processing) 主要是執(zhí)行基本日常的事務(wù)處理，比如數(shù)據(jù)庫記錄的增刪查改。比如在銀行的一筆交易記錄，就是一個典型的事務(wù)。高并發(fā)，高性能，且滿足事務(wù)的ACID特性。

聯(lián)機分析處理OLAP(On-Line Analytical Processing) 是數(shù)據(jù)倉庫系統(tǒng)的主要應(yīng)用，支持復(fù)雜的分析操作，側(cè)重決策支持，并且提供直觀易懂的查詢結(jié)果。典型的應(yīng)用就是復(fù)雜的動態(tài)的報表系統(tǒng)。對實時性要求不高，數(shù)據(jù)量大

測試標(biāo)準(zhǔn)-OLAP

隨著開源Hapdoop、Spark、HDFS、HBASE等技術(shù)的商用化，大數(shù)據(jù)管理技術(shù)得到了突飛猛進的發(fā)展，為了更客觀地比較不同數(shù)據(jù)管理系統(tǒng)，TPC組織牽頭制定了大數(shù)據(jù)測試基準(zhǔn)TPC-H，TPC-DS，后者是TPC組織在TPC-H基礎(chǔ)上的升級版本，下面介紹一下兩者差異以及TPC-DS的SQL覆蓋

TPC-H測試簡介

TPC-H是事務(wù)處理性能委員會( Transaction ProcessingPerformance Council )制定的基準(zhǔn)程序之一。

TPC- H 主要目的是評測數(shù)據(jù)庫系統(tǒng)在統(tǒng)計分析、數(shù)據(jù)挖掘、分析處理等決策支持方面的能力。

該基準(zhǔn)模擬了決策支持系統(tǒng)中的數(shù)據(jù)庫操作，測試數(shù)據(jù)庫系統(tǒng)復(fù)雜查詢的響應(yīng)時間，以每小時執(zhí)行的查詢數(shù)(TPC-H QphH@Siz)作為度量指標(biāo)。

TPC-H基準(zhǔn)模型中定義了一個數(shù)據(jù)庫模型，容量可以在1GB~10000GB的8個級別中進行選擇。數(shù)據(jù)庫模型包括CUSTOMER、LINEITEM、NATION、ORDERS、PART、PARTSUPP、REGION和SUPPLIER 8張數(shù)據(jù)表。

模擬商品零售業(yè)決策支持系統(tǒng)的 22 個查詢，涉及22條復(fù)雜的select查詢流語句和2條帶有insert和delete程序段的更新流語句。SQL涵蓋了統(tǒng)計分組、排序、聚集操作、子查詢、多表關(guān)聯(lián)等復(fù)雜操作，可以測試各個查詢的響應(yīng)時間。

TPC-H查詢語句簡介

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

在單個表lineitem上查詢某個時間段內(nèi)，對已經(jīng)付款的、已經(jīng)運送的等各類商品進行統(tǒng)計，包括業(yè)務(wù)量的計費、發(fā)貨、折扣、稅、平均價格等信息。

Q1語句的特點是：帶有分組、排序、聚集操作并存的單表查詢操作。這個查詢會導(dǎo)致表上的數(shù)據(jù)有95%到97%行被讀取到。

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

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

Q2語句的特點是：帶有排序、聚集操作、子查詢并存的多表查詢操作。查詢語句沒有從語法上限制返回多少條元組，但是TPC-H標(biāo)準(zhǔn)規(guī)定，查詢結(jié)果只返回前100行(通常依賴于應(yīng)用程序?qū)崿F(xiàn))。

Q3語句是查詢運送優(yōu)先級

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

Q3語句的特點是：帶有分組、排序、聚集操作并存的三表查詢操作。查詢語句沒有從語法上限制返回多少條元組，但是TPC-H標(biāo)準(zhǔn)規(guī)定，查詢結(jié)果只返回前10行(通常依賴于應(yīng)用程序?qū)崿F(xiàn))。

TPC-H測試部署

1、軟件安裝

$ unzip tpc-h-tools-2.17.3.zip

$ cd 2.17.3/

$ cd dbgen/

$ cp makefile.suite makefile

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

CC=gcc

DATABASE=ORACLE

MACHINE=LINUX

WORKLOAD=TPCH

#編譯

$ make

2、產(chǎn)生數(shù)據(jù)模板

讓我們使用dbgen工具生成數(shù)據(jù)-有一個重要的參數(shù)“scale”影響數(shù)據(jù)量。它大致相當(dāng)于原始數(shù)據(jù)的GB數(shù)，所以要生成5GB的數(shù)據(jù)，注意當(dāng)前是否有足夠的空間。

./dbgen -s 5

它以類似于Oracle的CSV格式創(chuàng)建一堆.tbl文件，ls *.tbl查看。

3、要將它們轉(zhuǎn)換為與PostgreSQL兼容的CSV格式，請執(zhí)行以下操作

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

執(zhí)行完成后可以把.tbl文件刪除，否則占用空間，現(xiàn)在我們有八個CSV文件可以加載到數(shù)據(jù)庫中。但我們必須先創(chuàng)造它。

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、創(chuàng)建數(shù)據(jù)庫并加載數(shù)據(jù)

盡管TPC-H規(guī)范描述了數(shù)據(jù)庫結(jié)構(gòu)，但create腳本不是包的一部分。PG準(zhǔn)備了一個創(chuàng)建所有表的創(chuàng)建腳本和一個創(chuàng)建外鍵的alter腳本(在填充數(shù)據(jù)庫之后)，需要先創(chuàng)建一個數(shù)據(jù)庫tpch然后再創(chuàng)建表。

$ cd /soft/2.17.3/dbgen/dss

$ psql tpch < tpch-create.sql

把前面由八個.tbl 轉(zhuǎn)化為CSV文件mv到當(dāng)前路徑的data目錄下，data目錄需要自己創(chuàng)建。

$ mkdir data

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

6、用生成的數(shù)據(jù)填充數(shù)據(jù)庫

在/soft/2.17.3/dbgen/dss目錄下創(chuàng)建腳本load.sql，加入如下內(nèi)容：

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 '|') "

#執(zhí)行該腳本，導(dǎo)入數(shù)據(jù)：

sh load.sql

7、最后創(chuàng)建約束

psql tpch < tpch-pkeys.sql --先創(chuàng)建主鍵

psql tpch < tpch-alter.sql --創(chuàng)建外鍵

8、產(chǎn)生sql語句：

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

$ cd /soft/2.17.3/dbgen

--創(chuàng)建存放sql語句的目錄：

$ mkdir dss/queries

--產(chǎn)生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、產(chǎn)生sql語句(續(xù))：

現(xiàn)在，dss/querys目錄中應(yīng)該有44個文件。其中22個將實際運行查詢，另外22個將生成查詢的解釋計劃(不實際運行)。

自此，數(shù)據(jù)加載完成，同時產(chǎn)生sql語句，可以調(diào)用里面的sql語句執(zhí)行。

9、產(chǎn)生工作負載集

--從查詢模板中把22個查詢集中產(chǎn)生一個壓力測試的sql腳本：

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

--指定某些查詢模板產(chǎn)生工作負載集：

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

9、產(chǎn)生工作負載集

--指定1-10的查詢模板產(chǎn)生工作負載集：

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、執(zhí)行壓力測試腳本

前面我們以及生成了數(shù)據(jù)集，產(chǎn)生了壓力測試的腳本，接下來需要去執(zhí)行它，執(zhí)行的方式有很多種，根據(jù)實際測試的要求去運行。

--簡單的執(zhí)行測試腳本，可以執(zhí)行如下命令：

psql tpch < workload.sql

這個查詢只是產(chǎn)生工作量，沒有產(chǎn)生有關(guān)統(tǒng)計分析后的信息。

10、執(zhí)行壓力測試腳本

--生成一組結(jié)果日志，其中包含每個工作負載的秒數(shù)。從各種工具(iostat、vmstat等等)收集數(shù)據(jù)。

先準(zhǔn)備workload-1.sql-workload-4.sql 4個腳本，然后再寫一個腳本，調(diào)用該腳本：

DSS_QUERY=dss/queries ./qgen 1 > dss/workload-1.sql #根據(jù)這個命令產(chǎn)生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;

結(jié)合JeMeter產(chǎn)生測試報告

CUUG PostgreSQL技術(shù)大講堂系列公開課第46講-POC-TPCH測試的內(nèi)容，往期視頻及文檔，請聯(lián)系CUUG。