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

[Tatsuo Ishii]

Done.
http://www.pgpool.net/mediawiki/index.php/Relationship_between_max_pool,_num_init_children,_and_max_connections

Linked from document 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

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