标簽
PostgreSQL , 内置連接配接池 , pgbouncer , postgrespro
https://github.com/digoal/blog/blob/master/201805/20180521_03.md#%E8%83%8C%E6%99%AF 背景
PostgreSQL 與Oracle dedicate server 一樣采用了線程模式，在連接配接數非常多時，性能下降會比較嚴重。
通常社群使用者的做法是使用連接配接池，比如pgbouncer，但是使用PGbouncer也會引入一些使用上的不便利，比如transaction模式不能使用綁定變量等。在以下文章中做過一些較為詳細的分析。
《阿裡雲 RDS PostgreSQL 高并發特性 vs 社群版本 (1.6萬并發: 3倍吞吐，240倍響應速度)》Postgrespro是俄羅斯的一家PG生态公司，
《透過postgrespro看PostgreSQL的附加功能》内置連接配接池在他們的TODO清單有看到，最近放出了一版devel版本。
https://github.com/digoal/blog/blob/master/201805/20180521_03.md#postgres-buildin-pool-%E7%89%88%E6%9C%AC%E5%AE%89%E8%A3%85 postgres buildin pool 版本安裝
1、下載下傳源碼,切換分支
git clone https://github.com/postgrespro/postgresql.builtin_pool
cd postgresql.builtin_pool
git checkout conn_pool
git branch conn_pool
2、編譯安裝
./configure --prefix=/home/digoal/pgsql11_pool
make -j 128
make install
3、修改環境變量
vi env_pg11_pool.sh
export PS1="$USER@`/bin/hostname -s`-> "
export PGPORT=4001
export PGDATA=/data01/pg/pg_root$PGPORT
export LANG=en_US.utf8
export PGHOME=/home/digoal/pgsql11_pool
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=`date +"%Y%m%d%H%M"`
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
export PGHOST=$PGDATA
export PGUSER=postgres
export PGDATABASE=postgres
alias rm='rm -i'
alias ll='ls -lh'
unalias vi
4、設定環境變量
. ./env_pg11_pool.sh
5、初始化資料庫
initdb -D $PGDATA -U postgres -E UTF8 --locale=en_US.utf8 -X /data02/pg/pg_wal_4001
6、配置資料庫參數
cd $PGDATA
vi postgresql.conf
listen_addresses = '0.0.0.0'
port = 4001
max_connections = 20000
superuser_reserved_connections = 13
unix_socket_directories = '/tmp,.'
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 10
shared_buffers = 32GB
maintenance_work_mem = 1GB
dynamic_shared_memory_type = posix
vacuum_cost_delay = 0
bgwriter_delay = 10ms
bgwriter_lru_maxpages = 500
bgwriter_lru_multiplier = 5.0
effective_io_concurrency = 0
wal_level = minimal
synchronous_commit = off
full_page_writes = off
wal_buffers = 128MB
wal_writer_delay = 10ms
checkpoint_timeout = 25min
max_wal_size = 64GB
min_wal_size = 16GB
checkpoint_completion_target = 0.1
max_wal_senders = 0
random_page_cost = 1.1
log_destination = 'csvlog'
logging_collector = on
log_truncate_on_rotation = on
log_checkpoints = on
log_error_verbosity = verbose
log_timezone = 'PRC'
datestyle = 'iso, mdy'
timezone = 'PRC'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
default_text_search_config = 'pg_catalog.english'
内置連接配接池參數如下
# pool 配置
session_pool_size=56 # 最好與CPU核數一緻 ，如果有很多pool ports，可以考慮設小一點。
session_pool_ports=0 # 如果配置為0，表示shared server與dedicate server共用一個端口, port = 4001
# 如果配置為1，表示port = 4001為deadcate server port，port+1 為shared server ports.
# 如果配置為大于1，port+1, port+2, .... 為shared server ports.
# 如果要對應用透明，建議配置為0， 但是最佳實踐建議配置為大于1，比如每對user/dbname 一個port。
# postgres資料庫不受pool限制，一律使用dedicate server.
https://github.com/digoal/blog/blob/master/201805/20180521_03.md#%E6%9E%B6%E6%9E%84 架構
7、啟動資料庫
pg_ctl start
https://github.com/digoal/blog/blob/master/201805/20180521_03.md#%E8%BF%9E%E6%8E%A5%E6%B1%A0%E5%8F%82%E6%95%B0%E4%BB%8B%E7%BB%8D 連接配接池參數介紹
1、pool包含兩個參數
# pool 配置
session_pool_size=56 # 最好與CPU核數一緻 ，如果有很多pool ports，可以考慮設小一點。
session_pool_ports=0 # 如果配置為0，表示shared server與dedicate server共用一個端口, port = 4001
# 如果配置為1，表示port = 4001為deadcate server port，port+1 為shared server ports.
# 如果配置為大于1，port+1, port+2, .... 為shared server ports.
# 如果要對應用透明，建議配置為0， 但是最佳實踐建議配置為大于1，比如每對user/dbname 一個port。
# postgres資料庫不受pool限制，一律使用dedicate server.
在guc.c裡面可以看到這兩個參數的介紹
{
{"session_pool_size", PGC_POSTMASTER, CONN_AUTH_SETTINGS,
gettext_noop("Sets number of backends serving client sessions."),
gettext_noop("If non-zero then session pooling will be used: "
"client connections will be redirected to one of the backends and maximal number of backends is determined by this parameter."
"Launched backend are never terminated even in case of no active sessions.")
},
&SessionPoolSize,
10, 0, INT_MAX,
NULL, NULL, NULL
},
{
{"session_pool_ports", PGC_POSTMASTER, CONN_AUTH_SETTINGS,
gettext_noop("Number of session ports = number of session pools."),
gettext_noop("Number of extra ports which PostgreSQL will listen to accept client session. Each such port has separate session pool."
"It is intended that each port corresponds to some particular database/user combination, so that all backends in this session "
"pool will handle connection accessing this database. If session_pool_port is non zero then postmaster will always spawn dedicated (non-pooling) "
" backends at the main Postgres port. If session_pool_port is zero and session_pool_size is not zero, then sessions (pooled connection) will be also "
"accepted at main port. Session pool ports are allocated sequentially: if Postgres main port is 5432 and session_pool_ports is 2, "
"then ports 5433 and 5434 will be used for connection pooling.")
},
&SessionPoolPorts,
0, 0, MAX_SESSION_PORTS,
NULL, NULL, NULL
},
2、如果是postgres庫，不使用pool模式，使用dedidate server模式。
區分是否postgres庫的代碼
src/backend/tcop/postgres.c
/* Serve all conections to "postgres" database by dedicated backends */
if (SessionPoolSize != 0 && strcmp(dbname, "postgres") == 0) // 連接配接postgres，一律使用dedicate server, 友善DBA使用者上去維護 （在所有pool backend process都activate時，保證能連接配接資料庫）
{
elog(LOG, "Backend is dedicated");
SessionPoolSize = 0;
closesocket(SessionPoolSock);
SessionPoolSock = PGINVALID_SOCKET;
}
/* Assign session for this backend in case of session pooling */
if (SessionPoolSize != 0)
{
MemoryContext oldcontext;
ActiveSession = (SessionContext*)calloc(1, sizeof(SessionContext));
ActiveSession->memory = AllocSetContextCreate(TopMemoryContext,
"SessionMemoryContext",
ALLOCSET_DEFAULT_SIZES);
oldcontext = MemoryContextSwitchTo(ActiveSession->memory);
ActiveSession->id = CreateSessionId();
ActiveSession->port = MyProcPort;
ActiveSession->eventSet = FeBeWaitSet;
BackendPort = MyProcPort;
MemoryContextSwitchTo(oldcontext);
}
https://github.com/digoal/blog/blob/master/201805/20180521_03.md#%E6%B5%8B%E8%AF%95pg%E5%86%85%E7%BD%AE%E8%BF%9E%E6%8E%A5%E6%B1%A0%E6%98%AF%E4%BB%80%E4%B9%88%E6%A8%A1%E5%BC%8Ftransaction-%E6%A8%A1%E5%BC%8F 測試PG内置連接配接池是什麼模式(transaction 模式)
1、建立一個普通使用者與庫
create role digoal login;
create database digoal owner digoal;
2、目前内置連接配接池的POOL模式為事務級 pool。同一個backend process，某個活躍會話的事務執行結束後，對應backend process的資源即可給同一backend process上的其他session利用。
3、設定為隻有1個BACKEND PROCESS
session_pool_size=1
重新開機資料庫
4、建立測試表
create table a (id int, info text);
insert into a values (1,'test');
5、SESISON A:
檢視它的backend process的pid, 同時開啟一個事務
digoal=> select pg_backend_pid();
pg_backend_pid
----------------
56112
(1 row)
digoal=> begin;
BEGIN
digoal=> select * from a;
id | info
----+------
1 | test
(1 row)
6、SESISON B:
連接配接資料庫，堵塞，因為隻有1個backend process，并且這個backend process目前繁忙。
psql -p 4001 digoal digoal
hang
7、SESISON A:
結束會話
end;
8、SESISON B:
連接配接成功，檢視它的backend process的pid，與session a的backend process的pid一緻，是以會話A與B是共用一個backend process的。
digoal=> select pg_backend_pid();
pg_backend_pid
----------------
56112
(1 row)
9、SESISON A:
開啟事務
digoal=> begin;
BEGIN
digoal=> select * from a;
id | info
----+------
1 | test
(1 row)
10、SESISON B:
執行SQL處于等待狀态
digoal=> select count(*) from pg_stat_activity ;
hang
結論：Postgrespro pool模式為transaction模式，事務結束後，這個backend process才能給映射到這個backend process的其他會話使用。
目前的版本：session一定映射到一個backend process後，就不能漂移給其他的backend process，是以以上CASE，即使我們有多個shared backend process，實際上SESSION B也不能用到其他空閑中的backend process，因為它不能漂移到其他的backend process。
https://github.com/digoal/blog/blob/master/201805/20180521_03.md#postgres-pool%E7%89%88%E6%9C%AC%E7%9B%AE%E5%89%8D%E5%AD%98%E5%9C%A8%E7%9A%84%E4%B8%80%E4%BA%9B%E9%97%AE%E9%A2%98 postgres pool版本目前存在的一些問題
https://github.com/digoal/blog/blob/master/201805/20180521_03.md#%E9%97%AE%E9%A2%981 問題1
discard all 釋放同一個backend process下的所有變量，并不是目前session自己的變量，是以會導緻綁定到這個backend process的所有session的變量丢失。
例如造成其他會話已經建立的prepared statements丢失，異常。
測試
digoal=> \h discard
Command: DISCARD
Description: discard session state
Syntax:
DISCARD { ALL | PLANS | SEQUENCES | TEMPORARY | TEMP }
1、SESSION A:
digoal=> select pg_backend_pid();
pg_backend_pid
----------------
56112
(1 row)
digoal=> prepare p1 (int) as select * from a where id=$1;
PREPARE
digoal=> execute p1(1);
id | info
----+------
1 | test
(1 row)
2、SESSION B:
digoal=> select pg_backend_pid();
pg_backend_pid
----------------
56112
(1 row)
digoal=> execute p1(1);
ERROR: prepared statement "p1" does not exist
discard all
digoal=> discard all;
DISCARD ALL
3、SESSION A:
digoal=> execute p1(1);
ERROR: prepared statement "p1" does not exist
https://github.com/digoal/blog/blob/master/201805/20180521_03.md#%E9%97%AE%E9%A2%982 問題2
ctrl_c退出會話，會導緻資料庫crash , recovery.
這個用pgbench壓測，并ctrl_c pgbench就可以發現問題
https://github.com/digoal/blog/blob/master/201805/20180521_03.md#%E9%85%8D%E7%BD%AEpgbench%E5%8E%8B%E6%B5%8B%E6%94%AF%E6%8C%81%E8%B6%85%E8%BF%871000%E4%B8%AA%E8%BF%9E%E6%8E%A5 配置pgbench壓測支援超過1000個連接配接
1、編譯pgbench，支援超過1000個測試連接配接，參考如下方法
《PostgreSQL pgbench 支援100萬連接配接》用一個新的PostgreSQL編譯一下pgbench，conn_pool版本的pg 11版本可能太老，沒有辦法融合這個pgbench的patch
wget https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2
tar -jxvf postgresql-snapshot.tar.bz2
cd postgresql-11devel
patch pgbench參考
假設我把它編譯到了 pgsql11/bin/pgbench ，可以拷貝到conn_pool版本的bin目錄中
cp pgsql11/bin/pgbench ./pgsql11_pool/bin/
用pgbench壓測，可以校驗一下前面提到的，如果session數超過pool的backend process數量，那麼多個session 會映射到同一個backend process
當discard all時，影響的是同一個backend process下的所有session
修改配置
session_pool_size=56
session_pool_ports=0
重新開機資料庫
壓測，開啟8000個連接配接。
pgbench -i -s 20 -U digoal digoal
pgbench -M prepared -n -r -P 1 -p 4001 -c 8000 -j 8000 -T 12000 -U digoal digoal
壓測過程中，discard all
SESSION A:
psql -p 4001 -U digoal digoal
discard all;
觀察到pgbench 報錯的自有一部分連接配接
progress: 460.0 s, 53016.7 tps, lat 38.635 ms stddev 4.520
client 1253 aborted in command 4 (SQL) of script 0; ERROR: prepared statement "P0_4" does not exist
client 929 aborted in command 4 (SQL) of script 0; ERROR: prepared statement "P0_4" does not exist
client 873 aborted in command 4 (SQL) of script 0; ERROR: prepared statement "P0_4" does not exist
client 1264 aborted in command 4 (SQL) of script 0; ERROR: prepared statement "P0_4" does not exist
client 369 aborted in command 4 (SQL) of script 0; ERROR: prepared statement "P0_4" does not exist
client 201 aborted in command 4 (SQL) of script 0; ERROR: prepared statement "P0_4" does not exist
client 593 aborted in command 4 (SQL) of script 0; ERROR: prepared statement "P0_4" does not exist
client 152 aborted in command 4 (SQL) of script 0; ERROR: prepared statement "P0_4" does not exist
client 257 aborted in command 4 (SQL) of script 0; ERROR: prepared statement "P0_4" does not exist
client 602 aborted in command 4 (SQL) of script 0; ERROR: prepared statement "P0_4" does not exist
client 295 aborted in command 4 (SQL) of script 0; ERROR: prepared statement "P0_4" does not exist
client 518 aborted in command 4 (SQL) of script 0; ERROR: prepared statement "P0_4" does not exist
client 456 aborted in command 4 (SQL) of script 0; ERROR: prepared statement "P0_4" does not exist
client 761 aborted in command 4 (SQL) of script 0; ERROR: prepared statement "P0_4" does not exist
client 763 aborted in command 4 (SQL) of script 0; ERROR: prepared statement "P0_4" does not exist
client 90 aborted in command 4 (SQL) of script 0; ERROR: prepared statement "P0_4" does not exist
client 817 aborted in command 4 (SQL) of script 0; ERROR: prepared statement "P0_4" does not exist
client 998 aborted in command 4 (SQL) of script 0; ERROR: prepared statement "P0_4" does not exist
client 1993 aborted in command 4 (SQL) of script 0; ERROR: prepared statement "P0_4" does not exist
client 1624 aborted in command 4 (SQL) of script 0; ERROR: prepa ![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)