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

[Tatsuo Ishii]

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