linux:pgpouncer

PgBouncer

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

  1. 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
  2. ファイルを解凍する。
    $ tar zxvf libevent-2.0.22-stable.tar.gz
  3. libevent のビルドとインストールを行う。
    $ cd libevent-2.0.22-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/3393/pgbouncer-1.5.4.tar.gz
  2. ファイルを解凍する。
    $ tar zxvf pgbouncer-1.5.4.tar.gz
  3. PgBouncer のビルドとインストールを行う。
    $ cd pgbouncer-1.5.4
    $ ./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
    #!/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
  8. rc スクリプトのパーミッションを設定する。
    $ sudo chmod 755 /etc/rc.d/init.d/pgbouncer
  9. 自動起動の設定を行う。
    $ sudo chkconfig pgbouncer on
  10. デフォルト設定ファイルを作成して、設定ファイルとログファイルのパスを設定する。
    $ 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
  11. の設定を行う。
    $ 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
  12. サービスを起動する。
    $ sudo service pgbouncer start

バージョン確認

$ /usr/local/bin/pgbouncer -V

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

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

オンラインのまま再起動

$ sudo /usr/local/bin/pgbouncer -R -d /etc/pgbouncer.ini
  • linux/pgpouncer.txt
  • 最終更新: 2019/05/18 02:23
  • by 非ログインユーザー