<html><head></head><body><div style="font-family:Helvetica Neue, Helvetica, Arial, sans-serif;font-size:medium;"><div><div>Hi Thomas,</div><div><br></div><div>You have to check in the doc to be sure but I believe that connection_life_time is not going to help you: it means that after so many seconds pgpool will "refresh" the connection (destroy & recreate). This can help avoid memory leaks. The other settings is useful if your client do not disconnect : after client_idle_limit pgpool will give back the connection to the pool (make it available for another client)</div><div><br></div><div>If your client application is not using a connection pool on the client side (on top of pgpool, a connection pool with sharing at the session level), then it is very important that they explicitly disconnect each time so that the connection can be re-used. You can alleviate the issue with client_idle_limit but it is not enough.</div><div><br></div><div>Usually having 100 concurrent connections to postgres is already a lot, you cannot scale that way,</div><div><br></div><div>Pierre</div><div><br></div></div><div><br></div><div><br></div><hr><div id="ydpe134bc72yahoo_quoted_7031396730" class="ydpe134bc72yahoo_quoted"><div style="font-family:'Helvetica Neue', Helvetica, Arial, sans-serif;font-size:13px;color:#26282a;"><div>On Wednesday, June 7, 2017, 7:49:32 AM GMT+2, Thomas Höll <Thomas.Hoell@neos-it.de> wrote:</div><div><br></div><div><br></div><div><div id="ydpe134bc72yiv2685388014"><div>
<div>Pierre,</div>
<div><br clear="none">
</div>
<div>in my case the problem probably is that I cannot determine the amount of connections the application will spawn.</div>
<div><br clear="none">
</div>
<div>The activiti engine does not utilize a connection pull, but will spawn a new connection per user and process step. So, the more users using the application,</div>
<div>the more connections Pgpool needs to handle in parallel.</div>
<div><br clear="none">
</div>
<div>I've talked to the developers, they try to configure activiti to use connection pooling.</div>
<div><br clear="none">
</div>
<div>Also, do you have any suggestions regarding connection_life_time and client_idle_limit? I'm quite unsure how to configure that. At the moment, both are set to 0.</div>
<div>Does that mean that the connection will be cached forever unless the client closes it properly?</div>
<div><br clear="none">
</div>
<div>Thomas</div>
<div><br clear="none">
</div>
<div class="ydpe134bc72yiv2685388014yqt9179950694" id="ydpe134bc72yiv2685388014yqt55690"><div>On Tue, 2017-06-06 at 15:57 +0000, Pierre Timmermans wrote:</div>
<blockquote type="cite">
<div style="font-family:Helvetica Neue, Helvetica, Arial, sans-serif;font-size:medium;">
<div>
<div>Thomas</div>
<div><br clear="none">
</div>
<div>Maybe you simply need to increase the value of num_init_children. Once the pool is full, i.e. once there are num_init_children active connections, then the next connection attempt will hang until a connection is free. So it will be exactly like the behavior
you are seing.</div>
<div><br clear="none">
</div>
<div>You can do a select from pg_stat_activity (connect directly to postgres, not to pgpool, otherwise you will be queued): it the number of rows is equal to num_init_children then for sure you are hitting this limit</div>
<div><br clear="none">
</div>
<div>Pierre</div>
<div><br clear="none">
</div>
</div>
<div><br clear="none">
</div>
<div><br clear="none">
</div>
<hr>
<div class="ydpe134bc72yiv2685388014ydp699df61yahoo_quoted" id="ydpe134bc72yiv2685388014ydp699df61yahoo_quoted_7281814102">
<div style="font-family:'Helvetica Neue', Helvetica, Arial, sans-serif;font-size:13px;color:#26282a;">
<div>On Tuesday, June 6, 2017, 4:01:43 PM GMT+2, Thomas Höll <Thomas.Hoell@neos-it.de> wrote:</div>
<div><br clear="none">
</div>
<div><br clear="none">
</div>
<div>
<div id="ydpe134bc72yiv2685388014ydp699df61yiv0805466400">
<div>
<div>Hi all,</div>
<div><br clear="none">
</div>
<div>we're having a streaming replication setup as backend for one of our client's application we're hosting.</div>
<div>The backend consists of two virtual machines (Fedora Server 25) running PostgreSQL 9.6 and Pgpool 3.6.4 (this also happened with previous Pgpool versions).</div>
<div><br clear="none">
</div>
<div>Those machines are configured for streaming replication in master/slave mode. The Postgres-Instances are running fine, but Pgpool is giving us troubles.</div>
<div><br clear="none">
</div>
<div>After some time, Pgpool stops accepting connections. They are not rejected, the application trying to connect simply hangs. I have to stop the corresponding Pgpool-Instance</div>
<div>and the other takes over. The stopping itself takes forever. After some time, systemd simply kills the processes and I have to manually delete the leftover sockets.</div>
<div><br clear="none">
</div>
<div>I have absolutely no idea what's causing this. The Postgres-Instances are configured to accept up to 1000 connections, which should not be the problem.</div>
<div><br clear="none">
</div>
<div>About the application accessing the database:</div>
<div><br clear="none">
</div>
<div>There are multiple instances, each using a separate database. The application uses Activiti, which DOES NOT pool connections. So we're having a pretty high amount of</div>
<div>connections per second. The problem mostly occurs during peak hours, so load is most likely a factor. </div>
<div><br clear="none">
</div>
<div>I've tried plaing around with num_init_children and max_pool. Increasing max_pool and lowering num_init_children made things a lot worse.</div>
<div><br clear="none">
</div>
<div>Also, Pgpool still accepts pool join requests from other Pgpool instances, it's just that database connections aren't handled anymore.</div>
<div><br clear="none">
</div>
<div>You can find my pgpool.conf here: <a shape="rect" href="https://pastebin.com/4ee2bhhf" rel="nofollow" target="_blank">
https://pastebin.com/4ee2bhhf</a></div>
<div><br clear="none">
</div>
<div>Regards,</div>
<div>Thomas</div>
<div><br clear="none">
</div>
<div><span>
</span><pre>-- <br clear="none"></pre>
<p class="ydpe134bc72yiv2685388014ydp699df61yiv0805466400MsoNormal" style="margin:0cm 0cm 0.0001pt;font-family:Calibri, sans-serif;color:rgb(0, 0, 0);">
<b><span style="font-size:12pt;color:rgb(38, 38, 38);">NeosIT GmbH</span></b></p>
<p class="ydpe134bc72yiv2685388014ydp699df61yiv0805466400MsoNormal" style="margin:0cm 0cm 0.0001pt;font-family:Calibri, sans-serif;color:rgb(0, 0, 0);">
<span style="color:gray;">Wir schaffen IT, die die Welt ein Stück einfacher macht.</span></p>
<p class="ydpe134bc72yiv2685388014ydp699df61yiv0805466400MsoNormal" style="margin:0cm 0cm 0.0001pt;font-family:Calibri, sans-serif;color:rgb(0, 0, 0);">
<b><span style="font-size:12pt;color:rgb(127, 127, 127);"> </span></b></p>
<p class="ydpe134bc72yiv2685388014ydp699df61yiv0805466400MsoNormal" style="margin:0cm 0cm 0.0001pt;font-family:Calibri, sans-serif;color:rgb(0, 0, 0);">
<span style="color:rgb(127, 127, 127);">Schachtweg 1</span><span style="color:rgb(200, 231, 168);"></span></p>
<p class="ydpe134bc72yiv2685388014ydp699df61yiv0805466400MsoNormal" style="margin:0cm 0cm 0.0001pt;font-family:Calibri, sans-serif;color:rgb(0, 0, 0);">
<span style="color:rgb(127, 127, 127);">38440 Wolfsburg</span></p>
<p class="ydpe134bc72yiv2685388014ydp699df61yiv0805466400MsoNormal" style="margin:0cm 0cm 0.0001pt;font-family:Calibri, sans-serif;color:rgb(0, 0, 0);">
<span style="color:rgb(127, 127, 127);"> </span></p>
<p class="ydpe134bc72yiv2685388014ydp699df61yiv0805466400MsoNormal" style="margin:0cm 0cm 0.0001pt;font-family:Calibri, sans-serif;color:rgb(0, 0, 0);">
<span style="color:rgb(127, 127, 127);">Tel. +49 5361 83494-23</span></p>
<p class="ydpe134bc72yiv2685388014ydp699df61yiv0805466400MsoNormal" style="margin:0cm 0cm 0.0001pt;font-family:Calibri, sans-serif;color:rgb(0, 0, 0);">
<span style="color:rgb(127, 127, 127);">Fax +49 5361 83494-94</span></p>
<p class="ydpe134bc72yiv2685388014ydp699df61yiv0805466400MsoNormal" style="margin:0cm 0cm 0.0001pt;font-family:Calibri, sans-serif;color:rgb(0, 0, 0);">
<span style="color:gray;"><a shape="rect" href="mailto:christoph.steindorff@neos-it.de" style="color:purple;" rel="nofollow" target="_blank"><span style="color:gray;">mailto:thomas.hoell@neos-it.de</span></a></span></p>
<p class="ydpe134bc72yiv2685388014ydp699df61yiv0805466400MsoNormal" style="margin:0cm 0cm 0.0001pt;font-family:Calibri, sans-serif;color:rgb(0, 0, 0);">
<span style="color:gray;"><a shape="rect" href="http://www.neos-it.de/" style="color:purple;" rel="nofollow" target="_blank"><span style="color:gray;">http://www.neos-it.de/</span></a></span></p>
<p class="ydpe134bc72yiv2685388014ydp699df61yiv0805466400MsoNormal" style="margin:0cm 0cm 0.0001pt;font-family:Calibri, sans-serif;color:rgb(0, 0, 0);">
<span style="color:gray;"><a shape="rect" href="http://www.facebook.com/neositgmbh" style="color:purple;" rel="nofollow" target="_blank"><span style="color:gray;">http://www.facebook.com/neositgmbh</span></a></span></p>
<p class="ydpe134bc72yiv2685388014ydp699df61yiv0805466400MsoNormal" style="margin:0cm 0cm 0.0001pt;font-family:Calibri, sans-serif;color:rgb(0, 0, 0);">
<span style="color:rgb(127, 127, 127);"> </span></p>
<p class="ydpe134bc72yiv2685388014ydp699df61yiv0805466400MsoNormal" style="margin:0cm 0cm 0.0001pt;font-family:Calibri, sans-serif;color:rgb(0, 0, 0);">
<span style="color:rgb(127, 127, 127);">Handelsregister: Amtsgericht Braunschweig, HRB 203557</span></p>
<p class="ydpe134bc72yiv2685388014ydp699df61yiv0805466400MsoNormal" style="margin:0cm 0cm 0.0001pt;font-family:Calibri, sans-serif;color:rgb(0, 0, 0);">
<span style="color:rgb(127, 127, 127);">Geschäftsführer: Sebastian Schier</span></p>
</div>
</div>
</div>
_______________________________________________<br clear="none">
pgpool-general mailing list<br clear="none">
<a shape="rect" href="mailto:pgpool-general@pgpool.net" rel="nofollow" target="_blank">pgpool-general@pgpool.net</a><br clear="none">
<a shape="rect" href="http://www.pgpool.net/mailman/listinfo/pgpool-general" rel="nofollow" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br clear="none">
</div>
</div>
</div>
</div>
</blockquote></div>
<div><span>
</span><pre>-- <br clear="none"></pre>
<p class="ydpe134bc72yiv2685388014MsoNormal" style="margin:0cm 0cm 0.0001pt;font-family:Calibri, sans-serif;color:rgb(0, 0, 0);">
<b><span style="font-size:12pt;color:rgb(38, 38, 38);">NeosIT GmbH</span></b></p>
<p class="ydpe134bc72yiv2685388014MsoNormal" style="margin:0cm 0cm 0.0001pt;font-family:Calibri, sans-serif;color:rgb(0, 0, 0);">
<span style="color:gray;">Wir schaffen IT, die die Welt ein Stück einfacher macht.</span></p>
<p class="ydpe134bc72yiv2685388014MsoNormal" style="margin:0cm 0cm 0.0001pt;font-family:Calibri, sans-serif;color:rgb(0, 0, 0);">
<b><span style="font-size:12pt;color:rgb(127, 127, 127);"> </span></b></p>
<p class="ydpe134bc72yiv2685388014MsoNormal" style="margin:0cm 0cm 0.0001pt;font-family:Calibri, sans-serif;color:rgb(0, 0, 0);">
<span style="color:rgb(127, 127, 127);">Schachtweg 1</span><span style="color:rgb(200, 231, 168);"></span></p>
<p class="ydpe134bc72yiv2685388014MsoNormal" style="margin:0cm 0cm 0.0001pt;font-family:Calibri, sans-serif;color:rgb(0, 0, 0);">
<span style="color:rgb(127, 127, 127);">38440 Wolfsburg</span></p>
<p class="ydpe134bc72yiv2685388014MsoNormal" style="margin:0cm 0cm 0.0001pt;font-family:Calibri, sans-serif;color:rgb(0, 0, 0);">
<span style="color:rgb(127, 127, 127);"> </span></p>
<p class="ydpe134bc72yiv2685388014MsoNormal" style="margin:0cm 0cm 0.0001pt;font-family:Calibri, sans-serif;color:rgb(0, 0, 0);">
<span style="color:rgb(127, 127, 127);">Tel. +49 5361 83494-23</span></p>
<p class="ydpe134bc72yiv2685388014MsoNormal" style="margin:0cm 0cm 0.0001pt;font-family:Calibri, sans-serif;color:rgb(0, 0, 0);">
<span style="color:rgb(127, 127, 127);">Fax +49 5361 83494-94</span></p>
<p class="ydpe134bc72yiv2685388014MsoNormal" style="margin:0cm 0cm 0.0001pt;font-family:Calibri, sans-serif;color:rgb(0, 0, 0);">
<span style="color:gray;"><a shape="rect" href="mailto:christoph.steindorff@neos-it.de" style="color:purple;" rel="nofollow" target="_blank"><span style="color:gray;">mailto:thomas.hoell@neos-it.de</span></a></span></p>
<p class="ydpe134bc72yiv2685388014MsoNormal" style="margin:0cm 0cm 0.0001pt;font-family:Calibri, sans-serif;color:rgb(0, 0, 0);">
<span style="color:gray;"><a shape="rect" href="http://www.neos-it.de/" style="color:purple;" rel="nofollow" target="_blank"><span style="color:gray;">http://www.neos-it.de/</span></a></span></p>
<p class="ydpe134bc72yiv2685388014MsoNormal" style="margin:0cm 0cm 0.0001pt;font-family:Calibri, sans-serif;color:rgb(0, 0, 0);">
<span style="color:gray;"><a shape="rect" href="http://www.facebook.com/neositgmbh" style="color:purple;" rel="nofollow" target="_blank"><span style="color:gray;">http://www.facebook.com/neositgmbh</span></a></span></p>
<p class="ydpe134bc72yiv2685388014MsoNormal" style="margin:0cm 0cm 0.0001pt;font-family:Calibri, sans-serif;color:rgb(0, 0, 0);">
<span style="color:rgb(127, 127, 127);"> </span></p>
<p class="ydpe134bc72yiv2685388014MsoNormal" style="margin:0cm 0cm 0.0001pt;font-family:Calibri, sans-serif;color:rgb(0, 0, 0);">
<span style="color:rgb(127, 127, 127);">Handelsregister: Amtsgericht Braunschweig, HRB 203557</span></p>
<p class="ydpe134bc72yiv2685388014MsoNormal" style="margin:0cm 0cm 0.0001pt;font-family:Calibri, sans-serif;color:rgb(0, 0, 0);">
<span style="color:rgb(127, 127, 127);">Geschäftsführer: Sebastian Schier</span></p>
</div>
</div></div><div class="ydpe134bc72yqt9179950694" id="ydpe134bc72yqt50593">_______________________________________________<br clear="none">pgpool-general mailing list<br clear="none"><a shape="rect" href="mailto:pgpool-general@pgpool.net" rel="nofollow" target="_blank">pgpool-general@pgpool.net</a><br clear="none"><a shape="rect" href="http://www.pgpool.net/mailman/listinfo/pgpool-general" rel="nofollow" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br clear="none"></div></div></div></div></div></body></html>