Re: PostgreSQL db - Mailing list pgsql-admin

Евгений Селявка <evg.selyavka@gmail.com> wrote:

> Hi, Kevin
> I want to ask you about experience with java and dbcp pool, could
> you describe this case in detail.

Well, the pooler I have the most experience with is one that I
designed and wrote under Java 1.0 for Wisconsin Courts.  It
continued to work well for us until the time I left, about 16 years
later, with minimal adjustments to take advantage of newer
capabilities in later versions of Java as they came along.  But
before I left they were looking at reworking their software stack
using packages from outside, and I reviewed various Java-based
poolers to see how well I thought they would work.  IMO, dbcp was
the best of the lot that I reviewed.

> I have one database and several app each of this app have own
> datasource, which is a dbcp pool object. Maybe it is good
> practice to use pgbouncer before dbcp data sources(but my opinion
> it is not good practice to have 2 pool before database), or i
> should calculate all dbcp datasources from all app with formula
> sum(maxActive)=max connection - 3.

The goal is to try to limit the total number of active database
transactions to the number that the database can handle
efficiently, or as close as you can get to that.  When you have
multiple pools in parallel, you need to add the maximum database
connections from all of them.  I would try to avoid a second layer
of pooling, but if you can't limit the numbers and sizes of the
Java pools enough to prevent performance problems due to
concurrency, putting pgbouncer in front of the server would not be
crazy.

> And what about another java pool realization, like HikariCP, do
> you try them?

That is not one I reviewed.

> This is my sample property file for dbcp:

I don't remember all the settings and what they do, but the goal
would be to queue requests to start a transaction when a pool was
already at its maximum.  My recollection is that this is what dbcp
does by default.  Well, one thing to look at: I might try setting
initial and maximum pool sizes the same, to avoid a lot of
connection opening and closing during shifts in load.  Logging
connections and disconnections will help you spot thrashing of that
sort.

The best thing is to try incremental changes to the sizing of the
pool with production load with your actual application, and closely
monitor results.  I know I was surprised by how much reducing the
pool size improved performance of our application and allowed it to
scale better with thousands of users.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company