Re: Pooling in Core WAS: Need help in performance tuning.

Hi,

Sorry, if posting here was not proper instead of starting new thread
(I am really not sure if its bad thing to do)

I would like to share my recent experience on implementation of 
client side pooling using  pgbouncer. By client side i mean that
the the pgbouncer process in not on same machine as postgresql server.
In first trial pgbouncer and postgresql were in same machine & phbouncer
was connecting to postgresql using unix domain sockets. But i shifted it
laters owing to high CPU usage > 50%. ( using top)
Now i have shifted pgbouncer into a virtual machine (openvz container)
in the application server hardware and all my applications on other virtual machines
(web applications) connect to pgbouncer on this virtual machine.

I tested the setup with pgbench in two scenarios

1. connecting to DB server directly
2. connecting to DB via pgbouncer

the no of clients was 10 ( -c 10)  carrying out 10000 transactions each (-t 10000) .
pgbench db was initilised with scaling  factor -s 100.

since client count was less there was no queuing of requests in pgbouncer
i would prefer to say  it was in 'passthrough' mode.

the result was that

1. direct ~ 2000 tps
2. via pgbouncer ~ 1200 tps

----------------------------------------------------------------------------------------------------------------------------------------------
Experience on deploying to production environment with real world load/usage pattern
----------------------------------------------------------------------------------------------------------------------------------------------

Pgbouncer was put in same machine as postgresql connecting via unix domain
to server and tcp sockets with clients.

1. There was drastic reduction in CPU loads  from  30 to 10 ldavg
2. There were no clients waiting, pool size was 150 and number of active
    connections was 100-120.
3. Application performance was worse (inspite of 0 clients waiting )


I am still waiting to see what is the effect of shifting out pgbounce from dbserver
to appserver, but with pgbench results i am not very hopeful. I am curious why
inspite of 0 clients waiting pgbounce introduces a drop in tps.

Warm Regds
Rajesh Kumar Mallah.
CTO - tradeindia.com.



Keywords: pgbouncer performance