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.) |
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] |
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] > |
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] >> > |
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] >>> >> > |
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] |
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] |
In reply to this post by Phil Steitz
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] |
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] > |
Free forum by Nabble | Edit this page |