[pgpool-hackers: 117] Re: Explanation of max_pool parameter

[Gurjeet Singh]

"Relationship between max_pool, num_init_children, and max_connections" ?

On Thu, Aug 23, 2012 at 5:43 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:

> Ok. Can you please provide the document title?
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
>
> > It doesn't sound like an FAQ item. Either a page in Wiki or in User
> > contributed docs would be fine with me.
> >
> > On Wed, Aug 22, 2012 at 9:34 PM, Tatsuo Ishii <ishii at postgresql.org>
> wrote:
> >
> >> Putting into Wiki and add link in docs sounds nice idea.
> >> What about adding to FAQ?
> >>
> >> Or do you want to place "User contributed documentation" section?
> >> http://www.pgpool.net/mediawiki/index.php/Documentation
> >> --
> >> Tatsuo Ishii
> >> SRA OSS, Inc. Japan
> >> English: http://www.sraoss.co.jp/index_en.php
> >> Japanese: http://www.sraoss.co.jp
> >>
> >> > Because it is quite elaborate explanation, it might not be a good fit
> for
> >> > docs, which are supposed to be concise. Probably it should reside
> >> somewhere
> >> > in wiki and the docs for max_pool should link there. But don't mind
> me if
> >> > you think these can fit right in the docs.
> >> >
> >> > On Wed, Aug 22, 2012 at 7:06 PM, Tatsuo Ishii <ishii at postgresql.org>
> >> wrote:
> >> >
> >> >> Great explanation. I would like to add this to somewhere in the
> >> document.
> >> >> Thanks!
> >> >> --
> >> >> Tatsuo Ishii
> >> >> SRA OSS, Inc. Japan
> >> >> English: http://www.sraoss.co.jp/index_en.php
> >> >> Japanese: http://www.sraoss.co.jp
> >> >>
> >> >> > Hi Hackers,
> >> >> >
> >> >> >     I have answered max_pool related question a few times, and more
> >> than
> >> >> > once the following explanation cleared up the other party's
> confusion
> >> >> just
> >> >> > y reading it, without any more explanation from me. Do you think
> this
> >> >> > deserves some place on pgpool docs?
> >> >> >
> >> >> >     max_pool parameter configures how many connections to cache
> _per
> >> >> > child_. So if num_init_children is configured to 100, and max_pool
> is
> >> >> > configured to 3, then pgpool can potentially open 300 (=3*100)
> >> >> connections
> >> >> > to the backend database.
> >> >> >
> >> >> >     A child process opens a new backend connection only if the
> >> requested
> >> >> > [user,database] pair is not already in the cache. So if the
> >> application
> >> >> > uses only one user to connect to only one database, say
> >> [pguser1,pgdb1],
> >> >> > then each child will continue to reuse the first connection and
> will
> >> >> never
> >> >> > open a second connection, so in effect pgpool will open no more
> than
> >> 100
> >> >> > backend connections even though max_pool is set to 3.
> >> >> >
> >> >> >     But if the application uses more than one pair of
> [user,database]
> >> >> > connection parameters, then each child will cache the connection it
> >> might
> >> >> > already have for another pair, and open a new backend connection
> for
> >> the
> >> >> > requested pair.
> >> >> >
> >> >> >     For eg., if the application now uses these 4 pairs: [user1,db1]
> >> >> > [user1,db2] [user2,db1] [user2,db2] to connect to pgpool, then each
> >> child
> >> >> > process can cache up to 3 connections for the first 3 different
> pairs
> >> it
> >> >> > receives connection requests for. But as soon as it receives a
> request
> >> >> for
> >> >> > the 4th pair that it does not yet have a connection for, then it
> will
> >> >> > disconnect the oldest connection in the cache and open a new
> >> connection
> >> >> for
> >> >> > the 4th pair.
> >> >> >
> >> >> >     As we already know that there's no guarantee as to which child
> >> >> process
> >> >> > will handle an incoming connection request, max_pool tries to
> improve
> >> the
> >> >> > performance a little bit by caching connections of different
> pairs, in
> >> >> the
> >> >> > hopes that an incoming connection request might match one of the
> >> >> > connections cached by the child process. But this also causes an
> >> >> explosion
> >> >> > in the number of connections that pgpool would request from the
> >> database.
> >> >> >
> >> >> >     So, in order to guarantee that the application connection
> requests
> >> >> are
> >> >> > never rejected, and that the connection requests wait until a
> database
> >> >> > connection is available, the following condition should be met:
> >> >> >
> >> >> > max_pool*num_init_children <= (max_connections -
> >> >> > superuser_reserved_connections)
> >> >> >
> >> >> >     If the application uses superuser connections (which is not
> >> >> > recommended), then the condition is reduced to:
> >> >> >
> >> >> > max_pool*num_init_children <= max_connections
> >> >> >
> >> >> >     Setting max_pool to 1 will guarantee that the number of
> database
> >> >> > connections opened by pgpool child processes never exceeds the
> >> >> > num_init_children value. If for performance reasons, as explained
> >> above,
> >> >> > you do wish to set max_pool to more than 1, then max_connections
> will
> >> >> also
> >> >> > have to be increased accordingly so that application connection
> >> requests
> >> >> do
> >> >> > not get denied.
> >> >> >
> >> >> > Best regards,
> >> >> > --
> >> >> > Gurjeet Singh
> >> >>
> >> >
> >> >
> >> >
> >> > --
> >> > Gurjeet Singh
> >>
> >
> >
> >
> > --
> > Gurjeet Singh
>



-- 
Gurjeet Singh
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20120824/dcc70ebd/attachment.html>