Re: tomcat connection pool per database (postgres)

[Rob Sargent]

For those scoring at home, I'm going to try managing pool-per-user 
myself for the potential jmx gain and as I said, two will be amazing 
success.



On 11/25/20 9:40 AM, Christopher Schultz wrote:

Phil and Rob,

On 11/24/20 11:26, Phil Steitz wrote:

On 11/24/20 8:52 AM, Rob Sargent wrote:
Perhaps I read too much into the description of "The tomcat JDBC 
Connection Pool" page?


TheJDBC Connection Pool|org.apache.tomcat.jdbc.pool|is a replacement 
or an alternative to theApache Commons DBCP 
connection pool.


I reacted to the "replacement" bit. Are both equally sound, 
supported, surviving?


Yes.  I can't speak for jdbc-pool, but it looks like it is being 
actively maintained.


We really need to change the language on that page. It should really 
say something like "Back in the day, the Tomcat team (in retrospect) 
overreacted to the limitations of DBCP and decided to build a new 
high-performance pool. These days, the newer DBCP2 is great, and 
jdbc-pool also exists. They are two reasonably equivalent ways to 
solve the same problem. The default is DBCP and it's fine. Switching 
to jdbc-pool is also fine; it does have a few minor features that you 
can't (currently) get from jdbc-pool, and you will know right away if 
you actually need them."



The tomcat website text has never been updated to reflect this.


Sorry about that. The current text says "Note that this does not apply 
to Commons DBCP 2.x." in a few places. It's obviously very partisan 
when it doesn't need to be.


-chris

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: tomcat connection pool per database (postgres)

[Christopher Schultz]

Phil and Rob,

On 11/24/20 11:26, Phil Steitz wrote:

On 11/24/20 8:52 AM, Rob Sargent wrote:
Perhaps I read too much into the description of "The tomcat JDBC 
Connection Pool" page?


TheJDBC Connection Pool|org.apache.tomcat.jdbc.pool|is a replacement 
or an alternative to theApache Commons DBCP 
connection pool.


I reacted to the "replacement" bit. Are both equally sound, supported, 
surviving?


Yes.  I can't speak for jdbc-pool, but it looks like it is being 
actively maintained.


We really need to change the language on that page. It should really say 
something like "Back in the day, the Tomcat team (in retrospect) 
overreacted to the limitations of DBCP and decided to build a new 
high-performance pool. These days, the newer DBCP2 is great, and 
jdbc-pool also exists. They are two reasonably equivalent ways to solve 
the same problem. The default is DBCP and it's fine. Switching to 
jdbc-pool is also fine; it does have a few minor features that you can't 
(currently) get from jdbc-pool, and you will know right away if you 
actually need them."


The tomcat website text has never been 
updated to reflect this.


Sorry about that. The current text says "Note that this does not apply 
to Commons DBCP 2.x." in a few places. It's obviously very partisan when 
it doesn't need to be.


-chris

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: tomcat connection pool per database (postgres)

[Mark Thomas]

On 24/11/2020 16:26, Phil Steitz wrote:
> 
> 
> On 11/24/20 8:52 AM, Rob Sargent wrote:
>> Perhaps I read too much into the description of "The tomcat JDBC
>> Connection Pool" page?
>>
>> TheJDBC Connection Pool|org.apache.tomcat.jdbc.pool|is a replacement
>> or an alternative to theApache Commons DBCP
>> connection pool.
>>
>> I reacted to the "replacement" bit. Are both equally sound, supported,
>> surviving?
> 
> Yes.  I can't speak for jdbc-pool, but it looks like it is being
> actively maintained.  I can confirm that Commons DBCP is being
> maintained.  A repackaged, slightly stripped down version of DBCP is the
> default pool that ships with tomcat.  I am not sure if the
> PerUserPoolDataSource is included in the tomcat distro, but you can just
> use DBCP directly to get this if you want to use it.
> 
> Others who know dbcp-pool better can chime in, but I think the
> difference between the two is that DBCP has more features (including,
> for example the DS above), but those features come at the expense of a
> larger code base, dependency on Commons Pool (another widely used,
> pretty well-maintained library) and slightly worse performance.  When
> jdbc-pool was first introduced, the performance gap, and some nasty bugs
> in DBCP 1.x made the former a better choice for high-concurrency
> applications.  With DBCP 2, the gap has narrowed to the point where it
> is not practically significant.  The tomcat website text has never been
> updated to reflect this.

There is some useful (although not necessarily from a neutral viewpoint)
history in this thread:
https://markmail.org/message/nhayhdcstkj2lssf

One point that thread doesn't address is that jdbc-pool has better JMX
monitoring than DBCP which is a large part of why it is still around.

Mark

