On Nov 19, 2013, at 8:32 AM, Carl Boberg <carl.bob...@memnonnetworks.com> wrote:
> Thanks for taking the time Daniel,
>
> It is very hard to explain the problem since, and it was also stupid of me
> to not include the fact that I have tried all kinds of similar combinations
> of configuration in context.xml. With botch dbcp and jdbc pools
> The behaviour persists. For example these are one version of config. I have
> tested.
>
> DBCP: behaviour is absent and all i well
> <Resource name="database1"
> auth="Container"
> maxActive="50"
> maxIdle="8"
> minIdle="2"
> initialSize="0"
> username="'''''"
> password="'''''"
> driverClassName="com.inet.tds.TdsDriver"
> type="javax.sql.DataSource"
> defaultTransactionIsolation="READ_UNCOMMITTED"
> url="jdbc:inetdae7://devdb12:1433/database1_dev"
> testOnBorrow="true"
> validationQuery="SELECT 1"
> timeBetweenEvictionRunsMillis="10000"
> removeAbandoned="true"
> removeAbandonedTimeout="600"
> maxWait="10000"/>
>
> JDBC: I see the weird behaviour and my DBA is angry
> <Resource name="database1"
> auth="Container"
> maxActive="50"
> maxIdle="10"
> minIdle="2"
> initialSize="0"
> username="'''''"
> password="'''''"
> driverClassName="com.inet.tds.TdsDriver"
> type="javax.sql.DataSource"
> factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
> defaultTransactionIsolation="READ_UNCOMMITTED"
> defaultAutoCommit="true"
> url="jdbc:inetdae7://devdb12:1433/database1_dev"
> testOnBorrow="true"
> validationQuery="SELECT 1"
> timeBetweenEvictionRunsMillis="10000"
> removeAbandoned="true"
> removeAbandonedTimeout="600"
> maxWait="10000"/>
>
>
> The behaviour applies to ALL queries/statements from the application.
>
> I have here an example of the way we close from the application, (the devs
> have named it dispose). From my untrained non java dev eye we do not seem
> to be doing statement.Close(); and Im curious if that might be the issue?
If you open it, you should close it. That goes for Connections, Statements and
ResultSets. If you suspect this might be an issue, you could always run a
static analysis tool, like FindBugs, against your code base. Tools like that
will generally catch this type of problem.
> If so, why does DBCP handle it nicely and not JDBC?
I think Mark has answered this already. Only thing that t I would add is that
jdbc-pol does have a StatementFinalizer interceptor. I'm not sure if you've
tried this already, I didn't look back at your original post. The
documentation for it states that the interceptor "Keeps track of all statements
created using createStatement, prepareStatement or prepareCall and closes these
statements when the connection is returned to the pool.". It doesn't seem as
comprehensive as DBCP, so depending on how you create your statements, it may
or may not be helpful. Also, it doesn't mention anything about ResultSet
tracking, so I don't think it'll help there.
Dan
> public void dispose() {
>
> if (connection != null) {
>
> try {
> if (!connection.isClosed()) {
>
> // If autoCommit is false, we are most likely using
> transactions. A rollback will end the transaction
> // properly even if a pool treats all actual
> connections to the db as single long transactions.
> // Examine the connection directly instead of relying
> on ConnectionManager attribute.
> if (ROLLBACK_ON_CLOSE && !connection.getAutoCommit()) {
> connection.rollback();
> }
>
> // Close the connection
> connection.close();
> if (traceOpenedConnections) {
> timeConnectionClosed = System.currentTimeMillis();
> }
> }
>
> } catch (java.sql.SQLException sqle) {
> sqle.printStackTrace();
> }
>
> this.connection = null;
> }
>
> // Deregister this ConnectionManger
> if (traceOpenedConnections) {
> deregister(this);
> }
> }
>
>
> TNTUnilab delenda est
> ---
> Carl Boberg
> Operations
>
> Memnon Networks AB
> Tegnérgatan 34, SE-113 59 Stockholm
>
> Mobile: +46(0)70 467 27 12
> www.memnonnetworks.com
>
>
> On 18 November 2013 18:23, Daniel Mikusa <dmik...@gopivotal.com> wrote:
>
>> 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
>>
>>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
