Connection Pooling with Pgbouncer on PostgreSQL 9.0

Connection pooling, Why we go for connection pooling in PostgreSQL, When your application demands for very good number of concurrent connection hits then you need to approach it, because Connection pool sits between your application and the database.

Idea behind connection pool is that you have enough connections to use of all the available resources and any incoming requests are re-used without dropping the database connection and keeping ready for a new connection to use.

pgbouncer is lightweight connection pooler. pgBouncer runs as a single process, not spawning a process per connection, which relies on library named libevent for connection pooling.

pgbouncer setup on PostgreSQL 9.0 is very simple, however there is small change with the latest version you need to create manual pg_auth file. pgbouncer uses pg_auth file for user authentication. Earlier verion of PostgreSQL 9.0, you can find the pg_auth file under $PGDATA/global/pg_auth, now in the latest version that file has been removed and placed in pg_catalog as table 'pg_auth'.

pgbouncer Setup:

1. First, download libevent library for pgbouncer.
Download link for libevent:
http://www.monkey.org/~provos/libevent-2.0.12-stable.tar.gz

tar -xvf libevent-2.0.12-stable.tar.gz
cd libevent-2.0.12-stable
./configure 
make
make install

2. Download the latest pgbouncer tar and configure to your PostgreSQL 9.0.
http://pgfoundry.org/frs/download.php/2912/pgbouncer-1.4.tgz

tar -xvf pgbouncer-1.4
cd pgbouncer-1.4
./configure --prefix=/opt/PostgreSQL/9.0/bin
make
make install

3. Create a libevent-i386.conf file in /etc/ld.so.conf.d directory

vi /etc/ld.so.conf.d/libevent-i386.conf
/usr/local/lib
:wq!

4. Run the ldconfig to apply new changes.

#ldconfig

5. Change the ownership of pgbouncer utility in PostgreSQL binary to postgres user.

chown -R postgres:postgres /opt/PostgreSQL/9.0/bin/bin/pgbouncer

6. Create the pgbouncer_auth file for users authentication.

7. Create pgbouncer.ini file with postgres user permission under /etc directory.

8. Start pgbouncer

-bash-4.1$ ./pgbouncer -d /etc/pgbouncer.ini
2011-08-14 11:42:00.925 1949 LOG File descriptor limit: 1024 (H:1024), max_client_conn: 1000, max fds possible: 1010

9. Connect to the databases using pgbouncer

10. Getting help: Connect to pgbouncer database and get helped.

$ psql -p 6432 -U postgres pgbouncer
pgbouncer=# show help;

For better understanding on pg_auth you can find in below link by 'depesz'.
http://www.depesz.com/index.php/2010/12/04/auto-refreshing-password-file-for-pgbouncer/

Do post your comments which are highly appreciated.

--Raghav

pgmemcache vs Infinite Cache

In my recent post on pgmemcache, there were couple of questions asked which were really interesting and made me to work on it. I should thank for it :)

Questions:
1. Is pgmemcache application transparent ?
2. Is there any synchronization between memcached and PostgreSQL Shared buffers ?

Answer:

pgmemcache(memcached) is not application transparent, you need to do changes in the application for pushing or retreiving the data from the cache.

EnterpriseDB, product PostgresPlus Advance Server includes a feature called Infinite Cache, which is based on production proven technology memcached the open source distributed object cache.

About EnterpriseDB, the Enterprise PostgreSQL Company, provides enterprise-class PostgreSQL products of the world's most advanced open source database. The company's Postgres Plus products are ideally suited for transaction-intensive applications requiring superior performance, massive scalability and compatibility with proprietary database products.

Overview

Above diagram helps to understand the architecture of pgmemcache vs infinite cache. In infinite cache, all the pages are first searched in shared_buffers and then in Infinite Cache. Synchronization between shared buffer cache and infinite cache makes application transparency, which is not the case with pgmemcache.

Infinite Cache, is faster and completely application transparent. No special code is needed from developers. Warms up your cache with multiple parallel processes and pre-loads cache at startup reducing warming time.

To avail infinite cache you have to download the PostgresPlus Advance Server which is Oracle Compatible product bundled with Infinite Cache.
Download Link:
http://www.enterprisedb.com/downloads/postgres-postgresql-downloads

Implementation of Infinite cache is as simple as memcached, below link will help in setting up the infinite cache.

http://www.enterprisedb.com/docs/en/8.4/perf/Postgres_Plus_Advanced_Server_Performance_Guide-04.htm

Very informative discussion on PostgreSQL Community Forum:-

http://archives.postgresql.org/pgsql-performance/2011-07/msg00001.php

--Raghav