tomcat connection pool per database (postgres)

[Rob Sargent]

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.)

[Christopher Schultz-2]

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: [hidden email]
For additional commands, e-mail: [hidden email]

[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: [hidden email]
> For additional commands, e-mail: [hidden email]
>

[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 <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: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
>

[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
<https://commons.apache.org/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 <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: [hidden email]
>>> For additional commands, e-mail: [hidden email]
>>>
>>
>

[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
> <https://commons.apache.org/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 <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: [hidden email]
>>>> For additional commands, e-mail: [hidden email]
>>>>
>>>
>>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

[markt]

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
>> <https://commons.apache.org/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 <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: [hidden email]
>>>>> For additional commands, e-mail: [hidden email]
>>>>>
>>>>
>>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

[Christopher Schultz-2]

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
>> <https://commons.apache.org/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: [hidden email]
For additional commands, e-mail: [hidden email]

[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
>>> <https://commons.apache.org/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: [hidden email]
> For additional commands, e-mail: [hidden email]
>