<div dir="ltr">Hi, <div><br></div><div>we are testing pgpool-II 3.3.3 with Postgresql 9.3 before production. pgpool-II is configured for connection-pooling and failover for Postgresql with streaming replication (master + 4 slaves).</div>
<div>In addition, we are using Postgresql for multi-tenant application. hence we expect connections from hundreds of users.</div>
<div><br></div><div>At some point of time, the database connections started to be rejected. </div><div><br></div><div>Expert from pgpool.conf:</div><div><br></div><div><div><font face="courier new, monospace">#------------------------------------------------------------------------------</font></div>
<div><font face="courier new, monospace"># POOLS</font></div><div><font face="courier new, monospace">#------------------------------------------------------------------------------</font></div><div><font face="courier new, monospace"><br>
</font></div><div><font face="courier new, monospace"># - Pool size -</font></div><div><font face="courier new, monospace"><br></font></div><div><font face="courier new, monospace">num_init_children = 8</font></div><div><font face="courier new, monospace"> # Number of pools</font></div>
<div><font face="courier new, monospace"> # (change requires restart)</font></div><div><font face="courier new, monospace">max_pool = 240</font></div><div><font face="courier new, monospace"> # Number of connections per pool</font></div>
<div><font face="courier new, monospace"> # (change requires restart)</font></div><div><font face="courier new, monospace"><br></font></div><div><font face="courier new, monospace"># - Life time -</font></div>
<div><font face="courier new, monospace"><br></font></div><div><font face="courier new, monospace">child_life_time = 300</font></div><div><font face="courier new, monospace"> # Pool exits after being idle for this many seconds</font></div>
<div><font face="courier new, monospace">child_max_connections = 0</font></div><div><font face="courier new, monospace"> # Pool exits after receiving that many connections</font></div><div>
<font face="courier new, monospace"> # 0 means no exit</font></div><div><font face="courier new, monospace">connection_life_time = 60</font></div><div><font face="courier new, monospace"> # Connection to backend closes after being idle for this many seconds</font></div>
<div><font face="courier new, monospace"> # 0 means no close</font></div><div><font face="courier new, monospace">client_idle_limit = 60</font></div><div><font face="courier new, monospace"> # Client is disconnected after being idle for that many seconds</font></div>
<div><font face="courier new, monospace"> # (even inside an explicit transactions!)</font></div><div><font face="courier new, monospace"> # 0 means no disconnection</font></div>
</div><div><br></div><div>The pgpool processes looked as below :</div><div><br></div><div><p class="MsoNormal"><font face="courier new, monospace" color="#000000"><span lang="EN-US">root 1987 1
0 Mar04 ? 00:00:00 abrt-dump-oops -d
/var/spool/abrt -rwx /var/log/messages</span><span lang="EN-US"></span></font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000"><span lang="EN-US">postgres 10053 1 0 Apr29
? 00:01:00 /usr/bin/pgpool -f
/etc/pgpool-II/pgpool.conf -n</span><span lang="EN-US"></span></font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000"><span lang="EN-US">postgres 10060 10053 0 Apr29
? 00:00:00 pgpool: watchdog</span><span lang="EN-US"></span></font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000"><span lang="EN-US">postgres 10061 10053 0 Apr29
? 00:00:01 pgpool: heartbeat receiver</span><span lang="EN-US"></span></font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000"><span lang="EN-US">postgres 10062 10053 0 Apr29
? 00:00:02 pgpool: heartbeat sender</span><span lang="EN-US"></span></font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000"><span lang="EN-US">postgres 10063 10053 0 Apr29
? 00:00:26 pgpool: lifecheck</span><span lang="EN-US"></span></font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000"><span lang="EN-US">postgres 10072 10053 0 Apr29
? 00:00:00 pgpool: PCP: wait for
connection request</span><span lang="EN-US"></span></font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000"><span lang="EN-US">postgres 10073 10053 0 Apr29
? 00:01:13 pgpool: worker process</span><span lang="EN-US"></span></font></p></div><div><p class="MsoNormal"><font face="courier new, monospace" color="#000000"><span lang="EN-US">postgres 11544 10053 0 08:14
? 00:00:11 pgpool: t_921413 tomr_qa 15.22.23.6(60214) idle</span><span lang="EN-US"></span></font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000"><span lang="EN-US">postgres 11553 10053 0 08:15
? 00:00:11 pgpool: t_921413 tomr_qa 15.22.23.6(60196) idle</span><span lang="EN-US"></span></font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000"><span lang="EN-US">postgres 11554 10053 0 08:15
? 00:00:11 pgpool: t_921413 tomr_qa 15.22.23.6(60206) idle</span><span lang="EN-US"></span></font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000"><span lang="EN-US">postgres 11557 10053 0 08:15
? 00:00:11 pgpool: t_921413 tomr_qa 15.22.23.6(60200) idle</span><span lang="EN-US"></span></font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000"><span lang="EN-US">postgres 11558 10053 0 08:15
? 00:00:11 pgpool: t_921413 tomr_qa 15.22.23.7(49434) idle</span><span lang="EN-US"></span></font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000"><span lang="EN-US">postgres 11559 10053 0 08:15
? 00:00:11 pgpool: t_921413 tomr_qa 15.22.23.6(60210) idle</span><span lang="EN-US"></span></font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000"><span lang="EN-US">postgres 11697 10053 0 08:23
? 00:00:11 pgpool: t_921413 tomr_qa 15.22.23.7(49431) idle</span><span lang="EN-US"></span></font></p>
<p class="MsoNormal"><font color="#000000"><span lang="EN-US"><font face="courier new, monospace">postgres 12708 10053 0 09:26
? 00:00:01 pgpool: t_921413 tomr_qa 15.22.23.7(49427) idle</font></span><span lang="EN-US"></span></font></p></div><div><font color="#000000"><br></font></div><div><font color="#000000"><br></font></div><div><p class="MsoNormal">
<font face="courier new, monospace" color="#000000"><b><span lang="EN-US">pcp_proc_info</span><span lang="EN-US"> 10 pool-vip 9898 pguser XXXXXX 11544</span></b><span lang="EN-US"></span></font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000">toma_qa tom_admin_lab 2014-04-30 08:15:00 2014-04-30 08:24:02 3 0 6 29783 0</font></p><p class="MsoNormal"><font face="courier new, monospace" color="#000000">toma_qa tom_admin_lab 2014-04-30 08:15:00 2014-04-30 08:24:02 3 0 6 26487 0</font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000">toma_qa tom_admin_lab 2014-04-30 08:15:00 2014-04-30 08:24:02 3 0 6 20197 0</font></p><p class="MsoNormal"><font face="courier new, monospace" color="#000000">tomr_qa tom_admin_exec 2014-04-30 08:15:00 2014-04-30 08:27:34 3 0 1767 8784 0</font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000">tomr_qa tom_admin_exec 2014-04-30 08:15:00 2014-04-30 08:27:34 3 0 1767 22090 0</font></p><p class="MsoNormal"><font face="courier new, monospace" color="#000000">tomr_qa tom_admin_exec 2014-04-30 08:15:00 2014-04-30 08:27:34 3 0 1767 29893 0</font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000">tomr_qa tom_admin_exec 2014-04-30 08:15:00 2014-04-30 08:27:34 3 0 1767 26598 0</font></p><p class="MsoNormal"><font face="courier new, monospace" color="#000000">tomr_qa tom_admin_exec 2014-04-30 08:15:00 2014-04-30 08:27:34 3 0 1767 20307 0</font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000">toma_qa t_103886 2014-04-30 08:15:00 2014-04-30 08:33:23 3 0 16 8964 0</font></p><p class="MsoNormal"><font face="courier new, monospace" color="#000000">toma_qa t_103886 2014-04-30 08:15:00 2014-04-30 08:33:23 3 0 16 22270 0</font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000">toma_qa t_103886 2014-04-30 08:15:00 2014-04-30 08:33:23 3 0 16 30072 0</font></p><p class="MsoNormal"><font face="courier new, monospace" color="#000000">toma_qa t_103886 2014-04-30 08:15:00 2014-04-30 08:33:23 3 0 16 26777 0</font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000">toma_qa t_103886 2014-04-30 08:15:00 2014-04-30 08:33:23 3 0 16 20489 0</font></p><p class="MsoNormal"><font face="courier new, monospace" color="#000000">tomr_qa tom_admin_lab 2014-04-30 08:15:00 2014-04-30 08:43:55 3 0 6 9295 0</font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000">tomr_qa tom_admin_lab 2014-04-30 08:15:00 2014-04-30 08:43:55 3 0 6 22600 0</font></p><p class="MsoNormal"><font face="courier new, monospace" color="#000000">tomr_qa tom_admin_lab 2014-04-30 08:15:00 2014-04-30 08:43:55 3 0 6 30402 0</font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000">tomr_qa tom_admin_lab 2014-04-30 08:15:00 2014-04-30 08:43:55 3 0 6 27106 0</font></p><p class="MsoNormal"><font face="courier new, monospace" color="#000000">tomr_qa tom_admin_lab 2014-04-30 08:15:00 2014-04-30 08:43:55 3 0 6 20818 0</font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000"><b>tomr_qa t_921413 2014-04-30 08:15:00 2014-04-30 08:56:37 3 0 340 9695 1</b></font></p><p class="MsoNormal"><font face="courier new, monospace" color="#000000"><b>tomr_qa t_921413 2014-04-30 08:15:00 2014-04-30 08:56:37 3 0 340 23003 1</b></font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000"><b>tomr_qa t_921413 2014-04-30 08:15:00 2014-04-30 08:56:37 3 0 340 30802 1</b></font></p><p class="MsoNormal"><font face="courier new, monospace" color="#000000"><b>tomr_qa t_921413 2014-04-30 08:15:00 2014-04-30 08:56:37 3 0 340 27506 1</b></font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000"><b>tomr_qa t_921413 2014-04-30 08:15:00 2014-04-30 08:56:37 3 0 340 21229 1</b></font></p><p class="MsoNormal"><font face="courier new, monospace" color="#000000"><b>toma_qa t_921413 2014-04-30 08:15:00 2014-04-30 09:13:18 3 0 24 10227 0</b></font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000"><b>toma_qa t_921413 2014-04-30 08:15:00 2014-04-30 09:13:18 3 0 24 23535 0</b></font></p><p class="MsoNormal"><font face="courier new, monospace" color="#000000"><b>toma_qa t_921413 2014-04-30 08:15:00 2014-04-30 09:13:18 3 0 24 31335 0</b></font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000"><b>toma_qa t_921413 2014-04-30 08:15:00 2014-04-30 09:13:18 3 0 24 28038 0</b></font></p><p class="MsoNormal"><font face="courier new, monospace" color="#000000"><b>toma_qa t_921413 2014-04-30 08:15:00 2014-04-30 09:13:18 3 0 24 21760 0</b></font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000"><b><br></b></font></p><p class="MsoNormal"><font face="courier new, monospace" color="#000000"><b><br></b></font></p><p class="MsoNormal"><font color="#000000">In the database we saw several sessions from the same user <b style="font-family:'courier new',monospace">t_921413 in different statuses.</b></font></p>
<p class="MsoNormal"><font color="#000000"><span lang="EN-US"> </span><span lang="EN-US"></span></font></p><p class="MsoNormal"><font face="courier new, monospace" color="#000000">postgres 21218 5469 0 Apr30 ? 00:00:00 postgres: t_921413 tomr_qa 15.22.23.6(33660) UPDATE waiting</font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000">postgres 21223 5469 0 Apr30 ? 00:00:00 postgres: t_921413 tomr_qa 15.22.23.6(33685) UPDATE waiting</font></p><p class="MsoNormal"><font face="courier new, monospace" color="#000000">postgres 21224 5469 0 Apr30 ? 00:00:00 postgres: t_921413 tomr_qa 15.22.23.6(33690) UPDATE waiting</font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000">postgres 21226 5469 0 Apr30 ? 00:00:00 postgres: t_921413 tomr_qa 15.22.23.6(33695) UPDATE waiting</font></p><p class="MsoNormal"><font face="courier new, monospace" color="#000000">postgres 21229 5469 0 Apr30 ? 00:00:00 postgres: t_921413 tomr_qa 15.22.23.6(33705) UPDATE waiting</font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000">postgres 21249 5469 0 Apr30 ? 00:00:00 postgres: t_921413 tomr_qa 15.22.23.6(33755) UPDATE waiting</font></p><p class="MsoNormal"><font face="courier new, monospace" color="#000000">postgres 21250 5469 0 Apr30 ? 00:00:00 postgres: t_921413 tomr_qa 15.22.23.6(33760) UPDATE waiting</font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000">postgres 21758 5469 0 Apr30 ? 00:00:00 postgres: t_921413 toma_qa 15.22.23.6(34759) idle</font></p><p class="MsoNormal"><font face="courier new, monospace" color="#000000">postgres 21759 5469 0 Apr30 ? 00:00:00 postgres: t_921413 toma_qa 15.22.23.6(34760) idle</font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000">postgres 21760 5469 0 Apr30 ? 00:00:00 postgres: t_921413 toma_qa 15.22.23.6(34767) idle</font></p><p class="MsoNormal"><font face="courier new, monospace" color="#000000">postgres 21761 5469 0 Apr30 ? 00:00:00 postgres: t_921413 toma_qa 15.22.23.6(34770) idle</font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000">postgres 21767 5469 0 Apr30 ? 00:00:00 postgres: t_921413 toma_qa 15.22.23.6(34785) idle</font></p><p class="MsoNormal"><font face="courier new, monospace" color="#000000">postgres 21771 5469 0 Apr30 ? 00:00:00 postgres: t_921413 toma_qa 15.22.23.6(34800) idle</font></p>
<p class="MsoNormal"><font face="courier new, monospace" color="#000000">postgres 21772 5469 0 Apr30 ? 00:00:00 postgres: t_921413 toma_qa 15.22.23.6(34805) idle</font></p><p class="MsoNormal"><font face="courier new, monospace" color="#000000">postgres 22161 5469 0 Apr30 ? 00:00:00 postgres: t_921413 tomr_qa 15.22.23.6(35590) UPDATE</font></p>
<p class="MsoNormal"><font face="courier new, monospace"><br></font></p><p class="MsoNormal"><font face="tahoma, sans-serif">The problems we encountered and can't understand what is causing and if there is a fix to it or there is a an error in configuration:</font></p>
<p class="MsoNormal"><font face="tahoma, sans-serif" color="#000000">1. Application server 15.22.23.6 was taken down but pgpool still showed us that front-end is connected (<span lang="EN-US">pcp_proc_info - status 1)</span></font></p>
<p class="MsoNormal"><span lang="EN-US"><font face="tahoma, sans-serif" color="#000000">2. There were several idle cached connections in database that were not closed even after there were idle for much more that 60 seconds.</font></span></p>
<p class="MsoNormal"><font face="tahoma, sans-serif" color="#000000">3. Only after we killed all connections to database of the user t_921413 (in all nodes), other users could continue working</font></p><p class="MsoNormal">
<font face="tahoma, sans-serif" color="#000000"><br></font></p><p class="MsoNormal"><font face="tahoma, sans-serif" color="#000000">Please, advise!</font></p><p class="MsoNormal"><font face="tahoma, sans-serif" color="#000000"><br>
</font></p><p class="MsoNormal"><font face="tahoma, sans-serif" color="#000000">Thank you in advance</font></p><p class="MsoNormal"><font face="tahoma, sans-serif" color="#000000"><br></font></p><p class="MsoNormal"><font face="tahoma, sans-serif" color="#000000">Anna</font></p>
<p class="MsoNormal"><font face="courier new, monospace"><br></font></p><p class="MsoNormal"><font color="#1f497d" face="courier new, monospace"><b><br></b></font></p></div></div>