PgSQL · 應用案例 · 阿裡雲 RDS PostgreSQL 高并發特性 vs 社群版本

背景

程序模型資料庫，需要為每個會話指派獨立的程序與之服務，在連接配接數非常多，且大都是活躍連接配接時，程序排程浪費或引入的開銷甚至遠遠大于實際任務需要的開銷（例如上下文切換，MEMCPY等），性能下降會較為嚴重。

PostgreSQL與Oracle Dedicate Server一樣，屬于程序模型。在非常高并發的情況下，性能會下降比較厲害，通常社群版本可以通過加連接配接池來解決，例如pgbouncer，但是加連接配接池也會帶來一些問題：

1、綁定變量無法很好的滿足，當然，PostgreSQL 11會增加類似Oracle cursor force的功能，内部将非綁定變量的SQL轉換為綁定變量。

《PostgreSQL 11 preview - 強制auto prepared statment開關(自動化plan cache)(類似Oracle cursor_sharing force)》

2、連接配接池會使得跳數增加，增加了延遲。

3、資料庫防火牆配置的變化。從直接控制應用端來源，變成了連接配接池端來源。（除非修改連接配接池層的代碼，做到來源IP和端口透傳）

Oracle為了解決性能問題，提出了shared server的概念，類似資料庫端的backend process pool，一個process可能服務于多個client。

PostgreSQL也可以如法炮制，比如阿裡雲RDS PG核心層面增加了内置的POOL。在高并發的情況下，性能好很多。

測試CASE

1、測試64 ~ 16384個并發

2、測試TPC-B，包含5億資料量。

3、測試logged table與unlogged table

4、測試對比社群PostgreSQL 10 與阿裡雲PostgreSQL 10

測試環境準備

1、資料庫使用huge page

《PostgreSQL Huge Page 使用建議 - 大記憶體主機、執行個體注意》

2、修改pgbench，支援超過1000個連接配接的測試

《PostgreSQL 11 preview - pgbench 支援大于1000連結(ppoll()代替select())》

https://commitfest.postgresql.org/18/1388/

《從PostgreSQL支援100萬個連接配接聊起》

如果使用ppoll，則pstack pgbench可以看到類似如下資訊

Thread 1 (Thread 0x7f3f4d89d840 (LWP 116621)):    
#0  0x00007f3f4ca4569d in poll () from /lib64/libc.so.6    
#1  0x00007f3f4d45a9cf in poll (__timeout=-1, __nfds=1, __fds=0x7ffcd6e13c80) at /usr/include/bits/poll2.h:46    
#2  pqSocketPoll (end_time=-1, forWrite=0, forRead=28675152, sock=<optimized out>) at fe-misc.c:1129    
#3  pqSocketCheck (conn=conn@entry=0x1b58c50, forRead=forRead@entry=1, forWrite=forWrite@entry=0, end_time=end_time@entry=-1) at fe-misc.c:1071    
#4  0x00007f3f4d45aa50 in pqWaitTimed (forRead=forRead@entry=1, forWrite=forWrite@entry=0, conn=conn@entry=0x1b58c50, finish_time=finish_time@entry=-1) at fe-misc.c:1003    
#5  0x00007f3f4d454012 in connectDBComplete (conn=0x1b58c50) at fe-connect.c:1902    
#6  PQconnectdbParams (keywords=<optimized out>, values=<optimized out>, expand_dbname=<optimized out>) at fe-connect.c:542    
#7  0x000000000040576a in doConnect ()    
#8  0x0000000000406e29 in threadRun ()    
#9  0x0000000000403a1b in main ()

3、修改系統配置，保證有足夠的fd, proc等

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

4、postgresql.conf 通用配置

