PgBouncer

　PgBouncer は、PostgreSQL へのコネクションをプールして、接続時間や接続を管理するのに必要なリソースを、劇的に減少させる事ができる。

1. libevent より libevent をダウンロードする。

   $ mkdir ~/work
   $ cd ~/work
   $ curl -L -O https://github.com/downloads/libevent/libevent/libevent-2.0.20-stable.tar.gz

2. ファイルを解凍する。

   $ tar zxvf libevent-2.0.20-stable.tar.gz

3. libevent のビルドとインストールを行う。

   $ cd libevent-2.0.20-stable
   $ ./configure
   $ make
   $ sudo make install

4. libevent をシステムに登録するための定義ファイルを作成する。

   $ sudo vi /etc/ld.so.conf.d/libevent2-x86_64.conf

   /usr/local/lib/

5. libevent をシステムに登録する。

   $ sudo ldconfig

1. PgFoundry: PgBouncer: ファイルリスト より PgBouncer をダウンロードする。

   $ mkdir ~/work
   $ cd ~/work
   $ curl -O http://pgfoundry.org/frs/download.php/3369/pgbouncer-1.5.3.tar.gz

2. ファイルを解凍する。

   $ tar zxvf pgbouncer-1.5.3.tar.gz

3. PgBouncer のビルドとインストールを行う。

   $ cd pgbouncer-1.5.3
   $ ./configure --prefix=/usr/local --with-libevent=/usr/local
   $ make
   $ sudo make install

4. ディレクトリを作成する。

   $ sudo mkdir -p /var/log/pgbouncer
   $ sudo mkdir -p /var/run/pgbouncer

5. 実行ユーザーを作成する。

   $ sudo useradd pgbouncer

6. オーナーとパーミッションの設定を行う。

   $ sudo chown pgbouncer:pgbouncer /var/log/pgbouncer
   $ sudo chown pgbouncer:pgbouncer /var/run/pgbouncer

7. rc スクリプトを作成する。

   $ sudo vi /etc/rc.d/init.d/pgbouncer

   以下のソースを展開してダブルクリックし、コピーし貼り付ける。<sxh bash collapse:true>

#!/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 devrim@CommandPrompt.com # - Adjust order of startup and kill. # # v2.1 Devrim GUNDUZ devrim@gunduz.org # - Fix chkconfig line # - Start pgbouncer in daemon mode, so that it won't log twice # - to the same log file. # # v2.2 Devrim GUNDUZ devrim@gunduz.org # - 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 </sxh>

1. rc スクリプトのパーミッションを設定する。

   $ sudo chmod 755 /etc/rc.d/init.d/pgbouncer

2. 自動起動の設定を行う。

   $ sudo chkconfig pgbouncer on

3. 設定ファイルとログファイルのパスを設定する。

   $ sudo vi /etc/sysconfig/pgbouncer

   以下のソースを展開してダブルクリックし、コピーし貼り付ける。<sxh bash collapse:true>

# pgbouncer defaults #

# Path to the init file BOUNCERCONF=/etc/pgbouncer.ini

# Path to the log file BOUNCERLOG=/var/log/pgbouncer/pgbouncer.log </sxh>

1. の設定を行う。

   $ sudo vi /etc/pgbouncer.ini

   以下のソースを展開してダブルクリックし、コピーし貼り付ける。<sxh ini collapse:true>

;; database name = connect string ;; ;; connect string params: ;; dbname= host= port= user= password= ;; client_encoding= datestyle= timezone= ;; pool_size= connect_query= [databases] 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 </sxh>

バージョン確認

$ /usr/local/bin/pgbouncer -V

コマンドラインからデーモンとして起動

$ sudo /usr/local/bin/pgbouncer -d /etc/pgbouncer.ini

オンラインのまま再起動

$ sudo /usr/local/bin/pgbouncer -R -d /etc/pgbouncer.ini

Postgres Plus ユーザーサイト - FAQ
Pgbouncer installation « Stan Santiago's blog
build libevent and transmission on RHEL/CentOS 5.x | geekery
libevent
PgFoundry: PgBouncer: ファイルリスト
pgbouncerのインストール : 個人的なメモ帳