<div dir="ltr">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.<br>
<br><div class="gmail_quote">On Wed, Aug 22, 2012 at 7:06 PM, Tatsuo Ishii <span dir="ltr"><<a href="mailto:ishii@postgresql.org" target="_blank">ishii@postgresql.org</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Great explanation. I would like to add this to somewhere in the document.<br>
Thanks!<br>
--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
<div><div><br>
> Hi Hackers,<br>
><br>
> I have answered max_pool related question a few times, and more than<br>
> once the following explanation cleared up the other party's confusion just<br>
> y reading it, without any more explanation from me. Do you think this<br>
> deserves some place on pgpool docs?<br>
><br>
> max_pool parameter configures how many connections to cache _per<br>
> child_. So if num_init_children is configured to 100, and max_pool is<br>
> configured to 3, then pgpool can potentially open 300 (=3*100) connections<br>
> to the backend database.<br>
><br>
> A child process opens a new backend connection only if the requested<br>
> [user,database] pair is not already in the cache. So if the application<br>
> uses only one user to connect to only one database, say [pguser1,pgdb1],<br>
> then each child will continue to reuse the first connection and will never<br>
> open a second connection, so in effect pgpool will open no more than 100<br>
> backend connections even though max_pool is set to 3.<br>
><br>
> But if the application uses more than one pair of [user,database]<br>
> connection parameters, then each child will cache the connection it might<br>
> already have for another pair, and open a new backend connection for the<br>
> requested pair.<br>
><br>
> For eg., if the application now uses these 4 pairs: [user1,db1]<br>
> [user1,db2] [user2,db1] [user2,db2] to connect to pgpool, then each child<br>
> process can cache up to 3 connections for the first 3 different pairs it<br>
> receives connection requests for. But as soon as it receives a request for<br>
> the 4th pair that it does not yet have a connection for, then it will<br>
> disconnect the oldest connection in the cache and open a new connection for<br>
> the 4th pair.<br>
><br>
> As we already know that there's no guarantee as to which child process<br>
> will handle an incoming connection request, max_pool tries to improve the<br>
> performance a little bit by caching connections of different pairs, in the<br>
> hopes that an incoming connection request might match one of the<br>
> connections cached by the child process. But this also causes an explosion<br>
> 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<br>
> never rejected, and that the connection requests wait until a database<br>
> connection is available, the following condition should be met:<br>
><br>
> max_pool*num_init_children <= (max_connections -<br>
> superuser_reserved_connections)<br>
><br>
> If the application uses superuser connections (which is not<br>
> recommended), then the condition is reduced to:<br>
><br>
> max_pool*num_init_children <= max_connections<br>
><br>
> Setting max_pool to 1 will guarantee that the number of database<br>
> connections opened by pgpool child processes never exceeds the<br>
> num_init_children value. If for performance reasons, as explained above,<br>
> you do wish to set max_pool to more than 1, then max_connections will also<br>
> have to be increased accordingly so that application connection requests do<br>
> not get denied.<br>
><br>
> Best regards,<br>
> --<br>
> Gurjeet Singh<br>
</div></div></blockquote></div><br><br clear="all"><br>-- <br><div dir="ltr">Gurjeet Singh<br><br></div><br>
</div>