Re: PostgreSQL db

Kevin thank you very much for your advice, they are helps me a lot.

2014-06-20 21:54 GMT+04:00 Kevin Grittner <kgrittn@ymail.com>:
> Евгений Селявка <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



--
Best Regards,
Seliavka Evgenii