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

[Tatsuo Ishii]

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