This post helps you install, setup and benchmark pgbouncer connection pooling for PostgreSQL 9.2 on CentOS 6/RedHat/Fedora based systems. pgbouncer is one of the PostgreSQL connection poolers. There are other connection poolers available for PostgreSQL. Any enduser applications can be connected to pgbouncer as if it were a PostgreSQL server, and pgbouncer will create a connection to the actual server, or it will reuse one of its existing connections. The purpose of connection poolers in general is to lower the performance impact of opening new connections to PostgreSQL(or Other) databases. pgbouncer can connection pool in three ways, which are
- Session pooling
- Transaction pooling
- Statement pooling
Second and Third options are more aggressive and not very easy to maintain/manage. In this post I will setup session pooling. I am assuming that you have a postgresql database server up and running before you start deploying pgbouncer. If haven’t done installing postgresql before, please go through my other post on how to install Postgres database server. To install pgbouncer, simply yum install it as shown below.
yum install pgbouncer
Once pgbouncer is installed we need to tweak its config and create some users. Let me walk through these with you. First lets edit the config file and tweak it.
You will need to update/add the following lines to your config:
Add all the databases that you need to pass through from pgbouncer, I just added postgres/pgbouncer_bench db redirects here (I will be using pgbouner_bench database down for benchmarking). Also you can change the auth_type to your needs (I am using plain for illustration purpose).
postgres = host=localhost dbname=postgres pgbouncer_bench = host=localhost dbname=pgbouncer_bench listen_addr = * auth_file = /etc/pgbouncer/userlist.txt auth_type = plain
Your full config file should look as following:
;; database name = connect string ;; ;; connect string params: ;; dbname= host= port= user= password= ;; client_encoding= datestyle= timezone= ;; pool_size= connect_query= [databases] ; foodb over unix socket ;foodb = ; redirect bardb to bazdb on localhost ;bardb = host=localhost dbname=bazdb postgres = host=localhost dbname=postgres pgbouncer_bench = host=localhost dbname=pgbouncer_bench ; 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.log pidfile = /var/run/pgbouncer/pgbouncer.pid ;;; ;;; Where to wait for clients ;;; ; ip address or * which means all ip-s listen_addr = * listen_port = 6432 ; 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 = plain ;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 ; ; 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 ; 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 = 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
Now lets create users.txt file for accessing database through pgbouncer. These users are users that already exist in our existing database. Add all users you wish to give access to your databases through pgbouncer. Format of user/password should be like "user" "password".
Now that we created users we can start our connection pooler.
service pgbouncer start
Thats it folks you have pgbouncer up and running. Now lets connect to it at port 6432 [default, this can be updated in pgbouncer.ini file].
You have now successfully connected to pgbouncer. Lets benchmark and see if pgbouncer makes any difference or not. We need pgbench utility to benchmark postgres. To get that utility we need to install postgres contrib package.
yum install postgresql92-contrib
To benchmark we need to create pgbouncer_bench(temporary) database to run our connections against and populate this database with some data.
createdb pgbouncer_bench pgbench -i -s 10 pgbouncer_bench
Now we can start benchmarking our database between port 5432 and 6432(pgbouncer) and see the performance difference.
Performance at 10 client connections:
[opensourcedbms@opensourcedbms ~]$ pgbench -c 10 -C -T 60 pgbouncer_bench starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 10 query mode: simple number of clients: 10 duration: 60 s number of transactions actually processed: 13522 tps = 225.321050 (including connections establishing) tps = 1027.382767 (excluding connections establishing) [opensourcedbms@opensourcedbms ~]$ pgbench -c 10 -C -T 60 -p 6432 pgbouncer_bench Password: starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 10 query mode: simple number of clients: 10 duration: 60 s number of transactions actually processed: 29899 tps = 498.226421 (including connections establishing) tps = 977.361555 (excluding connections establishing)
Performance at 80 client connections:
[opensourcedbms@opensourcedbms ~]$ pgbench -c 80 -C -T 60 pgbouncer_bench starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 10 query mode: simple number of clients: 80 duration: 60 s number of transactions actually processed: 12512 tps = 208.060346 (including connections establishing) tps = 2747.531460 (excluding connections establishing) [opensourcedbms@opensourcedbms ~]$ pgbench -c 80 -C -T 60 -p 6432 pgbouncer_bench Password: starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 10 query mode: simple number of clients: 80 duration: 60 s number of transactions actually processed: 30661 tps = 509.958995 (including connections establishing) tps = 1194.006439 (excluding connections establishing)
You can see the significant improvement of transactions when we are connecting to database using pgbouncer. There are various tweaks that you can do to pgbouncer config file and tune according to your needs.
Please post questions in comments if you have any.
Nice article, very useful 🙂
Very detail and accurate guide. Thanks a lot
Thanks … i can increase TPS with pgbouncer … good job bro …
Hai. I have some error.
I was successfully run this :
[postgres@edb ~]$ pgbench -c 10 -C -T 60 pgbouncer_bench
But when I run this :
[postgres@edb ~]$ pgbench -c 10 -C -T 60 -p 6432 pgbouncer_bench
Getting error :
Connection to database "pgbouncer_bench" failed:
ERROR: No such database: pgbouncer_bench
Do you have idea why this is happen?
I am facing below mentioned error after following the above mentioned steps.
psql: ERROR: pgbouncer cannot connect to server
Could you please able to help me to resolve this issue?
Thanks in advance.
great guide, thanks man !!
Add in the -h for host, thats how I got around this error.
hi Prashanth Goriparth your website is very useful and easy to lean i have every seen before any site like urs thanks and keep post many topics
Set your user is owner of your DB.
I have installed pgbouncer 1.13, and followed the configurations listed here. I have not been able to bring the service up. No indication in logs as to why. Any hint?