企业网站首页设计,谷歌网络营销的概念,网站建设论文标题,wordpress是哪个国家的今天在虚拟机上整理了下pgbouncer的安装使用过程#xff0c;记录如下。 说明#xff1a;pgbouncer是一款轻量级针对postgresql的数据库连接工具#xff0c;可以对客户端的连接做限制#xff0c;防止恶意连接#xff0c;另外也可以减少数据库的实际连接数#xff0c;从而减…今天在虚拟机上整理了下pgbouncer的安装使用过程记录如下。 说明pgbouncer是一款轻量级针对postgresql的数据库连接工具可以对客户端的连接做限制防止恶意连接另外也可以减少数据库的实际连接数从而减少数据库的开销。 环境 VMWARE 8CentOS 5.7 PG 9.1.2pgbouncer 1.5.2libevent-2.0.19 一.安装 1.下载pgbouncer的安装包
http://pgfoundry.org/frs/?group_id1000258release_id1952,本次下载的安装包是pgbouncer-1.5.2.tar.gz2.下载libevent包
http://monkey.org/~provos/libevent/见pgbouncer源码包中的Readme
这玩意也是个好东西在很多地方用到3.安装libevent
$ cd libevent-2.0.19-stable
$ ./configure --prefix/home/postgres/libevent
$ make
$ make install4.安装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是失败. 二.配置 1.配置pgbouncer的cfg文件 [postgreslocalhost config]$ pwd
/home/postgres/pgbouncer/config
[postgreslocalhost config]$ more pgbouncer.ini
[databases]
f_game host127.0.0.1 port1949 dbnametest_db usertest passwordtest[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 [postgreslocalhost pgbouncer]$ pwd
/home/postgres/pgbouncer
[postgreslocalhost pgbouncer]$ more user.txt
u_kenyon 123 三.启动 1.启动命令 [postgreslocalhost 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.查看日志 tail -f /home/postgres/pgbouncer/pgbouncer.log 3.登录 [postgreslocalhost 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 \dList of relationsSchema | Name | Type | Owner
-----------------------------------public | t_kenyon | table | postgrespublic | test | table | testpublic | test2 | table | postgres
(3 rows)f_game \q 4.参数使用例子 连接pgbouncer本身的DB [postgreslocalhost 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|VERSIONSHOW STATS|FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEMSHOW DNS_HOSTS|DNS_ZONESSET key argRELOADPAUSE []RESUME []KILL SUSPENDSHUTDOWN
SHOW
pgbouncer# show config;key | value | changeable
-------------------------------------------------------------------------------job_name | pgbouncer | noconffile | pgbouncer.ini | yeslogfile | /home/postgres/pgbouncer/pgbouncer.log | yespidfile | /home/postgres/pgbouncer/pgbouncer.pid | nolisten_addr | 127.0.0.1 | nolisten_port | 1999 | nolisten_backlog | 128 | nounix_socket_dir | /tmp | nounix_socket_mode | 511 | nounix_socket_group | | noauth_type | md5 | yesauth_file | /home/postgres/pgbouncer/user.txt | yespool_mode | transaction | yesmax_client_conn | 100 | yesdefault_pool_size | 20 | yesmin_pool_size | 0 | yesreserve_pool_size | 0 | yesreserve_pool_timeout | 5 | yessyslog | 0 | yessyslog_facility | daemon | yessyslog_ident | pgbouncer | yesuser | | noautodb_idle_timeout | 3600 | yesserver_reset_query | DISCARD ALL | yesserver_check_query | select 1 | yesserver_check_delay | 30 | yesquery_timeout | 0 | yesquery_wait_timeout | 0 | yesclient_idle_timeout | 0 | yesclient_login_timeout | 60 | yesidle_transaction_timeout | 0 | yesserver_lifetime | 3600 | yesserver_idle_timeout | 600 | yesserver_connect_timeout | 15 | yesserver_login_retry | 15 | yesserver_round_robin | 0 | yessuspend_timeout | 10 | yesignore_startup_parameters | | yesdisable_pqexec | 0 | nodns_max_ttl | 15 | yesdns_zone_check_period | 0 | yespkt_buf | 2048 | nosbuf_loopcnt | 5 | yestcp_defer_accept | 1 | yestcp_socket_buffer | 0 | yestcp_keepalive | 1 | yestcp_keepcnt | 0 | yestcp_keepidle | 0 | yestcp_keepintvl | 0 | yesverbose | 0 | yesadmin_users | u_kenyon | yesstats_users | | yesstats_period | 60 | yeslog_connections | 1 | yeslog_disconnections | 1 | yeslog_pooler_errors | 1 | yes
(56 rows) 本次测试环境修改的参数不多还有其他参数如server_idle_timeoutmax_connections也比较重要后期再写。 使用reload可以不用重启pgbouncer而使修改的参数生效 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/Pacific15 | client | u_kenyon | f_game | 127.0.0.1 | 59558 | 2718240829565746463 | 0 | UTF8 | on | ISO, MDY | US/Pacific14 | client | u_kenyon | f_game | 127.0.0.1 | 59573 | 9376066101244793463 | 0 | UTF8 | on | ISO, MDY | US/Pacific16 | client | u_kenyon | f_game | 127.0.0.1 | 59576 | 9911177087544032756 | 0 | UTF8 | on | ISO, MDY | US/Pacific17 | client | u_kenyon | f_game | 127.0.0.1 | 59578 | 7987308370302986343 | 0 | UTF8 | on | ISO, MDY | US/Pacific12 | server | test | f_game | 127.0.0.1 | 1949 | 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端的进程并不是很多.6.pool_mode说明 有三种模式:session,transaction,statement session是默认的模式,每开启一个进程DB端也会开启一个新的进程 transaction是基于事务模式的 statement是基于每个查询的开启此模式不适合执行事务会报错 原文见安装后pgbouncer中的usage.txt,本次文件地址 /home/postgres/pgbouncer/share/doc/pgbouncer 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.测试过程中假如第一次访问被拒绝以后(不是密码不对)后续访问会直接拒绝此举可以有效降低攻击性的访问。 转http://my.oschina.net/Kenyon/blog/73935转载于:https://www.cnblogs.com/shuaixf/archive/2012/10/02/2710432.html
