PostgreSQL连接池pgbouncer的使用
发布时间：2020-12-13 16:53:50 所属栏目：百科 来源：网络整理
导读：今天在虚拟机上整理了下pgbouncer的安装使用过程，记录如下。 说明：pgbouncer是一款轻量级针对postgresql的数据库连接工具，可以对客户端的连接做限制，防止恶意连接，另外也可以减少数据库的实际连接数，从而减少数据库的开销。 环境： VMWARE 8 CentOS 5.
|
今天在虚拟机上整理了下pgbouncer的安装使用过程，记录如下。 VMWARE 8 一.安装 1.下载pgbouncer的安装包 http://pgfoundry.org/frs/?group_id=1000258&release_id=1952,本次下载的安装包是pgbouncer-1.5.2.tar.gz 2.下载libevent包 http://monkey.org/~provos/libevent/，见pgbouncer源码包中的Readme 这玩意也是个好东西，在很多地方用到 3.安装libevent $ cd libevent-2.0.19-stable $ ./configure --prefix=/home/postgres/libevent $ make $ make install 4.安装pgbouncer $ cd pgbouncer-1.5.2 $ ./configure --prefix=/home/postgres/pgbouncer/ --with-libevent=/home/postgres/libevent/ $ make $ make install 查看pgbouncer是否安装成功，可以通过查看config.log中最后的返回值exit来确认，0是成功1是失败. [postgres@localhost config]$ pwd /home/postgres/pgbouncer/config [postgres@localhost config]$ more pgbouncer.ini [databases] f_game = host=127.0.0.1 port=1233 dbname=test_db user=test password=test [pgbouncer] listen_port = 1999 listen_addr = 127.0.0.1 auth_type = md5 auth_file = /home/postgres/pgbouncer/user.txt logfile = /home/postgres/pgbouncer/pgbouncer.log pidfile = /home/postgres/pgbouncer/pgbouncer.pid admin_users = u_kenyon pool_mode = Transaction 2.配置用户密码文件users.txt [postgres@localhost pgbouncer]$ pwd /home/postgres/pgbouncer [postgres@localhost pgbouncer]$ more user.txt "u_kenyon" "123" 三.启动 [postgres@localhost config]$ pgbouncer -d pgbouncer.ini 2012-08-21 00:29:55.573 4247 LOG File descriptor limit: 1024 (H:1024),max_client_conn: 100,max fds possible: 130 2.查看日志 [postgres@localhost pgbouncer]$ psql -h 127.0.0.1 -p 1999 -U u_kenyon f_game
Password for user u_kenyon:
psql (9.1.2)
Type "help" for help.
f_game=> d
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | t_kenyon | table | postgres
public | test | table | test
public | test2 | table | postgres
(3 rows)
f_game=> q
4.参数使用例子 [postgres@localhost config]$ psql -h 127.0.0.1 -p 1999 -U u_kenyon pgbouncer Password for user u_kenyon:
psql (9.1.2,server 1.5.2/bouncer)
WARNING: psql version 9.1,server version 1.5.
Some psql features might not work.
Type "help" for help.
pgbouncer=# show help;
NOTICE: Console usage
DETAIL:
SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|VERSION
SHOW STATS|FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM
SHOW DNS_HOSTS|DNS_ZONES
SET key = arg
RELOAD
PAUSE []
RESUME []
KILL SUSPEND
SHUTDOWN
SHOW
pgbouncer=# show config;
key | value | changeable
---------------------------+----------------------------------------+------------
job_name | pgbouncer | no
conffile | pgbouncer.ini | yes
logfile | /home/postgres/pgbouncer/pgbouncer.log | yes
pidfile | /home/postgres/pgbouncer/pgbouncer.pid | no
listen_addr | 127.0.0.1 | no
listen_port | 1999 | no
listen_backlog | 128 | no
unix_socket_dir | /tmp | no
unix_socket_mode | 511 | no
unix_socket_group | | no
auth_type | md5 | yes
auth_file | /home/postgres/pgbouncer/user.txt | yes
pool_mode | transaction | yes
max_client_conn | 100 | yes
default_pool_size | 20 | yes
min_pool_size | 0 | yes
reserve_pool_size | 0 | yes
reserve_pool_timeout | 5 | yes
syslog | 0 | yes
syslog_facility | daemon | yes
syslog_ident | pgbouncer | yes
user | | no
autodb_idle_timeout | 3600 | yes
server_reset_query | DISCARD ALL | yes
server_check_query | select 1 | yes
server_check_delay | 30 | yes
query_timeout | 0 | yes
query_wait_timeout | 0 | yes
client_idle_timeout | 0 | yes
client_login_timeout | 60 | yes
idle_transaction_timeout | 0 | yes
server_lifetime | 3600 | yes
server_idle_timeout | 600 | yes
server_connect_timeout | 15 | yes
server_login_retry | 15 | yes
server_round_robin | 0 | yes
suspend_timeout | 10 | yes
ignore_startup_parameters | | yes
disable_pqexec | 0 | no
dns_max_ttl | 15 | yes
dns_zone_check_period | 0 | yes
pkt_buf | 2048 | no
sbuf_loopcnt | 5 | yes
tcp_defer_accept | 1 | yes
tcp_socket_buffer | 0 | yes
tcp_keepalive | 1 | yes
tcp_keepcnt | 0 | yes
tcp_keepidle | 0 | yes
tcp_keepintvl | 0 | yes
verbose | 0 | yes
admin_users | u_kenyon | yes
stats_users | | yes
stats_period | 60 | yes
log_connections | 1 | yes
log_disconnections | 1 | yes
log_pooler_errors | 1 | yes
(56 rows)
本次测试环境修改的参数不多，还有其他参数如server_idle_timeout，max_connections也比较重要，后期再写。 pgbouncer=# reload; RELOAD pgbouncer=# 5.使用效果 pgbouncer=# show clients; type | user | database | state | addr | port | local_addr | local_port | connect_time | request_time | ptr | link ------+----------+-----------+--------+-----------+-------+------------+------------+---------------------+---------------------+----------+------ C | u_kenyon | f_game | active | 127.0.0.1 | 59555 | 127.0.0.1 | 1999 | 2012-08-21 00:32:19 | 2012-08-21 01:47:14 | 0xbc4670 | C | u_kenyon | f_game | active | 127.0.0.1 | 59558 | 127.0.0.1 | 1999 | 2012-08-21 00:32:35 | 2012-08-21 02:09:04 | 0xbc4940 | C | u_kenyon | f_game | active | 127.0.0.1 | 59573 | 127.0.0.1 | 1999 | 2012-08-21 02:09:08 | 2012-08-21 02:09:10 | 0xbc43a0 | C | u_kenyon | f_game | active | 127.0.0.1 | 59576 | 127.0.0.1 | 1999 | 2012-08-21 02:09:44 | 2012-08-21 02:09:46 | 0xbc4aa8 | C | u_kenyon | f_game | active | 127.0.0.1 | 59578 | 127.0.0.1 | 1999 | 2012-08-21 02:09:58 | 2012-08-21 02:10:10 | 0xbc4c10 | C | u_kenyon | pgbouncer | active | 127.0.0.1 | 59570 | 127.0.0.1 | 1999 | 2012-08-21 02:01:58 | 2012-08-21 02:10:12 | 0xbc4508 | C | u_kenyon | pgbouncer | active | 127.0.0.1 | 59574 | 127.0.0.1 | 1999 | 2012-08-21 02:09:17 | 2012-08-21 02:09:29 | 0xbc47d8 | (7 rows) pgbouncer=# show fds; fd | task | user | database | addr | port | cancel | link | client_encoding | std_strings | datestyle | timezone ----+--------+----------+----------+-----------+-------+---------------------+------+-----------------+-------------+-----------+------------ 8 | pooler | | | 127.0.0.1 | 1999 | 0 | 0 | | | | 9 | pooler | | | unix | 1999 | 0 | 0 | | | | 13 | client | u_kenyon | f_game | 127.0.0.1 | 59555 | 8879718699069493200 | 0 | UTF8 | on | ISO,MDY | US/Pacific 15 | client | u_kenyon | f_game | 127.0.0.1 | 59558 | 2718240829565746463 | 0 | UTF8 | on | ISO,MDY | US/Pacific 14 | client | u_kenyon | f_game | 127.0.0.1 | 59573 | 9376066101244793463 | 0 | UTF8 | on | ISO,MDY | US/Pacific 16 | client | u_kenyon | f_game | 127.0.0.1 | 59576 | 9911177087544032756 | 0 | UTF8 | on | ISO,MDY | US/Pacific 17 | client | u_kenyon | f_game | 127.0.0.1 | 59578 | 7987308370302986343 | 0 | UTF8 | on | ISO,MDY | US/Pacific 12 | server | test | f_game | 127.0.0.1 | 1233 | 20770794712503 | 0 | UTF8 | on | ISO,MDY | US/Pacific (8 rows) 在DB端看连接数： f_game=> select count(1) from pg_stat_activity;
count
-------
2
(1 row)
只有2个,看来池的效果已经初显成效,可预计高并发的时候池连接的可重复性使用，实际连接到DB端的进程并不是很多. Session pooling :: Most polite method. When client connects,a server connection will be assigned to it for the whole duration the client stays connected. When the client disconnects,the server connection will be put back into the pool. This is the default method. Transaction pooling :: A server connection is assigned to client only during a transaction. When PgBouncer notices that transaction is over,the server connection will be put back into the pool. Statement pooling :: Most aggressive method. The server connection will be put back into pool immediately after a query completes. Multi-statement transactions are disallowed in this mode as they would break. 四.总结 1.本次在虚拟机上测试，使用的模式是修改后的transcation模式，在服务端开启6个psql进程，但在数据库端实际只有1个连接，池的效果可见一斑，对并发高的访问，可以有效减少DB端的连接进程，减少消耗。 2.测试过程中，假如第一次访问被拒绝以后(不是密码不对)，后续访问会直接拒绝，此举可以有效降低攻击性的访问。 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |