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

От: Hannu Krosing
Тема: Re: Pooling in Core WAS: Need help in performance tuning.
Дата: ,
Msg-id: 1279472068.16803.18.camel@hvost
(см: обсуждение, исходный текст)
Ответ на: Re: Pooling in Core WAS: Need help in performance tuning.  (Rajesh Kumar Mallah)
Список: pgsql-performance

Скрыть дерево обсуждения

Need help in performance tuning.  (Harpreet singh Wadhwa, )
 Re: Need help in performance tuning.  ("Kevin Grittner", )
 Re: Need help in performance tuning.  (Craig Ringer, )
  Re: Need help in performance tuning.  (Samuel Gendler, )
   Re: Need help in performance tuning.  (Tom Lane, )
    Re: Need help in performance tuning.  (Craig Ringer, )
     Re: Need help in performance tuning.  (Harpreet singh Wadhwa, )
     Re: Need help in performance tuning.  (Craig Ringer, )
     Re: Need help in performance tuning.  (Harpreet singh Wadhwa, )
      Re: Need help in performance tuning.  ("Jorge Montero", )
     Re: Need help in performance tuning.  (Bruce Momjian, )
    Re: Need help in performance tuning.  (Brad Nicholson, )
     Re: Need help in performance tuning.  ("Kevin Grittner", )
      Re: Need help in performance tuning.  (Matthew Wakeling, )
       Re: Need help in performance tuning.  ("Kevin Grittner", )
        Re: Need help in performance tuning.  (Matthew Wakeling, )
         Re: Need help in performance tuning.  ("Kevin Grittner", )
          Re: Need help in performance tuning.  ("Jorge Montero", )
           Re: Need help in performance tuning.  ("Kevin Grittner", )
         Re: Need help in performance tuning.  (Greg Smith, )
          Re: Need help in performance tuning.  ("Kevin Grittner", )
           Re: Pooling in Core WAS: Need help in performance tuning.  (Josh Berkus, )
            Re: Pooling in Core WAS: Need help in performance tuning.  (Craig Ringer, )
             Re: Pooling in Core WAS: Need help in performance tuning.  (Robert Haas, )
              Re: Pooling in Core WAS: Need help in performance tuning.  (Josh Berkus, )
               Re: Pooling in Core WAS: Need help in performance tuning.  (Tom Lane, )
                Re: Pooling in Core WAS: Need help in performance tuning.  (Matthew Wakeling, )
                 Re: Pooling in Core WAS: Need help in performance tuning.  (Craig Ringer, )
                  Re: Pooling in Core WAS: Need help in performance tuning.  ("Kevin Grittner", )
                   Re: Pooling in Core WAS: Need help in performance tuning.  (Rajesh Kumar Mallah, )
                    Re: Pooling in Core WAS: Need help in performance tuning.  (Rajesh Kumar Mallah, )
                    Re: Pooling in Core WAS: Need help in performance tuning.  (Greg Smith, )
                     Re: Pooling in Core WAS: Need help in performance tuning.  (Rajesh Kumar Mallah, )
                     Re: Pooling in Core WAS: Need help in performance tuning.  (Rajesh Kumar Mallah, )
                      Re: Pooling in Core WAS: Need help in performance tuning.  (Rajesh Kumar Mallah, )
                       Re: Pooling in Core WAS: Need help in performance tuning.  (Tatsuo Ishii, )
                        Re: Pooling in Core WAS: Need help in performance tuning.  (Rajesh Kumar Mallah, )
                         Re: Pooling in Core WAS: Need help in performance tuning.  (Tatsuo Ishii, )
                    Re: Pooling in Core WAS: Need help in performance tuning.  (Hannu Krosing, )
                  Re: Pooling in Core WAS: Need help in performance tuning.  (Hannu Krosing, )
                  Re: Pooling in Core WAS: Need help in performance tuning.  (Robert Haas, )
                   Re: Pooling in Core WAS: Need help in performance tuning.  (Andres Freund, )
                    Re: Pooling in Core WAS: Need help in performance tuning.  (Robert Haas, )
                     Re: Pooling in Core WAS: Need help in performance tuning.  (Tom Lane, )
                      Re: Pooling in Core WAS: Need help in performance tuning.  (Robert Haas, )
                       Re: Pooling in Core WAS: Need help in performance tuning.  (Tom Lane, )
                        Re: Pooling in Core WAS: Need help in performance tuning.  (Robert Haas, )
                         Re: Pooling in Core WAS: Need help in performance tuning.  (Tom Lane, )
                        Re: Pooling in Core WAS: Need help in performance tuning.  (Vitalii Tymchyshyn, )
                        Re: Pooling in Core WAS: Need help in performance tuning.  (Josh Berkus, )
                         Re: Pooling in Core WAS: Need help in performance tuning.  (Robert Haas, )
                          Re: Pooling in Core WAS: Need help in performance tuning.  (Tom Lane, )
                           Re: Pooling in Core WAS: Need help in performance tuning.  (Andres Freund, )
                           Re: Pooling in Core WAS: Need help in performance tuning.  (Robert Haas, )
                            Re: Pooling in Core WAS: Need help in performance tuning.  (Josh Berkus, )
                      Re: Pooling in Core WAS: Need help in performance tuning.  (Craig Ringer, )
                  Re: Pooling in Core WAS: Need help in performance tuning.  (Robert Haas, )
                   Re: Pooling in Core WAS: Need help in performance tuning.  ("Joshua D. Drake", )
                    Re: Pooling in Core WAS: Need help in performance tuning.  (Hannu Krosing, )
                     Re: Pooling in Core WAS: Need help in performance tuning.  ("Joshua D. Drake", )
                      Re: Pooling in Core WAS: Need help in performance tuning.  (Hannu Krosing, )
                     Re: Pooling in Core WAS: Need help in performance tuning.  ("Joshua D. Drake", )
                   Re: Pooling in Core WAS: Need help in performance tuning.  (Hannu Krosing, )
                    Re: Pooling in Core WAS: Need help in performance tuning.  (Robert Haas, )
                     Re: Pooling in Core WAS: Need help in performance tuning.  (Hannu Krosing, )
                      Re: Pooling in Core WAS: Need help in performance tuning.  (Robert Haas, )
                       Re: Pooling in Core WAS: Need help in performance tuning.  (Andres Freund, )
                       Re: Pooling in Core WAS: Need help in performance tuning.  (Craig Ringer, )
                        Re: Pooling in Core WAS: Need help in performance tuning.  (Robert Haas, )
                   Re: Pooling in Core WAS: Need help in performance tuning.  (Scott Carey, )
                    Re: Pooling in Core WAS: Need help in performance tuning.  (Joshua Tolley, )
                     Re: Pooling in Core WAS: Need help in performance tuning.  (Greg Smith, )
                      Re: Pooling in Core WAS: Need help in performance tuning.  (Andres Freund, )
                      Re: Pooling in Core WAS: Need help in performance tuning.  (Craig Ringer, )
                       Re: Pooling in Core WAS: Need help in performance tuning.  (Dimitri Fontaine, )
                       Re: Pooling in Core WAS: Need help in performance tuning.  (Hannu Krosing, )
                   Re: Pooling in Core WAS: Need help in performance tuning.  ("Joshua D. Drake", )
                Re: Pooling in Core WAS: Need help in performance tuning.  (Dimitri Fontaine, )
                 Re: Pooling in Core WAS: Need help in performance tuning.  (Magnus Hagander, )
             Re: Pooling in Core WAS: Need help in performance tuning.  (Scott Carey, )
            Re: Pooling in Core WAS: Need help in performance tuning.  (Samuel Gendler, )
          Re: Need help in performance tuning.  (Jesper Krogh, )
           Re: Need help in performance tuning.  (Greg Smith, )
            Re: Need help in performance tuning.  ("Pierre C", )
     Re: Need help in performance tuning.  (Mark Kirkwood, )
    Re: Need help in performance tuning.  (Robert Haas, )
    Re: Need help in performance tuning.  (Hannu Krosing, )
     Re: Need help in performance tuning.  (Scott Marlowe, )
      Re: Need help in performance tuning.  ("Kevin Grittner", )
       Re: Need help in performance tuning.  (Hannu Krosing, )
 Re: Pooling in Core WAS: Need help in performance tuning.  (Rajesh Kumar Mallah, )

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
> <> wrote:
>         Craig Ringer <> 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
>         ()
>         To make changes to your subscription:
>         http://www.postgresql.org/mailpref/pgsql-performance
>
>




В списке pgsql-performance по дате сообщения:

От: Tatsuo Ishii
Дата:
Сообщение: Re: Pooling in Core WAS: Need help in performance tuning.
От: Sergey Konoplev
Дата:
Сообщение: Re: What is the best way to optimize the query.