MPP分散式資料庫效能評定方法 - 阿裡雲HybridDB for PostgreSQL最佳實踐

摘要： 幕後通常評定一個資料庫的效能，可以選擇工業標準測試，或者根據商務型號，建模進行測試。例如PostgreSQLpgbench支援的tpc-b測試，以及自訂型號測試。benchmarksql支援的tpc-c測試。

通常評定一個資料庫的效能，可以選擇工業標準測試，或者根據商務型號，建模進行測試。

例如PostgreSQL pgbench支援的tpc-b測試，以及自訂型號測試。

benchmarksql支援的tpc-c測試。

gp_tpch支援的tpc-h測試等等。

參考文件如下

《TPC-H測試 -PostgreSQL 10 vs Deepgreen(Greenplum)》

《PostgreSQL使用 pgbench 測試 sysbench 相關case》

《PostgreSQLpgbench SQL RT 與 交易RT 淺析》

《資料庫界的華山論劍tpc.org》

但是這些都是在構建了資料庫之後才可以進行的測試，在構建資料庫系統之前，如何評定效能呢？

這些硬體指標是資料庫效能的主要影響因素

CPU主頻

CPU指令集

CPU核數

記憶體主頻、匯流排頻寬

硬碟的離散IOPS能力

硬碟的連續IOPS能力

硬碟的頻寬

網路的頻寬

針對Greenplum資料庫，它的主要影響如下：

1、CPU主頻

決定了資料庫的計算速度，哪些涉及計算呢？例如：

where條件遮罩，select子句中的操作符計算，彙總計算，排序 等。

2、CPU指令集

指令集決定了資料庫的某些特殊優化的效能，例如：

向量計算。

《PostgreSQL向量化執行外掛程式(瓦片式實現)10x提速OLAP》

3、CPU核數

CPU主頻決定了單個核的計算能力，而核數，決定了資料庫的平行計算的能力。

4、記憶體主頻、匯流排頻寬

當在記憶體中進行讀寫時，記憶體主頻和匯流排頻寬大小決定了整體的讀寫吞吐能力，非常重要。

例如 DDR 2 667，頻寬即為64bit×667MHz÷8≈5.3GB/s，如果是雙通道記憶體，還得×2，即雙通道DDR 2 667記憶體資料頻寬為10.6GB/s。

https://www.cyberciti.biz/faq/check-ram-speed-linux/

https://en.wikipedia.org/wiki/Memory_bandwidth

例如這個記憶體，理論讀寫頻寬64*2*2400/8/1024=37.5 GB/s

dmidecode --type 17

Array Handle: 0x0034

Error Information Handle: Not Provided

Total Width: 72 bits##帶ECC, 64+8

Data Width: 72 bits

Size: 32 GB

Form Factor: DIMM

Set: None

Locator: CPU0_A0

Bank Locator: NODE 1

Type: DDR4

Type Detail:

Speed: 2400 MHz

Manufacturer:

Serial Number:

Asset Tag:

Part Number:

Rank: 2

Configured Clock Speed: 2133 MHz

注意，這是記憶體的理論極限，單個CPU核心處理時，通常不能達到這個極限速度。

單個CPU的處理速度如何？可以通過一個簡單的測試得到

記憶體速度

#dd if=/dev/zero of=/dev/null bs=4k count=1024000000

^C68517474+0 records in

68517473+0 records out

280647569408 bytes (281 GB) copied, 34.1855 s, 8.2 GB/s

塊裝置速度

#dd if=/dev/塊裝置名稱 of=/dev/null bs=4k count=102300000

^C2687957+0 records in

2687956+0 records out

11009867776 bytes (11 GB) copied, 4.6525 s, 2.4 GB/s

實際上，在資料庫套用中，算上CPU參與計算的部份，實際上單核應該達不到8.2GB/s的速度。

6、硬碟的離散IOPS能力

索引存取、多個個對話或流程（並行）存取同一個硬碟的資料時，會涉及硬碟的離散存取能力。

（通過預讀，可以升階並行循序存取的能力，趨於連續IOPS的能力。）

7、硬碟的順序IOPS能力

不考慮並行時，只要不是索引掃描，通常AP系統大部分是順序的讀寫檔案。

8、硬碟的頻寬、硬碟的介面速率

硬碟的頻寬、介面速率決定了資料在硬碟中掃描的上限速度。

例如廠商會給出讀寫頻寬這樣的資料

