pgbouncer连接池
目录
背景
在做一站式项目的过程中发现当大量的服务连接到同一个库，以及大量的开发人员连接到同一个库的时候，数据库的2000连接不够。当然和使用c3p0连接池有关系，以前默认都是将最大最小连接数设置为一样，但现在都是用druid连接池，所以最大和最小连接数不必设置为一样。这样可以减少连接数，当然这只是一方面，今天我们要说的是当有大量连接的时候pgbouncer连接池的使用
如果没有数据库连接池
数据库在没有任何连接池的情况下，应用程序必须直接访问数据库来建立连接。打开的一个连接的消耗，关闭一个连接的消耗，而这样的消耗，伴随着你打开的连接越多，则消耗的就越多。还有短连接，可能同时并发的连接多，但占用这个连接的时间很短。这就会引起另一个问题，你设置的连接数和突入起来的连接数不匹配的情况，最后就会造成拒绝连接的问题。
所以这就对数据库的连接提出一个问题，复用，连接的复用对数据库非常重要，这可以降低某些快速连接，快速断开的连接的数据库访问对数据库性能的消耗和产生的一些不必要的麻烦。
版本
PgBouncer 1.11.x已经可以支持PostgreSQL 12
PgBouncer 1.14.0也已经发布与PostgreSQL 13新功能匹配
pgbouncer的作用
既然应用已经使用了durid连接池了，那么pgbouncer还有什么用呢？
虽然我们每个服务都使用了连接池，但是各个服务之间是相互独立的，当有许多的服务去连一个数据库的时候，对数据库来说这么多连接还是不受保护的。（如果数据库仅有少数的应用连接，那么可以只使用程序自带的连接池就够了,但如果有大量的服务在连接数据库，数据库其实还是没有收到连接池的保护）
另外，PG是多进程结构，每个进程大概耗费10MB左右内存，所以连接越多，耗费的内存也就越多。
使用软件架构池对数据库进行连接
使用durid连接池如下面所示，当有大量的应用连接数据库的时候其实对数据库来说这些连接还是没有受到控制
使用pgbouncer连接池的方式对数据库进行连接
pgbouncer是在数据库端加了一个池子，也就是所有的连接经过数据库都需要进入这个池子
采用pgbouncer来管理连接池的话，相当于在众多的软件druid连接池下面加了一个大的池子，来管理连接。这样来保证数据库的稳性。
当然，pg的连接池不仅仅pgbouncer、还有pgpool，pgbouncer功能更加精简，配置简单。
安装配置：
安装参考安装文档
数据库里面对应的数据库信息需要在pgbouncer.ini配置里面配置，listen_port是连接池对外的端口，应用配置时需要配置listen_port，如果直接配置数据库的端口，那么就没有通过连接池
vi pgbouncer.ini
[databases]
pgbenchdb = host=localhost port=6543 user=sa dbname=pgbenchdb password=xxxx connect_query='SELECT 1' pool_size=100
[pgbouncer]
logfile = /etc/pgbouncer/pgbouncer.log
pidfile = /etc/pgbouncer/pgbouncer.pid
auth_type = hba
auth_file = /etc/pgbouncer/userlist.txt
auth_hba_file =/etc/pgbouncer/pg_hba.conf
admin_users = sa
stats_users = sa
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 228
;min_pool_size = 0
reserve_pool_size = 10
listen_addr = 0.0.0.0
listen_port = 5432
--登录到pgbouncer，可以看到对应的版本为1.12.0
[pg@sqlfx ~]$ psql -Usa -d pgbouncer -p 5432
psql (11.1, server 1.12.0/bouncer)
Type "help" for help.
--show help
pgbouncer=# show help;
NOTICE: Console usage
DETAIL:
SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|USERS|VERSION
SHOW FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM
SHOW DNS_HOSTS|DNS_ZONES
SHOW STATS|STATS_TOTALS|STATS_AVERAGES|TOTALS
SET key = arg
RELOAD
PAUSE [<db>]
RESUME [<db>]
DISABLE <db>
ENABLE <db>
RECONNECT [<db>]
KILL <db>
SUSPEND
SHUTDOWN
SHOW
pgbouncer=# show databas;
ERROR: invalid command 'show databas;', use SHOW HELP;
pgbouncer=# show databases;
name | host | port | database | force_user | pool_size | reserve_pool | pool_mode | max_connections | current_connections | paused | disabled
-----------+---------------+------+-----------+------------+-----------+--------------+-----------+-----------------+---------------------+--------+----------
pgbenchdb | xxxxxx | 6543 | pgbenchdb | sa | 100 | 10 | | 0 | 0 | 0 | 0
pgbouncer | | 5432 | pgbouncer | pgbouncer | 2 | 0 | statement | 0 | 0 | 0 | 0
(2 rows)
性能测试
短链接
使用连接池：5985tps
[pg@sqlfx ~]$ pgbench -M extended -v -r -P 1 -S -C -c 100 -j 100 -T 60 -p 5432 -Usa pgbenchdb
starting vacuum...end.
starting vacuum pgbench_accounts...end.
progress: 1.0 s, 6175.1 tps, lat 6.959 ms stddev 2.293
、、、
progress: 60.0 s, 6607.6 tps, lat 6.676 ms stddev 1.708
transaction type: <builtin: select only>
scaling factor: 100
query mode: extended
number of clients: 100
number of threads: 100
duration: 60 s
number of transactions actually processed: 359203
latency average = 7.426 ms
latency stddev = 5.283 ms
tps = 5985.437222 (including connections establishing)
tps = 13415.041978 (excluding connections establishing)
statement latencies in milliseconds:
0.003 \set aid random(1, 100000 * :scale)
7.425 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
不使用连接池：752tps
[pg@sqlfx ~]$ pgbench -M extended -v -r -P 1 -S -C -c 100 -j 100 -T 60 -p 6543 -Usa pgbenchdb
starting vacuum...end.
starting vacuum pgbench_accounts...end.
progress: 1.1 s, 656.3 tps, lat 8.981 ms stddev 7.668
、、、
progress: 60.0 s, 713.3 tps, lat 8.220 ms stddev 5.773
transaction type: <builtin: select only>
scaling factor: 100
query mode: extended
number of clients: 100
number of threads: 100
duration: 60 s
number of transactions actually processed: 45235
latency average = 7.972 ms
latency stddev = 6.182 ms
tps = 752.359996 (including connections establishing)
tps = 12079.524918 (excluding connections establishing)
statement latencies in milliseconds:
0.005 \set aid random(1, 100000 * :scale)
7.966 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
短链接情况下使用连接池和不使用连接池，差别非常大。原因是建立大量的连接需要耗费大量的时间
高并发
使用连接池：565tps
--使用连接池，数据库连接数为100
--这个地方的100是和pgbouncer.ini里面的pool_size一致
pgbenchdb=# select count(*) from pg_stat_activity where state !='idle' and application_name = 'pgbench';
count
-------
100
(1 row)
--初始化数据
pgbench -i -s 20 pgbenchdb
dropping old tables...
creating tables...
generating data...
、、、
2000000 of 2000000 tuples (100%) done (elapsed 13.11 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done.
--测试
[pg@sqlfx ~]$ pgbench -M extended -r -P 1 -c 1000 -j 1000 -T 60 -p 5432 -Usa pgbenchdb
starting vacuum...end.
、、、
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 20
query mode: extended
number of clients: 1000
number of threads: 1000
duration: 60 s
number of transactions actually processed: 34907
latency average = 1734.514 ms
latency stddev = 406.066 ms
tps = 565.477219 (including connections establishing)
tps = 565.611196 (excluding connections establishing)
statement latencies in milliseconds:
0.006 \set aid random(1, 100000 * :scale)
0.001 \set bid random(1, 1 * :scale)
0.001 \set tid random(1, 10 * :scale)
0.001 \set delta random(-5000, 5000)
1471.673 BEGIN;
2.593 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
1.302 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
105.564 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
122.611 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
2.311 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
29.079 END;
不使用连接池：406tps
--不使用用连接池，数据库连接数为1000
pgbenchdb=# select count(*) from pg_stat_activity where state !='idle' and application_name = 'pgbench';
count
-------
1000
(1 row)
--初始化数据
[pg@sqlfx ~]$ pgbench -i -s 20 pgbenchdb
dropping old tables...
creating tables...
generating data...
、、、
2000000 of 2000000 tuples (100%) done (elapsed 13.46 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done.
--测试
[pg@sqlfx ~]$ pgbench -M extended -r -P 1 -c 1000 -j 1000 -T 60 -p 6543 -Usa pgbenchdb
starting vacuum...end.
progress: 2.5 s, 443.8 tps, lat 362.341 ms stddev 407.794
、、、
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 20
query mode: extended
number of clients: 1000
number of threads: 1000
duration: 60 s
number of transactions actually processed: 24946
latency average = 2375.957 ms
latency stddev = 3390.823 ms
tps = 406.784046 (including connections establishing)
tps = 409.912851 (excluding connections establishing)
statement latencies in milliseconds:
0.016 \set aid random(1, 100000 * :scale)
0.001 \set bid random(1, 1 * :scale)
0.001 \set tid random(1, 10 * :scale)
0.003 \set delta random(-5000, 5000)
4.545 BEGIN;
4.436 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
2.508 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
1950.669 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
369.524 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
3.705 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
40.694 END;
可以看到使用pgbouncer连接池的tps要比不使用pgbouncer要稍高。使用了pgbouncer后，连接到数据库的连接数为100，而不使用pgbouncer的连接数为1000
总结
1、在使用durid连接池的时候，最大最小连接池不必设置成一样，这样可以减小连接池开销
2、durid连接池是应用端的池子，当连接数据库的应用变多了以后，数据库任然不受保护，并且连接越多耗费的内存越多
3、pgbouncer相当于在数据库端加了一个池子，所有的连接过来都需要经过这个池子，负责连接的打开和关闭
4、当有大量的服务连接到数据库时，虽然有durid应用端连接池，但是对于数据库来说，仍然像没有使用连接池一样，pgbouncer是数据库端的连接池，用来控制应用端的连接
参考资料:
