====== PgBouncer ======
PgBouncer は、PostgreSQL へのコネクションをプールして、接続時間や接続を管理するのに必要なリソースを、劇的に減少させる事ができる。
===== libevent のインストール =====
- [[http://libevent.org/|libevent]] より libevent をダウンロードする。
$ mkdir ~/work
$ cd ~/work
$ curl -L -O https://sourceforge.net/projects/levent/files/libevent/libevent-2.0/libevent-2.0.22-stable.tar.gz
- ファイルを解凍する。
$ tar zxvf libevent-2.0.22-stable.tar.gz
- libevent のビルドとインストールを行う。
$ cd libevent-2.0.22-stable
$ ./configure
$ make
$ sudo make install
- libevent をシステムに登録するための定義ファイルを作成する。
$ sudo vi /etc/ld.so.conf.d/libevent2-x86_64.conf
/usr/local/lib/
- libevent をシステムに登録する。
$ sudo ldconfig
===== PgBouncer のインストール =====
- [[http://pgfoundry.org/frs/?group_id=1000258|PgFoundry: PgBouncer: ファイルリスト]] より PgBouncer をダウンロードする。
$ mkdir ~/work
$ cd ~/work
$ curl -O http://pgfoundry.org/frs/download.php/3393/pgbouncer-1.5.4.tar.gz
- ファイルを解凍する。
$ tar zxvf pgbouncer-1.5.4.tar.gz
- PgBouncer のビルドとインストールを行う。
$ cd pgbouncer-1.5.4
$ ./configure --prefix=/usr/local --with-libevent=/usr/local
$ make
$ sudo make install
- ディレクトリを作成する。
$ sudo mkdir -p /var/log/pgbouncer
$ sudo mkdir -p /var/run/pgbouncer
- 実行ユーザーを作成する。
$ sudo useradd pgbouncer
- オーナーを設定する。
$ sudo chown pgbouncer:pgbouncer /var/log/pgbouncer
$ sudo chown pgbouncer:pgbouncer /var/run/pgbouncer
- rc スクリプトを作成する。
$ sudo vi /etc/rc.d/init.d/pgbouncer
#!/bin/sh
# pgbouncer This is the init script for starting up the pgbouncer
#
# chkconfig: - 64 36
# description: Starts and stops the pgbouncer daemon.
#
# processname: pgbouncer
# pidfile: /var/run/pgbouncer/pgbouncer.pid
#
# v2.0 Devrim GUNDUZ
# - Adjust order of startup and kill.
#
# v2.1 Devrim GUNDUZ
# - Fix chkconfig line
# - Start pgbouncer in daemon mode, so that it won't log twice
# - to the same log file.
#
# v2.2 Devrim GUNDUZ
# - Sleep 2 seconds before getting pid during start(), like
# we do in PostgreSQL init script, to avoid false positive
# startup errors.
# Source function library.
INITD=/etc/rc.d/init.d
. $INITD/functions
# Get function listing for cross-distribution logic.
TYPESET=`typeset -f|grep "declare"`
# Get config.
. /etc/sysconfig/network
# Find the name of the script
NAME=`basename $0`
if [ ${NAME:0:1} = "S" -o ${NAME:0:1} = "K" ]
then
NAME=${NAME:3}
fi
# For SELinux we need to use 'runuser' not 'su'
if [ -x /sbin/runuser ]
then
SU=runuser
else
SU=su
fi
# Override defaults from /etc/sysconfig/pgbouncer if file is present
[ -f /etc/sysconfig/${NAME} ] && . /etc/sysconfig/${NAME}
# Check that networking is up.
[ "${NETWORKING}" = "no" ] && exit 0
script_result=0
start(){
BOUNCER_START=$"Starting ${NAME} service: "
# Make sure startup-time log file is valid
if [ ! -e "$BOUNCERLOG" -a ! -h "$BOUNCERLOG" ]
then
touch "$BOUNCERLOG" || exit 1
chown pgbouncer:pgbouncer "$BOUNCERLOG"
chmod go-rwx "$BOUNCERLOG"
[ -x /usr/bin/chcon ] && /usr/bin/chcon -u system_u -r object_r -t postgresql_log_t "$BOUNCERLOG" 2>/dev/null
fi
# Make sure that pgbouncer is not already running:
if [ -f /var/run/${NAME}/${NAME}.pid ]
then
echo "${NAME} is already running"
echo_failure
echo
exit 1
fi
echo -n "$BOUNCER_START"
[ -d /var/run/${NAME} ] || mkdir /var/run/${NAME}
chown pgbouncer:pgbouncer /var/run/${NAME}
$SU -l pgbouncer -c "pgbouncer -d '$BOUNCERCONF' & " >> "$BOUNCERLOG" 2>&1 < /dev/null
sleep 2
pid=`pidof -s pgbouncer`
if [ $pid ]
then
success "$BOUNCER_START"
touch /var/lock/subsys/${NAME}
echo
else
failure "$BOUNCER_START"
echo
script_result=1
fi
}
stop(){
echo -n $"Stopping ${NAME} service: "
if [ -f /var/run/${NAME}/${NAME}.pid ]
then
kill -TERM `cat "/var/run/${NAME}/${NAME}.pid"`
ret=$?
if [ $ret -eq 0 ]
then
echo_success
else
echo_failure
script_result=1
fi
echo
rm -f "/var/run/${NAME}/${NAME}.pid"
rm -f "/var/lock/subsys/${NAME}"
else
echo " ${NAME} is not running."
echo_failure
echo
exit 1
fi
}
restart(){
stop
start
}
condrestart(){
[ -e /var/lock/subsys/${NAME} ] && restart
}
condstop(){
[ -e /var/lock/subsys/${NAME} ] && stop
}
reload(){
$SU -l pgbouncer -c "kill -HUP `cat /var/run/${NAME}/${NAME}.pid`" > /dev/null 2>&1 < /dev/null
}
pause(){
$SU -l pgbouncer -c "kill -USR1 `cat /var/run/${NAME}/${NAME}.pid`" > /dev/null 2>&1 < /dev/null
}
resum(){
$SU -l pgbouncer -c "kill -USR2 `cat /var/run/${NAME}/${NAME}.pid`" > /dev/null 2>&1 < /dev/null
}
# See how we were called.
case "$1" in
start)
start
;;
stop)
stop
;;
status)
status pgbouncer
script_result=$?
;;
restart)
restart
;;
pause)
pause
;;
continue)
resum
;;
condrestart)
condrestart
;;
condstop)
condstop
;;
reload|force-reload)
reload
;;
*)
echo $"Usage: $0 {start|stop|status|restart|pause|continue|reload|force-reload|condrestart|condstop}"
exit 1
esac
exit $script_result
- rc スクリプトのパーミッションを設定する。
$ sudo chmod 755 /etc/rc.d/init.d/pgbouncer
- 自動起動の設定を行う。
$ sudo chkconfig pgbouncer on
- デフォルト設定ファイルを作成して、設定ファイルとログファイルのパスを設定する。
$ sudo vi /etc/sysconfig/pgbouncer
#
# pgbouncer defaults
#
# Path to the init file
BOUNCERCONF=/etc/pgbouncer.ini
# Path to the log file
BOUNCERLOG=/var/log/pgbouncer/pgbouncer.log
- の設定を行う。
$ sudo vi /etc/pgbouncer.ini
;; database name = connect string
;;
;; connect string params:
;; dbname= host= port= user= password=
;; client_encoding= datestyle= timezone=
;; pool_size= connect_query=
[databases]
;template1 = host=pgsql.mydomain.net dbname=template1 user=pgadmin password=pgadmin pool_size=1
database_name1 = host=localhost dbname=database_name1 user=database_user1 password=password1
database_name2 = host=localhost dbname=database_name2 user=database_user2 password=password2
database_name3 = host=localhost dbname=database_name3 user=database_user3 password=password3
; foodb over unix socket
;foodb =
; redirect bardb to bazdb on localhost
;bardb = host=localhost dbname=bazdb
; acceess to dest database will go with single user
;forcedb = host=127.0.0.1 port=300 user=baz password=foo client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
; use custom pool sizes
;nondefaultdb = pool_size=50 reserve_pool=10
; fallback connect string
;* = host=testserver
;; Configuration section
[pgbouncer]
;;;
;;; Administrative settings
;;;
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
;;;
;;; Where to wait for clients
;;;
; ip address or * which means all ip-s
listen_addr = 127.0.0.1
listen_port = 6432
user = pgbouncer
; unix socket is also used for -R.
; On debian it should be /var/run/postgresql
;unix_socket_dir = /tmp
;unix_socket_mode = 0777
;unix_socket_group =
;;;
;;; Authentication settings
;;;
; any, trust, plain, crypt, md5
;auth_type = trust
auth_type = any
;auth_file = /8.0/main/global/pg_auth
;auth_file = /etc/pgbouncer/userlist.txt
;;;
;;; Users allowed into database 'pgbouncer'
;;;
; comma-separated list of users, who are allowed to change settings
admin_users = postgres
; comma-separated list of users who are just allowed to use SHOW command
stats_users = stats, postgres
;;;
;;; Pooler personality questions
;;;
; When server connection is released back to pool:
; session - after client disconnects
; transaction - after transaction finishes
; statement - after statement finishes
;pool_mode = session
pool_mode = transaction
;
; Query for cleaning connection immediately after releasing from client.
; No need to put ROLLBACK here, pgbouncer does not reuse connections
; where transaction is left open.
;
; Query for 8.3+:
; DISCARD ALL;
;
; Older versions:
; RESET ALL; SET SESSION AUTHORIZATION DEFAULT
;
; Empty if transaction pooling is in use.
;
server_reset_query = DISCARD ALL
;
; Comma-separated list of parameters to ignore when given
; in startup packet. Newer JDBC versions require the
; extra_float_digits here.
;
;ignore_startup_parameters = extra_float_digits
;
; When taking idle server into use, this query is ran first.
; SELECT 1
;
;server_check_query = select 1
; If server was used more recently that this many seconds ago,
; skip the check query. Value 0 may or may not run in immediately.
;server_check_delay = 30
;;;
;;; Connection limits
;;;
; total number of clients that can connect
;max_client_conn = 100
max_client_conn = 1000
; default pool size. 20 is good number when transaction pooling
; is in use, in session pooling it needs to be the number of
; max clients you want to handle at any moment
;default_pool_size = 20
default_pool_size = 100
; how many additional connection to allow in case of trouble
;reserve_pool_size = 5
; if a clients needs to wait more than this many seconds, use reserve pool
;reserve_pool_timeout = 3
; log if client connects or server connection is made
;log_connections = 1
; log if and why connection was closed
;log_disconnections = 1
; log error messages pooler sends to clients
;log_pooler_errors = 1
; If off, then server connections are reused in LIFO manner
;server_round_robin = 0
;;;
;;; Timeouts
;;;
;; Close server connection if its been connected longer.
;server_lifetime = 1200
;; Close server connection if its not been used in this time.
;; Allows to clean unnecessary connections from pool after peak.
;server_idle_timeout = 60
;; Cancel connection attempt if server does not answer takes longer.
;server_connect_timeout = 15
;; If server login failed (server_connect_timeout or auth failure)
;; then wait this many second.
;server_login_retry = 15
;; Dangerous. Server connection is closed if query does not return
;; in this time. Should be used to survive network problems,
;; _not_ as statement_timeout. (default: 0)
;query_timeout = 0
;; Dangerous. Client connection is closed if the query is not assigned
;; to a server in this time. Should be used to limit the number of queued
;; queries in case of a database or network failure. (default: 0)
;query_wait_timeout = 0
;; Dangerous. Client connection is closed if no activity in this time.
;; Should be used to survive network problems. (default: 0)
;client_idle_timeout = 0
;; Disconnect clients who have not managed to log in after connecting
;; in this many seconds.
;client_login_timeout = 60
;; Clean automatically created database entries (via "*") if they
;; stay unused in this many seconds.
; autodb_idle_timeout = 3600
;;;
;;; Low-level tuning options
;;;
;; buffer for streaming packets
;pkt_buf = 2048
;; man 2 listen
;listen_backlog = 128
;; networking options, for info: man 7 tcp
;; Linux: notify program about new connection only if there
;; is also data received. (Seconds to wait.)
;; On Linux the default is 45, on other OS'es 0.
;tcp_defer_accept = 0
;; In-kernel buffer size (Linux default: 4096)
;tcp_socket_buffer = 0
;; whether tcp keepalive should be turned on (0/1)
;tcp_keepalive = 1
;; following options are Linux-specific.
;; they also require tcp_keepalive=1
;; count of keepaliva packets
;tcp_keepcnt = 0
;; how long the connection can be idle,
;; before sending keepalive packets
;tcp_keepidle = 0
;; The time between individual keepalive probes.
;tcp_keepintvl = 0
;; DNS lookup caching time
;dns_max_ttl = 15
;; DNS zone SOA lookup period
;dns_zone_check_period = 0
- サービスを起動する。
$ sudo service pgbouncer start
===== PgBouncer の使い方 =====
バージョン確認
$ /usr/local/bin/pgbouncer -V
コマンドラインからデーモンとして起動
$ sudo /usr/local/bin/pgbouncer -d /etc/pgbouncer.ini
オンラインのまま再起動
$ sudo /usr/local/bin/pgbouncer -R -d /etc/pgbouncer.ini
===== 参考文献 =====
[[http://postgres.sios.com/modules/newbb/viewtopic.php?topic_id=149&forum=1|Postgres Plus ユーザーサイト - FAQ]]\\
[[http://stansantiago.wordpress.com/2011/10/22/pgbounce-installation/|Pgbouncer installation « Stan Santiago's blog]]\\
[[http://geekery.blog.com/2011/05/08/build-libevent-and-transmission-on-rhelcentos-5-x/|build libevent and transmission on RHEL/CentOS 5.x | geekery]]\\
[[http://libevent.org/|libevent]]\\
[[http://pgfoundry.org/frs/?group_id=1000258|PgFoundry: PgBouncer: ファイルリスト]]\\
[[http://blog.livedoor.jp/horahuto/archives/4487570.html|pgbouncerのインストール : 個人的なメモ帳]]\\