listen_addresses = '0.0.0.0'    
max_connections = 30000    
superuser_reserved_connections = 13    
unix_socket_directories = '/tmp,.'    
tcp_keepalives_idle = 60    
tcp_keepalives_interval = 10    
tcp_keepalives_count = 0    
shared_buffers = 32GB    
huge_pages = on    
maintenance_work_mem = 1GB    
dynamic_shared_memory_type = posix    
vacuum_cost_delay = 0    
bgwriter_delay = 10ms    
bgwriter_lru_maxpages = 500    
effective_io_concurrency = 0    
max_parallel_workers_per_gather = 0    
wal_level = minimal    
fsync = on    
synchronous_commit = on    
full_page_writes = on    
wal_buffers = 32MB    
checkpoint_timeout = 15min    
max_wal_size = 64GB    
min_wal_size = 16GB    
checkpoint_completion_target = 0.1    
max_wal_senders = 0    
random_page_cost = 1.2    
log_destination = 'csvlog'    
logging_collector = on    
log_truncate_on_rotation = on    
log_checkpoints = on    
log_connections = on    
log_disconnections = on    
log_error_verbosity = verbose       
log_timezone = 'PRC'    
autovacuum = on    
log_autovacuum_min_duration = 0    
autovacuum_freeze_max_age = 900000000    
autovacuum_multixact_freeze_max_age = 900000000    
autovacuum_vacuum_cost_delay = 0ms    
vacuum_freeze_min_age = 500000    
vacuum_freeze_table_age = 1500000000    
vacuum_multixact_freeze_min_age = 5000000    
vacuum_multixact_freeze_table_age = 1500000000    
datestyle = 'iso, mdy'    
timezone = 'PRC'    
lc_messages = 'en_US.utf8'    
lc_monetary = 'en_US.utf8'    
lc_numeric = 'en_US.utf8'    
lc_time = 'en_US.utf8'    
default_text_search_config = 'pg_catalog.english'

5、社群版本與阿裡雲版本的差異配置

native

port = 1921

aliyun

port = 1999    
shared_preload_libraries = 'pg_concurrency_control.so'    
pg_concurrency_control.query_concurrency=64    
pg_concurrency_control.bigquery_concurrency=64    
pg_concurrency_control.transaction_concurrency=64    
pg_concurrency_control.autocommit_concurrency=64

測試TPC-B

TPC-B測試SQL如下

scale=5000

\set aid random(1, 100000 * :scale)    
\set bid random(1, 1 * :scale)    
\set tid random(1, 10 * :scale)    
\set delta random(-5000, 5000)    
BEGIN;    
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;    
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;    
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;    
UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;    
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);    
END;

logged table

1、初始化

./pgsql11/bin/pgbench -i -s 5000

2、表大小

postgres=# \dt+    
                          List of relations    
 Schema |       Name       | Type  |  Owner   |  Size   | Description     
--------+------------------+-------+----------+---------+-------------    
 public | pgbench_accounts | table | postgres | 63 GB   |     
 public | pgbench_branches | table | postgres | 216 kB  |     
 public | pgbench_history  | table | postgres | 0 bytes |     
 public | pgbench_tellers  | table | postgres | 2200 kB |     
(4 rows)

3、社群版本測試腳本如下

vi test_native.sh    
    
#!/bin/bash    
export PGHOST=/tmp    
export PGPORT=1921    
export PGUSER=postgres    
export PGDATABASE=postgres    
    
Y=32    
for ((i=1;i<=7;i++))    
do    
X=$((Y*2))    
psql -c "vacuum freeze"    
psql -c "checkpoint"    
./pgsql11/bin/pgbench -M prepared -n -r -P 3 -c $X -j 64 -T 300 > ./test_native_$X.log 2>&1    
Y=X    
done    
    
psql -c "vacuum freeze"    
psql -c "checkpoint"    
./pgsql11/bin/pgbench -M prepared -n -r -P 3 -c 8192 -j 128 -T 600 > ./test_native_8192.log 2>&1    
    
psql -c "vacuum freeze"    
psql -c "checkpoint"    
./pgsql11/bin/pgbench -M prepared -n -r -P 3 -c 16384 -j 256 -T 600 > ./test_native_16384.log 2>&1

