<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body>
<div>Hi Pierre,</div>
<div><br>
</div>
<div>thanks for the input. The problem is indeed that the activiti engine doesn't seem to close the connections properly:</div>
<div><br>
</div>
<div>[<a href="mailto:service@db1">service@db1</a> ~]$ psql -h XXX.XXX.XXX.XXX -p 9999 -U postgres -c "select datname, count(*) from pg_stat_activity where datname != 'postgres' group by datname order by datname";</div>
<div> datname | count </div>
<div>------------------------------+-------</div>
<div> activiti_xxx_production | 128</div>
<div> activiti_xxx_qa | 14</div>
<div> activiti_yyy_production | 218</div>
<div> activiti_yyy_qa | 10</div>
<div> activiti_zzz_production | 16</div>
<div> activiti_zzz_qa | 18</div>
<div> app_yyy_production | 14</div>
<div> app_yyy_qa | 8</div>
<div> app_xxx_production | 17</div>
<div> app_xxx_qa | 6</div>
<div> app_zzz_production | 8</div>
<div> app_zzz_qa | 8</div>
<div>(12 rows)</div>
<div></div>
<div><br>
</div>
<div><br>
</div>
<div>What do you suggest for client_idle_limit? Is 30 too aggressive?</div>
<div><br>
</div>
<div>Meanwhile, I will talk to the developers about the issue.</div>
<div><br>
</div>
<div>Thomas</div>
<div><br>
</div>
<div><br>
</div>
<div>On Wed, 2017-06-07 at 07:20 +0000, Pierre Timmermans wrote:</div>
<blockquote type="cite">
<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>
</blockquote>
<div><span>
<pre>-- <br></pre>
<p class="MsoNormal" 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<o:p></o:p></span></b></p>
<p class="MsoNormal" 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><o:p></o:p></p>
<p class="MsoNormal" 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);"><o:p> </o:p></span></b></p>
<p class="MsoNormal" 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);"><o:p></o:p></span></p>
<p class="MsoNormal" 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<o:p></o:p></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-family: Calibri, sans-serif; color: rgb(0, 0, 0);">
<span style="color: rgb(127, 127, 127);"><o:p> </o:p></span></p>
<p class="MsoNormal" 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<o:p></o:p></span></p>
<p class="MsoNormal" 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="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-family: Calibri, sans-serif; color: rgb(0, 0, 0);">
<span style="color: gray;"><a href="mailto:christoph.steindorff@neos-it.de" style="color: purple;"><span style="color: gray;">mailto:thomas.hoell@neos-it.de</span></a><o:p></o:p></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-family: Calibri, sans-serif; color: rgb(0, 0, 0);">
<span style="color: gray;"><a href="http://www.neos-it.de/" style="color: purple;"><span style="color: gray;">http://www.neos-it.de/</span></a><o:p></o:p></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-family: Calibri, sans-serif; color: rgb(0, 0, 0);">
<span style="color: gray;"><a href="http://www.facebook.com/neositgmbh" style="color: purple;"><span style="color: gray;">http://www.facebook.com/neositgmbh</span></a><o:p></o:p></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0.0001pt; font-family: Calibri, sans-serif; color: rgb(0, 0, 0);">
<span style="color: rgb(127, 127, 127);"><o:p> </o:p></span></p>
<p class="MsoNormal" 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<o:p></o:p></span></p>
<p class="MsoNormal" 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>
</span></div>
</body>
</html>