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

On Mon, Jul 12, 2010 at 6:11 PM, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov
> wrote:

> Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> wrote:
>
> > So rather than asking "should core have a connection pool" perhaps
> > what's needed is to ask "what can an in-core pool do that an
> > external pool cannot do?"
>
> (1) It can prevent the most pessimal performance problems resulting
> from lack of an external connection pool (or a badly configured one)
> by setting a single GUC. Configuration tools could suggest a good
> value during initial setup.
>
> (2) It can be used without installing and configuring a more
> sophisticated and complex product.
>
> (3) It might reduce latency because it avoids having to receive,
> parse, and resend data in both directions -- eliminating one "hop".
> I know the performance benefit would usually accrue to the external
> connection pooler, but there might be some circumstances where a
> built-in pool could win.
>
> (4) It's one more checkbox which can be ticked off on some RFPs.
>
> That said, I fully agree that if we can include good documentation
> on the external poolers and we can get packagers to include poolers
> in their distribution, that gets us a much bigger benefit. A
> built-in solution would only be worthwhile if it was simple enough
> and lightweight enough not to be a burden on execution time or
> maintenance. Maybe that's too big an if.
>
> -Kevin
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>