測試方法

chmod 700 ./test_native.sh     
nohup ./test_native.sh >/dev/null 2>&1 &

5、阿裡雲版本測試腳本如下

vi test_aliyun.sh    
    
#!/bin/bash    
export PGHOST=/tmp    
export PGPORT=1999    
export PGUSER=postgres    
export PGDATABASE=postgres    
    
Y=32    
for ((i=1;i<=7;i++))    
do    
X=$((Y*2))    
psql -c "vacuum freeze"    
psql -c "checkpoint"    
./pgsql11/bin/pgbench -M prepared -n -r -P 3 -c $X -j 64 -T 300 > ./test_aliyun_$X.log 2>&1    
Y=X    
done    
    
psql -c "vacuum freeze"    
psql -c "checkpoint"    
./pgsql11/bin/pgbench -M prepared -n -r -P 3 -c 8192 -j 128 -T 600 > ./test_aliyun_8192.log 2>&1    
    
psql -c "vacuum freeze"    
psql -c "checkpoint"    
./pgsql11/bin/pgbench -M prepared -n -r -P 3 -c 16384 -j 256 -T 600 > ./test_aliyun_16384.log 2>&1

chmod 700 ./test_aliyun.sh     
nohup ./test_aliyun.sh >/dev/null 2>&1 &

unlogged table

./pgsql11/bin/pgbench -i -s 5000 --unlogged-tables

2、修改資料庫配置

vi $PGDATA/postgresql.conf    
    
synchronous_commit = off    
    
pg_ctl reload

3、測試同樣的腳本

性能對比

1 logged table對比

1、TPS對比

連接配接數	社群版本TPS	阿裡雲版本TPS	(過濾首尾幹擾值)
64	69216	67853	無幹擾
128	69211	65712
256	62964	62775
512	44595	53382	46141	54988
1024	35055	44295	37022	48679
2048	26791	38881	30327	44358
4096	24218	26990	32023	39086
8192	7064	24304	18611	34316
16384	5046	12478	10020	29499

1.6萬并發時，約3倍于社群版本。

2、事務整體RT對比

	社群版本RT	阿裡雲版本RT
	0.923 ms	0.941 ms
	1.839 ms	1.936 ms
	4.010 ms	4.021 ms
	11.151 ms	9.269 ms
	27.475 ms	21.070 ms
	67.295 ms	46.063 ms
	127.923 ms	104.689 ms
	999.236 ms	239.466 ms
	1594.185 ms	577.913 ms

3、實際SQL RT對比


	0.428 ms	0.465 ms
	0.698 ms	0.734 ms
	1.784 ms	1.658 ms
	4.736 ms	4.378 ms
	11.082 ms	8.664 ms
	37.258 ms	8.007 ms
	65.486 ms	7.395 ms
	818.411 ms	6.472 ms
	1183.571 ms	4.927 ms

1.6萬連接配接時，真實SQL響應速度約240倍于社群版本。

4、RT 标準方差對比

	社群版本RT DEV	阿裡雲版本RT DEV
	2.960 ms	2.863 ms
	7.559 ms	4.914 ms
	6.595 ms	6.090 ms
	11.810 ms	8.704 ms
	30.656 ms	46.411 ms
	88.371 ms	68.239 ms
	183.815 ms	140.255 ms
	20114.612 ms	345.584 ms
	2404.222 ms	1116.238 ms

5、建立完所有連接配接的耗時對比

	社群版本	阿裡雲版本
	0 s

	4.8 s	5 s
	8.9 s	11.3 s
	18.5 s	27.4 s
	36.3 s	37.8 s
	73.5 s	93.6 s
	150.9 s	168.6 s
	306 s	341.8 s

6、釋放完所有連接配接的耗時對比









	594 s	9 s
	21 s	24 s

2 unlogged table對比


