HTAP資料庫PostgreSQL場景與效能測試之21-(OLTP+OLAP)排序、建索引

德哥發表於2017-11-13
資料庫SQL排序索引

標籤

PostgreSQL , HTAP , OLTP , OLAP , 場景與效能測試

背景

PostgreSQL是一個歷史悠久的資料庫，歷史可以追溯到1973年，最早由2014計算機圖靈獎得主，關聯式資料庫的鼻祖Michael_Stonebraker 操刀設計，PostgreSQL具備與Oracle類似的功能、效能、架構以及穩定性。

pic

PostgreSQL社群的貢獻者眾多，來自全球各個行業，歷經數年，PostgreSQL 每年釋出一個大版本，以持久的生命力和穩定性著稱。

2017年10月，PostgreSQL 推出10 版本，攜帶諸多驚天特性，目標是勝任OLAP和OLTP的HTAP混合場景的需求：

《最受開發者歡迎的HTAP資料庫PostgreSQL 10特性》

1、多核並行增強

2、fdw 聚合下推

3、邏輯訂閱

4、分割槽

5、金融級多副本

6、json、jsonb全文檢索

7、還有外掛化形式存在的特性，如 向量計算、JIT、SQL圖計算、SQL流計算、分散式平行計算、時序處理、基因測序、化學分析、影像分析 等。

pic

在各種應用場景中都可以看到PostgreSQL的應用：

pic

PostgreSQL近年來的發展非常迅猛，從知名資料庫評測網站dbranking的資料庫評分趨勢，可以看到PostgreSQL向上發展的趨勢：

pic

從每年PostgreSQL中國召開的社群會議，也能看到同樣的趨勢，參與的公司越來越多，分享的公司越來越多，分享的主題越來越豐富，橫跨了 傳統企業、網際網路、醫療、金融、國企、物流、電商、社交、車聯網、共享XX、雲、遊戲、公共交通、航空、鐵路、軍工、培訓、諮詢服務等 行業。

接下來的一系列文章，將給大家介紹PostgreSQL的各種應用場景以及對應的效能指標。

環境

環境部署方法參考：

《PostgreSQL 10 + PostGIS + Sharding(pg_pathman) + MySQL(fdw外部表) on ECS 部署指南(適合新使用者)》

阿里雲 ECS：56核，224G，1.5TB*2 SSD雲盤。

作業系統：CentOS 7.4 x64

資料庫版本：PostgreSQL 10

PS：ECS的CPU和IO效能相比物理機會打一定的折扣，可以按下降1倍效能來估算。跑物理主機可以按這裡測試的效能乘以2來估算。

場景 – 排序、建索引 (OLTP+OLAP)

1、背景

1、OLTP中，排序通常是小資料集的排序。

2、OLAP中，通常需要對大結果集進行排序，取TOP。

3、排序的快慢，還直接影響建索引的速度。（提一下，PostgreSQL支援 CONCURRENTLY 構建索引，建索引時不會堵塞DML。）

4、OLAP中，取TOP還有一些簡便高效的方法：估算、HLL、取樣、統計資訊等。

《秒級任意維度分析1TB級大表 – 通過取樣估值滿足高效TOP N等統計分析需求》

《Greenplum 最佳實踐 – 估值外掛hll的使用(以及hll分式聚合函式優化)》

《PostgreSQL hll (HyperLogLog) extension for “State of The Art Cardinality Estimation Algorithm” – 1》

《PostgreSQL hll (HyperLogLog) extension for “State of The Art Cardinality Estimation Algorithm” – 2》

《PostgreSQL hll (HyperLogLog) extension for “State of The Art Cardinality Estimation Algorithm” – 3》

《妙用explain Plan Rows快速估算行》

2、設計

1、小資料量排序，1萬條資料。

2、大資料量排序，1億條資料。

3、大資料量建立索引效率，1億條資料。

4、大資料估值TOP N，1億條資料。

5、大資料、複合索引、求任意一個GROUP c1的TOP c2的ID，1億條資料。

3、準備測試表

create table t_small (id int);  
  
create table t_large (id int);  
  
create table t_estimate (id int);  
  
create table t_idx_test (id int, c1 int, c2 int);

4、準備測試函式(可選)

5、準備測試資料

1、小資料量排序，1萬條資料。

insert into t_small select generate_series(1,10000);

2、大資料量排序，1億條資料。

insert into t_large select generate_series(1,100000000);

4、大資料估值TOP N，1億條資料。

vi test.sql  
  
set id random_gaussian(1, 1000000, 2.5)  
insert into t_estimate values (:id);  

pgbench -M prepared -n -r -P 5 -f ./test.sql -c 50 -j 50 -t 2000000  

analyze t_estimate;

5、大資料、複合索引、求任意一個GROUP c1的TOP c2的ID，1億條資料。

insert into t_idx_test select id, random()*1000, random()*900000 from generate_series(1,100000000) t(id);  
create index idx_t_idx_test on t_idx_test (c1,c2);

6、準備測試指令碼

vi test.sql  
  
set c1 random(1,1000)  
select id from t_idx_test where c1=:c1 order by c2 desc limit 1;

