<div dir="ltr"><div class="gmail_quote">On Thu, Jun 28, 2012 at 6:38 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">
<div><div class="h5">
> connections to a database whose name is not in one of 'postgres',<br>
> 'template0', 'template1', or 'regression'.<br>
><br>
> This appears to be because of the following piece of code from child.c:<br>
><br>
> /*<br>
> * do not cache connection if:<br>
> * pool_config->connection_cahe == 0 or<br>
> * database name is template0, template1, postgres or regression<br>
> */<br>
> if (pool_config->connection_cache == 0 ||<br>
> !strcmp(sp->database, "template0") ||<br>
> !strcmp(sp->database, "template1") ||<br>
> !strcmp(sp->database, "postgres") ||<br>
> !strcmp(sp->database, "regression"))<br>
> {<br>
><br>
> So I see two problems with this.<br>
><br>
> One, these databases will never see the benefit of connection caching.<br>
> Since most pgpool users would be using the default 'postgres' database,<br>
<br>
</div></div>> I have noticed a bug that rears its head only when the client makes<br>I don't believe this and have never heard about such a user in my<br>
customers who is using "postgres" database to store mission critical<br>
data. Any serious database users will never use "postgres" for their<br>
real database. The postgres database should be soly used for<br>
PostgreSQL internal use (for example autovacuum) or database<br>
administration tool like createdb.<br></blockquote><div><br>I have to disagree! postgres is the default database a user is supposed to connect to and start working with. Serious or not a serious application is besides the point. This is a non-system database, ulike template0|1 databases, and hence should be treated as such. And I'd apply the same argument to remove 'regression' database too from that list.<br>
</div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div class="im"><br>
> this seems to be a bad decision.<br>
> Second, the client connections that used a database not in this list, seem<br>
> not to be able to reuse the cached connections unless the connections held<br>
> by pgpool come close the the max_connection parameter in postgres.<br>
><br>
> Here's how to reproduce this:<br>
><br>
> export PGHOST=localhost PGPORT=9999 PGDATABASE=some_other_db PGUSER=postgres<br>
><br>
> while sleep 1; do psql "select 1"; done<br>
><br>
> This causes the number of connections on postgres side to steadily<br>
> increase, even though the client app disconnects before requesting a new<br>
> connection.<br>
><br>
> If I change the PGPORT to 5432, then the number of connections stays stable<br>
> at 1. And if I change the PGDATABASE to 'postgres' (keeping PGPORT=9999),<br>
> then also the number of connections on postgres stay stable.<br>
><br>
> The first problem of treating some databases might be acceptable to some,<br>
> but the inability to reuse a previously released connection troubles me. To<br>
> be specific, this causes the c3p0 connection pooler (which wants to keep<br>
> just 5 connections) to cause a connection spike on postgres, and hence<br>
> those postgres backends are not usable by any other application.<br>
<br>
</div>Probably you'd better to look into our FAQ.<br>
Especially this:<br>
"Is connection pool cache shared among pgpool process?"<br></blockquote></div><br>Thanks, I didn't know this was by design.<br><br>For this case I think reducing child_life_time, to say 10 seconds, would help the cached connection be destroyed sooner, so that the total number of connections on the DB side stay low.<br clear="all">
<br>Best regards,<br>-- <br><div dir="ltr">Gurjeet Singh<br>EnterpriseDB Corporation<br>The Enterprise PostgreSQL Company<br></div><br>
</div>