http://www.shannon-sys.com/product_detail?id=4929256206666909936

注意，這是硬碟的理論極限，單個CPU核心處理時，通常不能達到這個極限速度。

9、網路的頻寬

網路頻寬決定了資料匯入速度，同時在資料JOIN時，決定了重散發的時候的速度。

單個主機可以有多個網卡，可以有多個資料節點，不管怎樣，按總的出口頻寬來估算，例如GP集群有10台主機，每台主機2張10GB網卡，則總網路頻寬為200 GB。

10、資料存放區傾斜性

分散式系統的短板效應，最慢的節點決定了總的處理時間。資料出現傾斜時，這個問題尤為醒目。

以上是影響效能的主要因素，那麼如何根據這些主要因素，評定SQL的回應速度呢？

PostgreSQL的代價型號中，有一些成本因數，通過成本計算公式和統計資訊，可以算出最終的SQL執行成本，如果將成本和時間對齊，就能得知SQL的執行時間。

《優化器成本因數校對 -PostgreSQL explain cost constants alignment to timestamp》

《優化器成本因數校對(disk,ssd,memoryIO開銷精算) - PostgreSQL real seq_page_cost &random_page_cost in disks,ssd,memory》

但是這依舊是在有資料庫、有資料（或者有資料的統計資訊）匯入到資料庫之後進行的評定。

在沒有資料庫，只有硬體指標和資料指標時，如何評定SQL回應時間呢？

我們可以將方程式抽樣出來，根據資料庫集群的指標以及資料的指標，SQL的需求進行評定。

簡化評定型號，因為CPU這方面（例如LLVM、向量優化、或者其他優化）帶來的效果是非常明顯的，對結果的影響很大。CPU引入的誤差我暫時不計較他。同時我們也不考慮資料扭曲。

1 環境介紹

以如下環境為例，講一下如何評定效能。

1、硬碟

2塊，每塊盤讀寫頻寬分別為2GB/s，通過LVM做成一塊盤。頻寬算4GB/s。

2、記憶體

512GB，讀寫頻寬 37.5 GB/s

3、CPU

2.5GHz,32Core

4、網卡

2塊10GB網卡

5、機器台數

8台

6、每台機器上的資料節點數

每台16個資料節點。

2 效能指標資料

某個環境下測試得出的效能指標

以整數資料型別為例：

GP列存

postgres=# create table mmtest(id int) with (appendonly=true, blocksize=2097152, ORIENTATION=COLUMN);

NOTICE:Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.

HINT:The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.

CREATE TABLE

postgres=# insert into mmtest select generate_series(1,100000);

INSERT 0 100000

insert into mmtest select * from mmtest ;

...

postgres=# insert into mmtest select * from mmtest ;

INSERT 0 409600000

postgres=# select pg_size_pretty(pg_total_relation_size('mmtest'));

pg_size_pretty

----------------

3133 MB

(1 row)

postgres=# select count(*) from mmtest ;

count

-----------

819200000

(1 row)

Time: 779.444 ms

postgres=# select * from mmtest where id=0;

id

----

(0 rows)

Time: 422.538 ms

GP 行存

postgres=# create table mmtest1(id int)

postgres-# ;

NOTICE:Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.

HINT:The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.

CREATE TABLE

Time: 273.659 ms

postgres=# insert into mmtest1 select * from mmtest;

postgres=# select pg_size_pretty(pg_total_relation_size('mmtest1'));

pg_size_pretty

----------------

28 GB

(1 row)

postgres=# select count(*) from mmtest1 ;

count

-----------

819200000

(1 row)

Time: 1171.229 ms

postgres=# select * from mmtest1 where id=0;

id

----

(0 rows)

Time: 452.582 ms

PG 行存

create unlogged table mmtest(id int);

postgres=# insert into mmtest select generate_series(1,100000);

INSERT 0 100000

insert into mmtest select * from mmtest ;

...

postgres=# insert into mmtest select * from mmtest ;

INSERT 0 409600000

postgres=# select pg_size_pretty(pg_total_relation_size('mmtest'));

pg_size_pretty

----------------

28 GB

(1 row)

postgres=# select * from mmtest where id=0;

id

----

(0 rows)

Time: 56410.222 ms (00:56.410)

32並行

3.02秒

1、GP 列隱藏

單核 4000萬行/s 整型filter速度

整機效能 18.8億行/s 整型filter速度

(含掃描時間)

2、GP 行隱藏

單核 3700萬行/s 整型filter速度

