Setup pgbouncer connection pooling for PostgreSQL on CentOS/RedHat/Fedora

Setup pgbouncer connection pooling for PostgreSQL on CentOS/RedHat/Fedora

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.

pgbouncer1Once 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.

pgbouncer15

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).

Your full config file should look as following:

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».

pgbouncer6

Now that we created users we can start our connection pooler.

pgbouncer5

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].

pgbouncer7

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.

pgbouncer14

To benchmark we need to create pgbouncer_bench(temporary) database to run our connections against and populate this database with some data.

pgbouncer 10

Now we can start benchmarking our database between port 5432 and 6432(pgbouncer) and see the performance difference.

Performance at 10 client connections:

pgbouncer11

Performance at 80 client connections:

pgbouncer12

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.

Добавить комментарий

Войти с помощью: 

Ваш e-mail не будет опубликован. Обязательные поля помечены *