[pgpool-hackers: 111] Explanation of max_pool parameter

[Gurjeet Singh]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20120822/049d5c2d/attachment.html>