99086	95932
86807	86719
69805	74371	70766	75143
49147	59423	50369	61153
42295	45883	44798	48910
32147	38698	36729	44552
23556	27604	31504	38334
17037	24524	22937	34553
196	12668	1943	30273


	0.644 ms	0.666 ms
	1.466 ms
	3.617 ms	3.391 ms
	10.115 ms	8.343 ms
	22.761 ms	20.864 ms
	55.771 ms	45.903 ms
	130.195 ms	107.858 ms
	356.904 ms	239.312 ms
	66640.630 ms	570.207 ms


	0.475 ms	0.501 ms
	0.934 ms	0.854 ms
	2.109 ms	1.842 ms
	4.656 ms	4.587 ms
	9.837 ms	8.69 ms
	36.882 ms	7.928 ms
	67.513 ms	7.522 ms
	201.208 ms	6.536 ms
	65428.243 ms	4.811 ms


	2.941 ms	1.767 ms
	4.445 ms	2.763 ms
	5.515 ms	2.775 ms
	11.424 ms	4.447 ms
	28.950 ms	16.575 ms
	87.051 ms	52.400 ms
	200.132 ms	149.614 ms
	403.771 ms	358.461 ms
	462277.689 ms	1161.376 ms




	4.9 s	5.3 s
	9.4 s	10.2 s
		20.2 s
	37.6 s	40 s
	75 s	81.3 s
	151.6 s	168.4 s
	312.1 s	341.5 s








	3 s
	6 s
	3312 s	27 s

小結

阿裡雲RDS PG，采用與Oracle Shared Server模式類似的方案，解決了程序模式在高并發的情況下性能下降的問題。

在超過1萬個活躍并發的情況下，阿裡雲RDS PG的TPC-B測試名額依舊能夠保持15萬左右的QPS (消除幹擾項)，吞吐能力是社群版本的3倍。同時，在低并發的情況下，性能不減，與社群版本相當。

具體測試結果分析：

1、阿裡雲RDS PG在高并發下，TPS相比社群版本好很多，更加平穩。

2、阿裡雲RDS PG引入了POOL機制後，響應延遲，抖動相比社群版本低了很多。

3、啟用POOL後，整個事務的RT，相比社群版本降低，使得整個處理吞吐得到提升。

4、啟用POOL機制，使得一個事務中，真正執行SQL的時間大大縮短。同時還避免了鎖等待的問題。

16384個連接配接，社群版本

1.750  BEGIN;    
 21.531  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;    
  0.745  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;    
461.077  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;    
700.583  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;    
  1.958  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);    
408.864  END;

16384個連接配接，阿裡雲版本

559.291  BEGIN;    
  2.359  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;    
  1.223  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;    
  1.191  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;    
  2.310  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;    
  0.981  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);    
 13.695  END;

對比以上兩個版本的事務BEGIN的耗費時間、SQL執行時間的分布：

社群版本的SQL執行時間耗時更高（基本達到了500毫秒左右）；

阿裡雲的PG版本，SQL執行時間非常短（基本都在1毫秒左右）。

實際的DML SQL執行越久，持鎖就越久，并發1萬多時，社群版本PG出現較多WAITING狀态，就可以說明問題。

0:00.18 postgres: postgres postgres [local] UPDATE waiting    
0:02.62 postgres: postgres postgres [local] UPDATE waiting    
0:00.15 postgres: postgres postgres [local] UPDATE waiting    
0:00.17 postgres: postgres postgres [local] UPDATE waiting    
0:00.12 postgres: postgres postgres [local] UPDATE waiting    
0:00.11 postgres: postgres postgres [local] UPDATE waiting    
..............................  
0:00.13 postgres: postgres postgres [local] COMMIT            
0:00.13 postgres: postgres postgres [local] UPDATE waiting    
0:00.13 postgres: postgres postgres [local] UPDATE waiting    
0:00.16 postgres: postgres postgres [local] UPDATE waiting    
0:00.14 postgres: postgres postgres [local] UPDATE waiting    
.....................

