[pgpool-hackers: 82] Re: Fwd: pgpool unable to reuse cached connections for non-default databases, and special treatment of default databases

Gurjeet Singh singh.gurjeet at gmail.com
Tue Jul 17 06:32:34 JST 2012


On Thu, Jun 28, 2012 at 6:38 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:

>  > connections to a database whose name is not in one of 'postgres',
> > 'template0', 'template1', or 'regression'.
> >
> > This appears to be because of the following piece of code from child.c:
> >
> >     /*
> >      * do not cache connection if:
> >      * pool_config->connection_cahe == 0 or
> >      * database name is template0, template1, postgres or regression
> >      */
> >     if (pool_config->connection_cache == 0 ||
> >         !strcmp(sp->database, "template0") ||
> >         !strcmp(sp->database, "template1") ||
> >         !strcmp(sp->database, "postgres") ||
> >         !strcmp(sp->database, "regression"))
> >     {
> >
> > So I see two problems with this.
> >
> > One, these databases will never see the benefit of connection caching.
> > Since most pgpool users would be using the default 'postgres' database,
>
> > I have noticed a bug that rears its head only when the client makes
> I don't believe this and have never heard about such a user in my
> customers who is using "postgres" database to store mission critical
> data. Any serious database users will never use "postgres" for their
> real database. The postgres database should be soly used for
> PostgreSQL internal use (for example autovacuum) or database
> administration tool like createdb.
>

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.


>
> > this seems to be a bad decision.
> > Second, the client connections that used a database not in this list,
> seem
> > not to be able to reuse the cached connections unless the connections
> held
> > by pgpool come close the the max_connection parameter in postgres.
> >
> > Here's how to reproduce this:
> >
> > export PGHOST=localhost PGPORT=9999 PGDATABASE=some_other_db
> PGUSER=postgres
> >
> > while sleep 1; do psql "select 1"; done
> >
> > This causes the number of connections on postgres side to steadily
> > increase, even though the client app disconnects before requesting a new
> > connection.
> >
> > If I change the PGPORT to 5432, then the number of connections stays
> stable
> > at 1. And if I change the PGDATABASE to 'postgres' (keeping PGPORT=9999),
> > then also the number of connections on postgres stay stable.
> >
> > The first problem of treating some databases might be acceptable to some,
> > but the inability to reuse a previously released connection troubles me.
> To
> > be specific, this causes the c3p0 connection pooler (which wants to keep
> > just 5 connections) to cause a connection spike on postgres, and hence
> > those postgres backends are not usable by any other application.
>
> Probably you'd better to look into our FAQ.
> Especially this:
> "Is connection pool cache shared among pgpool process?"
>

Thanks, I didn't know this was by design.

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.

Best regards,
-- 
Gurjeet Singh
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20120716/b9e52255/attachment.html>


More information about the pgpool-hackers mailing list