Re: Pooling in Core WAS: Need help in performance tuning. - Mailing list pgsql-performance

On Sun, 2010-07-18 at 21:48 +0530, Rajesh Kumar Mallah wrote:
> 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

Why in VM (openvz container) ?

Did you also try it in the same OS as your appserver ?

Perhaps even connecting from appserver via unix seckets ?

> 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

Are you sure you are not measuring how much sunning pgbouncer slows down
pgbench directly, by competing for CPU resources and not by adding
latency to requests ?


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

If you have less clients than pgbouncer connections, you can't have any
clients waiting in pgbouncer, as each of them is allocated it's own
connection right away.

What you were measuring was

1. pgbench and pgbouncer competeing for the same CPU
2. overhead from 2 hops to db (app-proxy-db) instead of 1 (app-db)

> Warm Regds
> Rajesh Kumar Mallah.
> CTO - tradeindia.com.
>
>
>
> Keywords: pgbouncer performance
>
>
>
>
>
>
>
>
>
>
> On Mon, Jul 12, 2010 at 6:11 PM, Kevin Grittner
> <Kevin.Grittner@wicourts.gov> wrote:
>         Craig Ringer <craig@postnewspapers.com.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@postgresql.org)
>         To make changes to your subscription:
>         http://www.postgresql.org/mailpref/pgsql-performance
>
>