阿裡雲RDS PG内置POOL，不會導緻SQL執行時間變長。是以有效的避免了持有資源鎖的問題，是的真實的SQL RT非常的平穩。

5、啟用POOL後，16384個連接配接高并發下，收尾時長縮短。從3312秒縮短到了27秒。

6、程序模式，建立連接配接比較耗時，如果業務上需要短時間内建立大量連接配接，也是一個瓶頸。比如建立16384個連接配接，串行建立，全部建立完16384個連接配接大概需要花費300秒。這樣的業務，建議采用業務層連接配接池，并且配置較少的後端連接配接。

7、pgbench在統計TPS時，從所有連接配接建立完成，到所有連接配接退出，這之間産生的TPS。當需要建立很多連接配接或釋放很多連接配接時，可能會耗時比較久，導緻實際測試的性能不準，特别是在8000個連接配接以上時，斷開連接配接過程中，TPS下降比較明顯，并且會被統計進去，實測600秒，到1000多秒才完成統計，詳見LOG。

8、阿裡雲RDS PG内置POOL，相比外置連接配接池，還有一個好處是“不會影響綁定變量的使用，也不會引入新的跳數，同時不會影響資料庫pg_hba.conf防火牆的配置”。

在超過1萬個活躍并發的情況下，阿裡雲RDS PG的TPC-B測試名額依舊能夠保持15萬左右的QPS (消除幹擾項)，吞吐能力是社群版本的3倍。真實SQL執行響應速度240倍于社群版本。同時低并發的情況下，性能不減，與社群版本相當。

歡迎使用阿裡雲RDS PG，我們會一如既往的優化，提高使用者使用感受。這個特性将同時支援

阿裡雲RDS PG

以及

RDS PPAS

兩個産品。

參考

《PostgreSQL pgbench : 冒号處理》《PostgreSQL pgbench SQL RT 與事務RT 淺析》

除此以外阿裡雲RDS PG還提供了很多企業級特性，比較典型的如：

1、varbitx，海量資料實時使用者畫像

《阿裡雲RDS PostgreSQL varbitx實踐 - 流式标簽 (閱後即焚流式批量計算) - 萬億級，任意标簽圈人，毫秒響應》《阿裡雲RDS for PostgreSQL varbitx插件與實時畫像應用場景介紹》《基于阿裡雲 RDS PostgreSQL 打造實時使用者畫像推薦系統(varbitx)》《驚天性能！單RDS PostgreSQL執行個體支撐 2000億 - 實時标簽透視案例 (含dblink異步并行調用)》《門禁廣告銷售系統需求剖析與 PostgreSQL資料庫實作》

2、oss二級存儲，冷熱資料分離存儲

《打造雲端流計算、線上業務、資料分析的業務資料閉環 - 阿裡雲RDS、HybridDB for PostgreSQL最佳實踐》《阿裡雲RDS PostgreSQL OSS 外部表實踐 - (dblink異步調用封裝并行) 從OSS并行導入資料》《強制資料分布與導出prefix - 阿裡雲pg, hdb pg oss快速資料規整外部表導出實踐案例》《Greenplum insert的性能(單步\批量\copy) - 暨推薦使用gpfdist、阿裡雲oss外部表并行導入》《阿裡雲RDS PostgreSQL OSS 外部表實踐 - (dblink異步調用封裝并行) 資料并行導出到OSS》《ApsaraDB的左右互搏(PgSQL+HybridDB+OSS) - 解決OLTP+OLAP混合需求》

PgSQL · 應用案例 · 阿裡雲 RDS PostgreSQL 高并發特性 vs 社群版本

背景

測試CASE

測試環境準備

測試TPC-B

logged table

unlogged table

性能對比

1 logged table對比

1、TPS對比

2、事務整體RT對比

3、實際SQL RT對比

4、RT 标準方差對比

5、建立完所有連接配接的耗時對比

6、釋放完所有連接配接的耗時對比

2 unlogged table對比

小結

參考

繼續閱讀

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述