From: Thomas Kellerer <spam_eater@gmx.net>
To: pgsql-admin@postgresql.org
Subject: Re: Per thread Connection memory
Date: Mon, 1 Feb 2016 12:57:40 +0100
Message-ID: <n8nh7k$hr9$1@ger.gmane.org> (raw)
In-Reply-To: <CALXoLqzk5CkZq=tKvv=W+ugc43gr6Ug-ELo9HnjM9pxJ0Pm-FQ@mail.gmail.com>
References: <CALXoLqxVbxJy8DKqmACkyE_UFZpuL0L38102S-vM+6=eQ+FHng@mail.gmail.com>
<CAFe9ZTqe3_SqnCYbWhQe9Ve+mw+V+R=ksdr-r9DASZ4NSQu40Q@mail.gmail.com>
<CALXoLqzk5CkZq=tKvv=W+ugc43gr6Ug-ELo9HnjM9pxJ0Pm-FQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
> Application Using Java Program , They have there java Pool system,
> when this Java pool memory full , tomcat need to restart in every 30- 60 min
You should configure your connection pool in the Java application to _release_ those connections
e.g. if you are using the Tomcat 7 pool, I would configure something like maxIdle="10", minIdle="5" and maxActive="150" and then you need to define a timeout when an idle connection is closed by the pool, e.g. minEvictableIdleTimeMillis="300000" to close an idle connection after 5 minutes.
Depending on your workload you might want to close that faster (e.g. after 1 minute).
> Most queries found in processlist is which full the Java memory pool , Hibernate is used in Application
Those queries look suspiciously like the query that is used by DatabaseMetaData.getTables().
I think the c3p0 connection pool has a pretty stupid default configuration where the validation query (that is used to check if a connection is still "alive") uses exactly that call. And I also think this is configured to be called each time a connection is returned to the pool - which imposes a huge (and unnecessary load) on the database server.
If that is the case you should configure a different validation query that is less invasive. Something like: preferredTestQuery="select 42", once that is configure c3p0 will no longer call the (expensive) DatabaseMetaData.getTables() method. It's one of the many reasons I don't like the c3p0 pool. I think Tomcat7's pool is a much better choice (I'm _not_ talking about DBCP, but about the new "native" pool introduced with Tomcat7)
I also recommend to _not_ test the connections when the pools hands them out or if they are returned, because those validation query will then put too much load on the database server.
It's better to configure the pool to validate the connections when they are idle. Again, how you configure this, depends on the pool you are using.
> Please Let me know in Postgresql pgbouncer is essential use for handling multithread applications?
No, if you already have a connection pool in your application, then you don't need another one.
pgBouncer (or pgPool) are usually used for either load balancing or if the applications themselves don't do pooling.
Thomas
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: spam_eater@gmx.net
Subject: Re: Per thread Connection memory
In-Reply-To: <n8nh7k$hr9$1@ger.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox