Apache DBCP vs Tomcat JDBC Pool etc.

[wolf]

Hello Isomorphic,

SmartGWT 3.1p v8.3p_2013-04-01/PowerEdition Deployment, Tomcat 7, Oracle 10, FireFox 10

Is there any plan for you guys to upgrade from Apache DBCP 1.2.2, that you have in your package? Also is there any plan to allow another pooling mechanism such as the new Tomcat JDBC Pool (http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html)?

My team is having problems with performance (query speed) once connections in the pool have been up for a couple days. We have tried to use the information here http://www.smartclient.com/smartgwtee-latest/javadoc/com/smartgwt/client/docs/SqlConnectionPooling.html and have tried tweaking

 testWhileIdle, timeBetweenEvictionRunsMillis, minEvictableIdleTimeMillis, numTestsPerEvictionRun

While these settings do what they are supposed to do, they are not solving our problem. The new feature "maxAge" in the Tomcat JDBC Pool seems like it would be perfect to solve the issue we are having. It seems our only other option would be to have a batch job running in Oracle that will kill connections after a certain amount of time. Have any of your other customers been having similar issues, or do have any other insights?

Thanks.

[Isomorphic]

No, we haven't seen anything like a slow degradation over time.
That sounds like a bug in your JDBC driver or in Oracle, for which there may be a patch - we would look there first, as reclaiming connections is just a workaround rather than a real fix.

However, if you ultimately have to look for a workaround, setting a property on the Oracle side to shut down connections periodically seems like the best approach.

[wolf]

Ok, we will look into that.

[wolf]

We are still looking into our issue, but is there any reason you are sticking with an Apache DBCP version from 2007 instead of updating to the latest version or a newer technology?

From my research online and through the Tomcat documentation the 'Tomcat JDBC Pool' seems to have gained favor as a superior pooling method over DBCP with tomcat. Is there any intent to use that product instead? I would assume the majority, or at least a huge portion of your customers use tomcat as their web app server. Additionally, if you could control more pooling options through the server.properties file that would be great.

[Isomorphic]

Tomcat is a minority actually.

We're not looking for a wide variety of possible pooling implementations, we just want one that works well.

Everything you've reported so far suggests that the existing pooling mechanism is working fine but your JDBC driver or DB has a bug.

[wolf]

Ok, we will continue to look at the DB and driver side. Can I ask what is the most popular or couple most popular application servers that your clients use?

[Isomorphic]

WebLogic, WebSphere, JBoss, Glassfish, roughly in that order. There's no single dominant platform really.. and SmartGWT doesn't require any J2EE features (just J2SE) so it's true that these servers are overkill in many deployments. It's just what happens to be already deployed for unrelated reasons.

[bbruyn]

I'm not sure I'm following this, but why wouldn't you just use a JNDI configuration to point at whatever pool you want?

[wolf]

Isomorphic: Thanks for the information.

bbruyn: I am using JNDI that is configured through Tomcat's context.xml. If I understand correctly, SmartGWT is hardcoded to use Apache DBCP and you can't use another pooling mechanism by setting the 'factory' attribute in context.xml. Additionally, if you want to change certain settings (testWhileIdle, timeBetweenEvictionRunsMillis, minEvictableIdleTimeMillis) you have to set them in the server.properties file. Setting these in context.xml will have no impact. Isomorphic, please clarify if I mispoke.

[billbruyn4]

Hrm. No, I don't think so. I trust Isomorphic will correct me if I'm wrong, but IIRC you should be able to disable the default pooling mechanism by setting

sql.pool.enabled=false

in your server.properties (as documented in the SmartGWT doc you linked to) and configuring your JNDI datasource for pooling as you normally would (e.g., as in the Tomcat doc you linked to.)

[Isomorphic]

You can disable the default pooling as bbryun indicated. It's also off by default when using JNDI since JNDI connections typically provide their own pooling.

So the irony here is that you seem to have encountered an issue in the pooling mechanism you were encouraging us to switch to :)

[wolf]

I am following the style as you outlined in your post here:
http://forums.smartclient.com/showpo...88&postcount=3 and have NOT set 'sql.pool.enabled=false'

What pooling mechanism is being used when I follow the config from your post? SmartGWTs? Whatever tomcat defaults to? I can tell the app is creating/using a pool of db connections, not just 1 at a time, from looking at the active/idle connections in oracle.


During some trials I set

sql.Oracle.pool.testWhileIdle: true
sql.Oracle.pool.timeBetweenEvictionRunsMillis: 60000
sql.Oracle.pool.minEvictableIdleTimeMillis: 120000

in server.properties and I could clearly see in the logs that the Evictor was running. I am confused how SmartGWTs pooling is off yet setting these properties still had an effect.

Also, I never encountered an issue with the one I suggested because I assumed it was not possible to switch to it, thus I never tested it.

When reading the documentation on the pooling it states: "SQLDataSource uses DBCP (Apache Commons) pooling, which also compensates for connection closure automatically. This is enabled by default with appropriate settings." which is what I was going by. There is no mention that SQLDataSource doesn't use DBCP when JDNI is being used, maybe this should be clarified in the documentation?

Thank you for the help and posts, but I am still consfused. Sorry if I'm just not getting it.

[Isomorphic]

If you are seeing messages from our pooling system, then you are not using JNDI.

JNDI settings may exist in Tomcat's context.xml, and be available for use, but these won't magically be used - you need to tell SmartGWT to use it if you want to use it - see the Admin Console overview.

[wolf]

I am using (and have been using over the whole course of this conversation) the method described that the bottom of this page.

http://www.smartclient.com/smartgwt/javadoc/com/smartgwt/client/docs/AdminConsole.html

[Isomorphic]

Give your results, it seems that you are not actually using JNDI.

Please review your settings, and if you need more help, post both the complete settings and the server logs that appear when you *first* contact the database.