整機效能 17.7億行/s 整型filter速度

(含掃描時間)

3、PG 行隱藏

單核 1500萬行/s 整型filter速度

整機效能 2.649億行/s 整型filter速度

(含掃描時間)

3 查詢效能評定

1、資料掃描時間

1.1 非記憶體點擊：

每個流程的掃描速度取決於（1. 行的大小，2. 單核的行處理速度：4000萬行/s，3. 單流程的讀速度 2.4GB/s），取最長時間。

每台主機的掃描速度上限是：4GB/s

least(記錄數/(總數據節點數*4000萬), 記錄數/(總CPU核心數*4000萬), 表大小/(資料節點主機數*4G), 表大小/(總數據節點數*2.4G))

1.2 記憶體點擊：

每個流程的掃描速度取決於（1. 行的大小，2. 單核的行處理速度：4000萬行/s，3. 單流程的讀速度 8.2GB/s），取最長時間。

每台主機的掃描速度上限是：37.5GB/s

根據每台主機的節點數可以推算出單機的掃描能力，以及整個集群的掃描能力。

least(記錄數/(總數據節點數*4000萬), 記錄數/(總CPU核心數*4000萬), 表大小/(資料節點主機數*37.5G), 表大小/(總數據節點數*8.2G))

1.3OSS掃描能力

阿裡雲還提供了一個OSS外部表格的功能。

在資料節點上的單個流程目前的存取速度約30MB/s。如果使用者開多個對話同時存取，速度線性升階。所以這塊的上限速度是網卡頻寬決定的。

least(主機數*網卡頻寬, 資料節點數*30MB/s)

2、資料運算時間

以整型為例，單核的行處理速度：4000萬行/s

根據資料節點數以及CPU單個核的處理能力評估整個HybridDB for PostgreSQL的處理能力。

least(總記錄數/(總數據節點數*4000萬), 總記錄數/(總數據節點主機CPU數*4000萬))

3、資料彙總時間

以整型COUNT彙總為例，單核的行處理速度：3300萬行/s。

根據資料節點數以及CPU單個核的處理能力評估整個HybridDB for PostgreSQL的處理能力。

least(總記錄數/(總數據節點數*3300萬), 總記錄數/(總數據節點主機CPU數*3300萬))

4、資料排序時間

根據資料節點數以及CPU單個核的處理能力評估。

還和work_mem，暫存檔案寫入速度，排序方法有關。

5、資料JOIN時間

根據資料節點數以及CPU單個核的處理能力評估。

和JOIN方法有關，HASH,MERGE,NESTLOOP速度評定方法不一。

hash每個表算一次，同時算一次HASH的時間。

merge每個表算一次SORT的時間。

NESTLOOP，內表需要算N次循環的時間。

JOIN還可能涉及資料重散發，需要估算重散發時間。

6、資料返回時間

按MASTER節點的網路頻寬，單個CPU的返回速度評定。

4 資料匯入效能評定

1、insert單步提交

並行寫，1萬條/s以內

2、insert單句批量提交

並行寫，10萬條/s以內

3、insert交易批量提交

並行寫，10萬條/s以內

4、COPY

並行寫，15萬條/s以內

5、OSS

阿裡雲還提供了一個OSS外部表格的功能。

在資料節點上的單個流程目前的存取速度約30MB/s。如果使用者開多個對話同時存取，速度線性升階。所以這塊的上限速度是網卡頻寬決定的。

least(主機數*網卡頻寬, 資料節點數*30MB/s)

6、gpfdist

與OSS類似。

6 資料重散發效能評定

資料重散發時間評定

根據總的網路頻寬評定，比如每台伺服器頻寬20G， 總共8台伺服器， 總共160G頻寬。

16GB的表，重散發需要16/(160/8) = 16/20 = 0.8秒

7 資料vacuum full(redistribute)效能評定

1、vacuumfull

涉及資料重散發，需要考慮資料重散發時間。

2、altertable redistribute.

如果重散發鍵不變，不涉及資料重散發，在節點內完成。

特別適合膨脹資料的收縮。

《優化器成本因數校對 -PostgreSQL explain cost constants alignment to timestamp》

《優化器成本因數校對(disk,ssd,memoryIO開銷精算) - PostgreSQL real seq_page_cost &random_page_cost in disks,ssd,memory》

相關產品：

1. 安全管家

2. 雲資料庫HybridDB版

3. 雲資料庫RDS

4. 雲端服務器ECS