Tag Archives: pgbouncer

Pgbouncer installation

22 Oct

Install PgBouncer

  1. Download and install latest version ( 2.0 or higher) from http://monkey.org/~provos/libevent/
  2. Extract the compressed file and run:
    1. ./configure
    2. make
    3. make install
  1. Download the latest version of Pgbouncer from http://pgfoundry.org/frs/?group_id=1000258
  1. # Extract the compressed file and run:
  2. ./configure –prefix=/usr/local –with-libevent=/usr/local
  3. make
  4. make install

Configure Libraries

  1. vi /etc/ld.so.conf.d/libevent-i386.conf
    • Note : If it is not created then create a new file libevent-i386.conf in the same folder.
  2. Enter /usr/local/lib/ the save and quit
    • The path in the libevent-i386.conf is the path where the actual .so files are located at. The path is set when we run the ./configure –prefix=/usr/local/
      during the libevent compilation.
  3. Reload the ld configuration with:
    1. ldconfig

Setup Logging and Other Directories

  1. mkdir -p /var/log/pgbouncer
  2. mkdir -p /var/run/pgbouncer
  3. chown apache:apache /var/log/pgbouncer
  4. chown apache:apache /var/run/pgbouncer
  • The chown command sets the directories ownership to the user ‘apache’ and group ‘apache’.

Setup PgBouncer configuration File:

  1. vi /etc/pgbouncer.ini and make changes to match the configuration file below. You can also copy/paste the text below to the /etc/pgbouncer.ini file.
  2. The most important entries are:
    • The databases section
    • The logfile/pidfile entries in the pgbouncer section
    • The lister_addr entry in the pgbouncer section
    • The user entry in the pgbouncer section
    • The pool_mode entry in the pgbouncer section
    • THE MAX_CLIENT entry should be set high enough. In this case it’s set to 10000
  1. See http://pgbouncer.projects.postgresql.org/doc/config.html#_pool_configuration# for complete descriptions.

/etc/pgbouncer.ini

;; database name = connect string
[databases]

template1 = host=pgsql.mydomain.net dbname=template1 user=pgadmin password=pgadmin pool_size=1
shard_1  =  host=pgsql.mydomain.net dbname=shard_1 user=pgadmin password=pgadmin 

; fallback connect string
;* = host=testserver

;; Configuation 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_addr = *
listen_port = 6432
unix_socket_dir = /tmp

user = apache

;;;
;;; Authentication settings
;;;

; any, trust, plain, crypt, md5
auth_type = any
#auth_file = /var/lib/pgsql/data/global/pg_auth
#auth_file = /etc/pgbuserlist.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, root

;;;
;;; 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 immidiately after releasing from client.
;
; Query for 8.3+:
;   DISCARD ALL;
;
; Older versions:
;   RESET ALL; SET SESSION AUTHORIZATION DEFAULT
;
server_reset_query =

;
; 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 immidiately.
server_check_delay = 10

;;;
;;; Connection limits
;;;
; total number of clients that can connect
max_client_conn = 10000
default_pool_size = 10

; 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_connections = 0
log_disconnections = 0

; 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 unneccessary connections from pool after peak.
;server_idle_timeout = 60

;; Cancel connection attepmt 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 = 0

;; 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

Pgbouncer Authentication Settings

  • Pgbouncer requires it’s own userlist file. The client authenticates with the Pgbouncer service first, this is independent
    of the Postgres Database authentication.
  • The userid/password for the Postgres database should be specified in the Databases section of the PgBouncer config file.
  • For our application, there will be a Pgbouncer service available locally for each Apache instance. As the service is available locally we can disable Pgbouncer
    authentication completely by selecting the “any” authentication option.

Start/Stop PgBouncer

  1. Run ulimit -n 20000 before running the pgbouncer start or restart command.
  1. /usr/local/bin/pgbouncer -d /etc/pgbouncer.ini
  2. /usr/local/bin/pgbouncer -R -d /etc/pgbouncer.ini (to restart)

Test PgBouncer Connectivity

  1. psql -U postgres -p 6432 shard_1

{info:title=Make Note}

  • 6432 in the above command is the port that PgBouncer is listening on. Pgbouncer will forward the request to the database mapped to shard_1 in
    the /etc/pgbouncer.ini file.

{info}

Connecting to PgBouncer via pg_connect()

  1. The following PHP pg_connect() code can now be used to connect to PgBouncer:
$connection = pg_connect('host=localhost port=6432 dbname=shard_1');

Using the PgBouncer console:

  1. psql -p 6432 pgbouncer (on the instance running pbbouncer)
  2. See http://pgbouncer.projects.postgresql.org/doc/usage.html#_quick_start for list of admin commands.

References

  1. http://wiki.postgresql.org/wiki/PgBouncer
  2. http://filip.rembialkowski.net/pgbouncer-mini-howto-benchmark/
  3. http://blog.enricostahn.com/2009/06/25/phps-pg_connect-via-pgpool-ii-vs-pgbouncer-vs-native.html
Advertisements