壓測

CONNECTS=112  
TIMES=300  
export PGHOST=$PGDATA  
export PGPORT=1999  
export PGUSER=postgres  
export PGPASSWORD=postgres  
export PGDATABASE=postgres  
  
pgbench -M prepared -n -r -f ./test.sql -P 5 -c $CONNECTS -j $CONNECTS -T $TIMES

7、測試

1、小資料量排序

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t_small order by id desc limit 1;  
                                                          QUERY PLAN  
------------------------------------------------------------------------------------------------------------------------------  
 Limit  (cost=195.00..195.00 rows=1 width=4) (actual time=2.580..2.581 rows=1 loops=1)  
   Output: id  
   Buffers: shared hit=45  
   ->  Sort  (cost=195.00..220.00 rows=10000 width=4) (actual time=2.580..2.580 rows=1 loops=1)  
         Output: id  
         Sort Key: t_small.id DESC  
         Sort Method: top-N heapsort  Memory: 25kB  
         Buffers: shared hit=45  
         ->  Seq Scan on public.t_small  (cost=0.00..145.00 rows=10000 width=4) (actual time=0.006..1.184 rows=10000 loops=1)  
               Output: id  
               Buffers: shared hit=45  
 Planning time: 0.027 ms  
 Execution time: 2.591 ms  
(13 rows)

2、大資料量排序

alter table t_large set (parallel_workers =32);  
set parallel_setup_cost =0;  
set parallel_tuple_cost =0;  
set max_parallel_workers_per_gather =32;  
set work_mem =`1GB`;  
explain select * from t_large order by id desc limit 1;  
  
postgres=# explain select * from t_large order by id desc limit 1;  
                                         QUERY PLAN  
---------------------------------------------------------------------------------------------  
 Limit  (cost=810844.97..810845.00 rows=1 width=4)  
   ->  Gather Merge  (cost=810844.97..3590855.42 rows=100000032 width=4)  
         Workers Planned: 32  
         ->  Sort  (cost=810844.14..818656.64 rows=3125001 width=4)  
               Sort Key: id DESC  
               ->  Parallel Seq Scan on t_large  (cost=0.00..473728.01 rows=3125001 width=4)  
(6 rows)  
  
postgres=# select * from t_large order by id desc limit 1;  
    id  
-----------  
 100000000  
(1 row)  
Time: 1482.964 ms (00:01.483)

3、大資料量建立索引效率

postgres=# create index idx_t_large_id on t_large(id);  
CREATE INDEX  
Time: 37937.482 ms (00:37.937)

4、大資料估值TOP N

postgres=# select most_common_vals, most_common_freqs from pg_stats where tablename=`t_estimate`;  
-[ RECORD 1 ]-----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
most_common_vals  | {354316,354445,404899,431899,451975,525707,545934,552219,631936,654703,686785,52824,97919,100231,134912,137688,158267,161541,171349,182376,182892,186086,192265,196224,197934,206937,207098,208325,213459,218788,218939,225221,226377,238291,239857,245513,245868,250632,250836,251535,251972,254658,254998,255236,256667,259600,260215,263041,266027,268086,271091,271490,271520,272019,272459,282086,286285,287848,288015,288233,288310,288344,288605,289181,289901,291581,296327,301385,301631,304765,304923,306094,306309,307188,312000,313190,313449,315581,317808,320374,320769,322517,322889,323389,326463,326738,330239,331553,334323,335451,335588,337521,338605,342766,344188,344662,344730,345081,345096,346053}  
most_common_freqs | {0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05}  
  
Time: 1.274 ms

5、大資料、複合索引、求任意一個GROUP c1的TOP c2的ID，1億條資料。

transaction type: ./test.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 112  
number of threads: 112  
duration: 300 s  
number of transactions actually processed: 203376551  
latency average = 0.165 ms  
latency stddev = 0.324 ms  
tps = 677702.381595 (including connections establishing)  
tps = 677897.901150 (excluding connections establishing)  
script statistics:  
 - statement latencies in milliseconds:  
         0.002  set c1 random(1,1000)  
         0.168  select id from t_idx_test where c1=:c1 order by c2 desc limit 1;

TPS： 677897

5、大資料、複合索引、求任意一個GROUP c1的TOP c2的ID，1億條資料。TPS：677897

平均響應時間： 0.165 毫秒

1、小資料量排序，1萬條資料，排序耗時 2.6 毫秒。

2、大資料量排序，1億條資料，排序耗時 1.48 秒。

3、大資料量建立索引效率，1億條資料，建立索引耗時 37.9 秒。

4、大資料估值TOP N，1一條資料，估算TOP N，耗時 1.27 毫秒。

5、大資料、複合索引、求任意一個GROUP c1的TOP c2的ID，1億條資料。平均響應時間： 0.165 毫秒。

參考

《PostgreSQL、Greenplum 應用案例寶典《如來神掌》 – 目錄》

《資料庫選型之 – 大象十八摸 – 致 架構師、開發者》

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

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

https://www.postgresql.org/docs/10/static/pgbench.html


相關文章