> 
> Phil
>> I try to use what I think is the safest longer term bet (my retirement
>> is nigh, it shouldn't take the code with it :) )
>>
>>
>> On 11/24/20 8:28 AM, Phil Steitz wrote:
>>>
>>> On 11/24/20 8:14 AM, Rob Sargent wrote:
 Thanks. I get it. But...

 - seems this solution raises the footprint of the pooler, with
 number-of-users * minimum-connection-count etc

 - would it be beyond the pale for the pooler to maintain
 username-connectionList maps?
>>>
>>> Per response elsethread, see PerUserPoolDataSource [1] provided by
>>> Commons DBCP.  It does that.
>>>
>>> Phil
>>>
>>> [1] https://s.apache.org/dlghr 
>>>

 Thankfully, I'll be wildly successful if I have two concurrent users
 (a user may have hundreds of clients needing db connection)

 (rant.  The RDBMSs really should have a more lightweight way of
 changing current user.  (e.g. postgres set role doesn't cut it,
 doesn't even invoke the users default search path))

 Thanks again, I appreciate the feedback and knowledge sharing


 On 11/24/20 6:28 AM, Christopher Schultz wrote:
> Rob,
>
> On 11/19/20 12:38, Rob Sargent wrote:
>> Since the connection URL names a specific postgres database is it
>> standard practice to have a pool per target database?  (Switching
>> databases in postgres amounts to closing/opening a connection.)
>
> I generally consider a database connection pool to be a connection
> to a certain database/tablespace/schema, not a connection to an IP
> address. That's the only thing that would make sense in terms of an
> application, which would expect a connection to a specific data
> store, right?
>
> If you are closing connections (and reopening them), the pool isn't
> dong its job.
>
> I would recommend a separate pool per database/tablespace/schema.
>
> -chris
>
> -
> To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
> For additional commands, e-mail: users-h...@tomcat.apache.org
>

>>>
>>
> 
> 
> -
> To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
> For additional commands, e-mail: users-h...@tomcat.apache.org
> 


-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: tomcat connection pool per database (postgres)

[Phil Steitz]

On 11/24/20 8:52 AM, Rob Sargent wrote:
Perhaps I read too much into the description of "The tomcat JDBC 
Connection Pool" page?


TheJDBC Connection Pool|org.apache.tomcat.jdbc.pool|is a replacement 
or an alternative to theApache Commons DBCP 
connection pool.


I reacted to the "replacement" bit. Are both equally sound, supported, 
surviving?


Yes.  I can't speak for jdbc-pool, but it looks like it is being 
actively maintained.  I can confirm that Commons DBCP is being 
maintained.  A repackaged, slightly stripped down version of DBCP is the 
default pool that ships with tomcat.  I am not sure if the 
PerUserPoolDataSource is included in the tomcat distro, but you can just 
use DBCP directly to get this if you want to use it.


Others who know dbcp-pool better can chime in, but I think the 
difference between the two is that DBCP has more features (including, 
for example the DS above), but those features come at the expense of a 
larger code base, dependency on Commons Pool (another widely used, 
pretty well-maintained library) and slightly worse performance.  When 
jdbc-pool was first introduced, the performance gap, and some nasty bugs 
in DBCP 1.x made the former a better choice for high-concurrency 
applications.  With DBCP 2, the gap has narrowed to the point where it 
is not practically significant.  The tomcat website text has never been 
updated to reflect this.


Phil
I try to use what I think is the safest longer term bet (my retirement 
is nigh, it shouldn't take the code with it :) )



On 11/24/20 8:28 AM, Phil Steitz wrote:


On 11/24/20 8:14 AM, Rob Sargent wrote:

Thanks. I get it. But...

- seems this solution raises the footprint of the pooler, with 
number-of-users * minimum-connection-count etc


- would it be beyond the pale for the pooler to maintain 
username-connectionList maps?


Per response elsethread, see PerUserPoolDataSource [1] provided by 
Commons DBCP.  It does that.


Phil

[1] https://s.apache.org/dlghr 



Thankfully, I'll be wildly successful if I have two concurrent users 
(a user may have hundreds of clients needing db connection)


(rant.  The RDBMSs really should have a more lightweight way of 
changing current user.  (e.g. postgres set role doesn't cut it, 
doesn't even invoke the users default search path))


Thanks again, I appreciate the feedback and knowledge sharing


On 11/24/20 6:28 AM, Christopher Schultz wrote:

Rob,

On 11/19/20 12:38, Rob Sargent wrote:
Since the connection URL names a specific postgres database is it 
standard practice to have a pool per target database?  (Switching 
databases in postgres amounts to closing/opening a connection.)


I generally consider a database connection pool to be a connection 
to a certain database/tablespace/schema, not a connection to an IP 
address. That's the only thing that would make sense in terms of an 
application, which would expect a connection to a specific data 
store, right?


If you are closing connections (and reopening them), the pool isn't 
dong its job.


I would recommend a separate pool per database/tablespace/schema.

-chris

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org










-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: tomcat connection pool per database (postgres)

[Rob Sargent]

Perhaps I read too much into the description of "The tomcat JDBC 
Connection Pool" page?


TheJDBC Connection Pool|org.apache.tomcat.jdbc.pool|is a replacement or 
an alternative to theApache Commons DBCP 
connection pool.


I reacted to the "replacement" bit. Are both equally sound, supported, 
surviving?  I try to use what I think is the safest longer term bet (my 
retirement is nigh, it shouldn't take the code with it :) )



On 11/24/20 8:28 AM, Phil Steitz wrote:


On 11/24/20 8:14 AM, Rob Sargent wrote:

Thanks. I get it. But...

- seems this solution raises the footprint of the pooler, with 
number-of-users * minimum-connection-count etc


- would it be beyond the pale for the pooler to maintain 
username-connectionList maps?


Per response elsethread, see PerUserPoolDataSource [1] provided by 
Commons DBCP.  It does that.


Phil

[1] https://s.apache.org/dlghr 



Thankfully, I'll be wildly successful if I have two concurrent users 
(a user may have hundreds of clients needing db connection)


(rant.  The RDBMSs really should have a more lightweight way of 
changing current user.  (e.g. postgres set role doesn't cut it, 
doesn't even invoke the users default search path))


Thanks again, I appreciate the feedback and knowledge sharing


On 11/24/20 6:28 AM, Christopher Schultz wrote:

Rob,

On 11/19/20 12:38, Rob Sargent wrote:
Since the connection URL names a specific postgres database is it 
standard practice to have a pool per target database?  (Switching 
databases in postgres amounts to closing/opening a connection.)


I generally consider a database connection pool to be a connection 
to a certain database/tablespace/schema, not a connection to an IP 
address. That's the only thing that would make sense in terms of an 
application, which would expect a connection to a specific data 
store, right?


If you are closing connections (and reopening them), the pool isn't 
dong its job.


I would recommend a separate pool per database/tablespace/schema.

-chris

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: tomcat connection pool per database (postgres)

[Phil Steitz]

On 11/24/20 8:14 AM, Rob Sargent wrote:

Thanks. I get it. But...

- seems this solution raises the footprint of the pooler, with 
number-of-users * minimum-connection-count etc


- would it be beyond the pale for the pooler to maintain 
username-connectionList maps?


Per response elsethread, see PerUserPoolDataSource [1] provided by 
Commons DBCP.  It does that.


Phil

[1] https://s.apache.org/dlghr 



Thankfully, I'll be wildly successful if I have two concurrent users 
(a user may have hundreds of clients needing db connection)


(rant.  The RDBMSs really should have a more lightweight way of 
changing current user.  (e.g. postgres set role doesn't cut it, 
doesn't even invoke the users default search path))


Thanks again, I appreciate the feedback and knowledge sharing


On 11/24/20 6:28 AM, Christopher Schultz wrote:

Rob,

On 11/19/20 12:38, Rob Sargent wrote:
Since the connection URL names a specific postgres database is it 
standard practice to have a pool per target database?  (Switching 
databases in postgres amounts to closing/opening a connection.)


I generally consider a database connection pool to be a connection to 
a certain database/tablespace/schema, not a connection to an IP 
address. That's the only thing that would make sense in terms of an 
application, which would expect a connection to a specific data 
store, right?


If you are closing connections (and reopening them), the pool isn't 
dong its job.


I would recommend a separate pool per database/tablespace/schema.

-chris

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: tomcat connection pool per database (postgres)

[Rob Sargent]

Thanks.  I get it. But...

- seems this solution raises the footprint of the pooler, with 
number-of-users * minimum-connection-count etc


- would it be beyond the pale for the pooler to maintain 
username-connectionList maps?


Thankfully, I'll be wildly successful if I have two concurrent users (a 
user may have hundreds of clients needing db connection)


(rant.  The RDBMSs really should have a more lightweight way of changing 
current user.  (e.g. postgres set role doesn't cut it, doesn't even 
invoke the users default search path))


Thanks again, I appreciate the feedback and knowledge sharing


On 11/24/20 6:28 AM, Christopher Schultz wrote:

Rob,

On 11/19/20 12:38, Rob Sargent wrote:
Since the connection URL names a specific postgres database is it 
standard practice to have a pool per target database?  (Switching 
databases in postgres amounts to closing/opening a connection.)


I generally consider a database connection pool to be a connection to 
a certain database/tablespace/schema, not a connection to an IP 
address. That's the only thing that would make sense in terms of an 
application, which would expect a connection to a specific data store, 
right?


If you are closing connections (and reopening them), the pool isn't 
dong its job.


I would recommend a separate pool per database/tablespace/schema.

-chris

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: tomcat connection pool per database (postgres)

[Christopher Schultz]

Rob,

On 11/19/20 12:38, Rob Sargent wrote:
Since the connection URL names a specific postgres database is it 
standard practice to have a pool per target database?  (Switching 
databases in postgres amounts to closing/opening a connection.)


I generally consider a database connection pool to be a connection to a 
certain database/tablespace/schema, not a connection to an IP address. 
That's the only thing that would make sense in terms of an application, 
which would expect a connection to a specific data store, right?


If you are closing connections (and reopening them), the pool isn't dong 
its job.


I would recommend a separate pool per database/tablespace/schema.

-chris

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org