<div dir="ltr"><span class="il">Hi Hackers,<br><br> 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?<br>
<br> max_pool</span> parameter configures how many connections to cache _per child_. So if num_init_children is configured to 100, and <span class="il">max_pool</span> is configured to 3, then pgpool can potentially open 300 (=3*100) connections to the backend database.<br>
<br> 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 <span class="il">max_pool</span> is set to 3.<br>
<br> 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.<br>
<br> 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.<br>
<br> As we already know that there's no guarantee as to which child process will handle an incoming connection request, <span class="il">max_pool</span>
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.<br>
<br> 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:<br><br>
<span class="il">max_pool</span>*num_init_children <= (max_connections - superuser_reserved_connections)<br><div id=":1k0"><br> If the application uses superuser connections (which is not recommended), then the condition is reduced to:<br>
<br><span class="il">max_pool</span>*num_init_children <= max_connections<br>
<br> Setting <span class="il">max_pool</span> 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 <span class="il">max_pool</span>
to more than 1, then max_connections will also have to be increased
accordingly so that application connection requests do not get denied.<br>
<br>Best regards,</div>-- <br><div dir="ltr">Gurjeet Singh<br><br></div><br>
</div>