On Nov 18, 2013, at 9:48 AM, Carl Boberg <carl.bob...@memnonnetworks.com> wrote:
> Hello, > > We have recently migrated from dbcp pool to the newer tomcat-jdbc pool. As > I understand, it is supposed to be almost a "drop in replacement" for dbcp. *Almost* is the key word here. It's very similar, but there are differences (typically with default values). Most of the time these don't matter, but occasionally you'll bump into them. > Everything works great except once small thing. Its a bit difficult for me > to explain but our DBA is really annoyed by it... > > With the new jdbc configuration, idle connections never really turn into > "normal idle" on the database side. > The DBS looks at the connections on the db side with sp_whoisactive > (excellent improvement of the sp_whoX procedures by Adam Machanic). It > should just show the statement/queries that are actually running. After > that the connection should be "empty" and idle and thus not be visible... > But with the new JDBC pool it shows the last statement/query executed in > the connection and it only changes when a new query comes from the > application (see below)* I therefore know that the connection gets reused > as an idle connection should but this is not the expected behaviour. It > should not "linger" on th db server like it does.. Sorry, I'm not certain what you mean here. I don't use MSSQL, but perhaps someone else does and can comment. > I automatically would assume there is something in the code not closing > statements properly and/or connection leak or some such… Entirely possible. A good test here would be to deploy a trivial application that you know is closing connections properly. If that still exhibits the same behavior then you can rule out the application, plus you'll have a good test application that you can use to replicate the problem. Probably also worth enabling "logAbandoned", so that you can see if you're getting abandoned connections with either configuration. > BUT When using the old dbcp pool configuration all connections behaved as > expected .. > .statement/query get run and the connection turns to a normal idle > connection with no information regarding earlier queries/statements... > therefore it never shows up in sp_whoisactive which is expected. It might help if you can be more specific about what these stored procedures are doing. What are they looking at? What is different about the data that they are looking at for DBCP & tomcat-jdbc? Also if you could correlate this data to what is shown in JMX for the pool, that would help. For example, if the database shows 5 connections with 3 idle, what does the connection pool show in JMX? Does this match? > > For example: rebooting the application an not doing anything (test env. no > users no nothing) if I execute sp_whoisactive I currently see the default > startup query from the app > > * 00 00:31:59.793 333 <?query -- update Account set loggedIn = 0 where > loggedIn = 1 --?> Sleeping > > Now this is a short simple fast query and it shouldn't show here but its > shows as been hanging around for half an hour. It doesn't hang around like > this if I revert to the old dbcp configuration... <- the issue > > Am I missing something fundamental in expected behaviour or should I just > go shout at the devs (who will only say "it worked with the old pool so it > should work the same now")... > > We are using tomcat 6.32 / jdk1.6.0_26 with MS SQL server 2012 > > Old dbcp conf, everything works fully as expected > <Resource name="database1" > auth="Container" > maxActive="50" > maxIdle="8" > username="*****" > password="****" > driverClassName="com.inet.tds.TdsDriver" > type="javax.sql.DataSource" > defaultTransactionIsolation="READ_UNCOMMITTED" > url="jdbc:inetdae7://sqlserver12:1433/database1_dev" > timeBetweenEvictionRunsMillis="300000" > removeAbandoned="true" > removeAbandonedTimeout="600" > maxWait="10000"/> In this config, minIdle is going to be 0. That's the default listed here. https://commons.apache.org/proper/commons-dbcp/configuration.html In the config below, you're setting minIdle to 2. That means your new configuration will always have at least two connections to the database server, whereas the old one could idle down to zero. Some other differences in configuration: - old configuration doesn't specify a validation query - the timeBetweenEvictionRunsMillis is quite different (300000 vs 10000) - remove abandoned timeout is different by a factor of 3 - maxIdle is 8 vs 10 - initial size is 0 vs 2 Probably worth making the configurations as similar as possible. > > JDBC configuration with tomca-jdbc.jar taken from tomcat7.42, has "idle > connections" with statement/query info in them, so that the dba never > really sees them as fully idle > <Resource name="database1" > auth="Container" > maxActive="50" > maxIdle="10" > minIdle="2" > initialSize="2" > username="*****" > password="*****" > driverClassName="com.inet.tds.TdsDriver" > type="javax.sql.DataSource" > factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" > defaultTransactionIsolation="READ_UNCOMMITTED" > jdbcInterceptors="ConnectionState;StatementFinalizer" > defaultAutoCommit="true" > url="jdbc:inetdae7://sqlserver12:1433/database1_dev" > testOnBorrow="true" > validationQuery="SELECT 1" > timeBetweenEvictionRunsMillis="10000" > removeAbandoned="true" > removeAbandonedTimeout="1800" > maxWait="10000"/> Have you tried disabling the interceptors (remove the attribute or set it to "")? That would make the pool more similar to DBCP. Hope that helps! Dan > > I have googled, read documentation, browsed forums but never seen anyone > having an issue like this... and I admit, its nothing major. I'm just very > curious about the source to this difference in behaviour (and can I do > something configuration wise to resolve it?). > > Best regards > --- > Carl Boberg --------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
