postgresql数据库连接池pgbouncer的使用方法

前端时间看了看服务器还有多余的内存，由于数据库和程序都在一个服务器上，就想看看有什么提升并发的方法。试用了下pgbouncer，记录下。

$ cd libevent-2.0.21-stable

$ ./configure --prefix=/usr/local/libevent

$ make

$ make install

$ cd pgbouncer-1.5.4

$ ./configure --prefix=/usr/local/pgbouncer/ --with-libevent=/usr/local/libevent/

$ make

$ make install

注意设置libevent 的环境变量不然后面启动 pgbouncer会出错

vim /ect/profile

export LD_LIBRARY_PATH=/usr/local/libevent/lib:$LD_LIBRARY_PATH

2.pgbouncer配置

主要两个文件pgbouncer.ini 和userlist.txt文件，可以参考/usr/local/pgbouncer/share/doc/下对应的两个示例文件。

这里看下pgbouncer.ini的配置，其主要说明可以从上面说的路径中查看对应的说明

[databases]

server_main = host=localhost port=5432 dbname=booksair user=postgres password=12

3456 connect_query='SELECT 1'

[pgbouncer]

listen_port = 5433

listen_addr = localhost

auth_type = md5

auth_file = /usr/local/pgbouncer/user.txt

logfile = /usr/local/pgbouncer/pgbouncer.log

pidfile = /usr/local/pgbouncer/pgbouncer.pid

admin_users = postgres

pool_mode = Transaction

ignore_startup_parameters = extra_float_digits

max_client_cOnn= 1000

3.pgbouncer启动与说明

启动pgbouncer ，这里必须以postgresql服务器用户启动，例如postgres

su postgres

/usr/local/pgbouncer/bin/pgbouncer -d /usr/local/pgbouncer/conf/pgbouncer.ini

然后通过 postgresql 的psql登录pgbouncer工具的数据库pgbouncer，以便查看pgbouncer工具的状态。

这里注意连接pgbouncer 要采用pgbouncer.ini配置的pgbouncer节的listen端口：

cd /usr/local/postgresql/bin

su postgres

./psql -h localhost -p 5433 -U postgres pgbouncer

采用show config; 查看pgbouncer的配置，包括pgbouncer.ini配置文件中配置：

pgbouncer=# show config;

key | value | changeable

---------------------------+------------------------------------+------------

job_name | pgbouncer | no

conffile | ../conf/pgbouncer.ini | yes

logfile | /usr/local/pgbouncer/pgbouncer.log | yes

pidfile | /usr/local/pgbouncer/pgbouncer.pid | no

listen_addr | 127.0.0.1 | no

listen_port | 5433 | 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 | /usr/local/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

max_packet_size | 2147483647 | 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 | postgres | yes

stats_users | | yes

stats_period | 60 | yes

log_connections | 1 | yes

log_disconnections | 1 | yes

log_pooler_errors | 1 | yes

(57 rows)

采用 show clients;查看连接状态:

pgbouncer=# show clients;

type | user | database | state | addr | port | local_addr | local_port | connect_time | request_time | ptr | link

------+----------+-----------+--------+-----------+-------+------------+------------+---------------------+---------------------+-----------+------

C | postgres | pgbouncer | active | 127.0.0.1 | 42782 | 127.0.0.1 | 5433 | 2013-06-13 00:05:19 | 2013-06-13 00:08:52 | 0x935c310 |

(1 row)

这里应用程序连接数据库的还没有启用，所以只有我通过shell命令psql 的连接。