Archive for category PostgreSQL Connection Pooling

pg_bouncer

The installation and configuration of pg_bouncer is what I like to say “not too complicated”. But if you don’t know what you are doing then you might end up cleaning up after.

I am using postgres version 8.4 and pg_bouncer 1.5.1 for this post. But any other version install should work just the same.

Dependencies:

pgbouncer relies on a library called libevent and for this installation I have used the latest stable release of libevent 2.0.19. The repository for centOS puts the old version of libevent 1.x and hence I used the source of libevent to configure.

you can find the source from here.

PgBouncer uses libevent for low-level socket handling. Libevent 2.x has proper async DNS implementation.

To install libevent:

$ ./configure --prefix=/usr/local --with-libevent=libevent-prefix

$ make

$ make install

To install pgbouncer

Get pg_bouncer from here.

$ ./configure –prefix=/opt/postgres/8.4.9

$ make

$ make install

Once make install is completed pgbouncer will be availabe in the /opt/postgres/8.4.9/bin directory

Configuration:

pgbouncer uses a configuration file with information about the bouncer settings. Below is a snippet of the ini file that is installed in /etc/pgbouncer

The configuration below shows what port the database is running on 6544 and the pgbouncer port 6543. This change requires a database restart as we are changing the actual db port.

Current pgbouncer pool settings will hold upto 1000 connections and the limit on the database can be specified as per requirement.

Log file and process id file from pgbouncer is located in the following directory:

logfile = /etc/pgbouncer/pgbouncer.log

pidfile = /etc/pgbouncer/pgbouncer.pid

NOTE:

Something I discovered a little later after I tested pgbouncer with a new JDBC connection. I got the error message: Unsupported startup parameter: extra_float_digits.

To fix this error I had to un comment the ignore_startup_paramenters setting in pgbouncer.ini file. This would ignore extra_float digit when trying to connect with pg_bouncer.

; 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

User authentication:

pgbouncer has its own user authentication file user_list.txt

This file is placed in /etc/pgbouncer directory and has all the users that connect to the postgres database.

In case there is a user that is added to the postgres database pgbouncer needs to be notified of the change by adding the user to the userlist file. Entry in the userlist.txt file should look like:

“someuser” “somepassword”

INI file:

;; database name = connect string

;;

[databases]

hdap = host=127.0.0.1 dbname=test port=6544 user=postgres

powerdns= host=127.0.0.1 dbname=test1 port=6544 user=postgres

postgres= host=127.0.0.1 dbname=test2 port=6544 user=postgres

;; Configuration section

[pgbouncer]

;;;

;;; Administrative settings

;;;

logfile = /etc/pgbouncer/pgbouncer.log

pidfile = /etc/pgbouncer/pgbouncer.pid

;;;

;;; Where to wait for clients

;;;

; ip address or * which means all ip-s

listen_addr = *

listen_port = 6543

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

;auth_file = /8.0/main/global/pg_auth

auth_file = /etc/pgbouncer/userlist.txt

;;;

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

;;;

;;; Connection limits

;;;

; total number of clients that can connect

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 = 600

Post Installation:

After the installation of pgbouncer it is time to change the database port to 6544 (can be set to any other desired port except 6543 “as we want pg_bouncer to work on that port in this example”). Changes to the database port:

edit the postrgesql.conf file and change:

port=6544

max_connections=300 (or lower to what you system requirement is)

The above change requires a restart of the postgres database. Can be done using pg_ctl -D /path to data dir restart

Or by using the service available in the /etc/init.d directory.

After the database has restarted start pgbouncer with the following syntax:

$ pgbouncer -v -d /etc/pgbouncer/pgbouncer.ini

-v increases verbosity in the log file and -d would start pgbouncer as a background deamon.

Monitoring the bouncer:

pgbouncer connection pool can be reset to a specified value without restarting pgbouncer / postgres database.

psql -p 6543 -U postgres pgbouncer

pgbouncer# reload

pgbouncer comes with different views that show stats on the transactions:

Most frequently show pools show stats and show clients will give all the information required.

Below view shows number of active clients connected and the number that is in waiting.

pgbouncer=# show pools;

database | user | cl_active | cl_waiting | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait

-----------+-----------+-----------+------------+-----------+---------+---------+-----------+----------+---------

test | postgres | 14 | 986 | 13 | 0 | 0 | 0 | 0 | 0

pgbouncer | pgbouncer | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0

show clients will show more granular information as to what the connect_time and the request_time was for the transaction.

pgbouncer=# show clients;

type | user | database | state | addr | port | local_addr | local_port | connect_time | request_time | ptr | link

------+----------+-----------+---------+------+------+------------+------------+---------------------+---------------------+-----------+-----------

C | postgres | test | active | unix | 6543 | unix | 6543 | 2012-05-04 14:09:01 | 2012-05-04 14:13:58 | 0x8ecd7f8 | 0x8eae758

C | postgres | test | active | unix | 6543 | unix | 6543 | 2012-05-04 14:09:01 | 2012-05-04 14:13:58 | 0x8ecb1e8 | 0x8eae2d0

pgbouncer=# show stats;

database | total_requests | total_received | total_sent | total_query_time | avg_req | avg_recv | avg_sent | avg_query

-----------+----------------+----------------+------------+------------------+---------+----------+----------+-----------

test | 510269 | 214569200 | 93842560 | 432492274981 | 1168 | 494235 | 216168 | 855690

pgbouncer | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0

Further information on the bouncer’s usage is available in the below link:

http://pgbouncer.projects.postgresql.org/doc/usage.html

Caveat:

The most effective way to use pgbouncer is to run it in transaction pooling mode the downside is that in transaction mode pgbouncer does not handle prepared statements from the application and there will be errors when trying to run them using pgbouncer. A workaround for this is to implement the following change to the jdbc connection string so that the prepared statements are handled at the database level.

prepareThreshold=0 parameter should be added to the connect string.

String url = "jdbc:postgresql://localhost:6543/test?prepareThreshold=0";
Advertisements

, , ,

Leave a comment

%d bloggers like this: