标签
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=)