tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Alexander Hartner <a...@j2anywhere.com>
Subject Re: Active DataSource Connection count never decreasing with PostgreSQL
Date Wed, 03 Feb 2010 00:06:14 GMT
Hi Christopher,

Thanks for your suggestion.


On 2 Feb 2010, at 22:48, Christopher Schultz wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> Alexander,
> 
> On 2/2/2010 2:44 PM, Alexander Hartner wrote:
>> I have developed an application which accesses a database via
>> Hibernate as well as directly via JDBC (datasource). The datasource is
>> defined in the server.xml file as follows:
>> 
>>    <Context path="" docBase="AddressBookServer.war">
> 
> :(
> 
> Put this into either META-INF/context.xml within your WAR/webapp
> directory, or into CATALINA_BASE/Catalina/[hostname]/ROOT.xml
> 
> Remove the "path" and "docBase" attributes when you move the <Context>
> element.

I really like it that way as it allows me to configure what I need to in one central place
without having to touch or expand the WAR file.

> 
>>          <Environment name="LicenseFile" value="Licensekey.license" type="java.lang.String"
override="true"/>         
>>          <Environment name="BackupsFolder" value="../backups" type="java.lang.String"
override="true"/>       
>> 
>>          <Resource name="jdbc/AddressBookDB" auth="Container" type="javax.sql.DataSource"
>>             maxActive="25" maxIdle="5" maxWait="-1" removeAbandoned="true" removeAbandonedTimeout="60"
logAbandoned="true"
>>             username="sa" password="" driverClassName="org.h2.Driver"
>>             url="jdbc:h2:tcp://localhost:28081/../db/AddressBookDB;LOCK_TIMEOUT=60000"/>
> 
> That URL looks fishy, with ".." in its name. It that a relative disk
> path? If so, you're asking for deployment headaches in the future.

This also works great for me. My database folder is relative the the startup script. Really
don't want my database in the bin folder of tomcat, but ../db works just great.

> 
>>          <Realm className="org.apache.catalina.realm.JDBCRealm"
>>            driverName="org.h2.Driver"
>>            connectionURL="jdbc:h2:tcp://localhost:28081/../db/AddressBookDB;LOCK_TIMEOUT=60000"
>>            connectionName="sa" connectionPassword=""
>>            userTable="WEBUSERS" userNameCol="USERID" userCredCol="PASSWORD"
>>            userRoleTable="WEBROLES" roleNameCol="ACCESSROLE"/>
> 
> Consider using DataSourceRealm instead of JDBCRealm: JDBCRealm uses a
> single Connection object (thereby serializing logins) and has had a
> bunch of concurrency issues in the past. DataSourceRealm can use your
> previously-configured DataSource for Connections. Or, you can define an
> authentication-only DataSource that is used only by the DataSourceRealm.

I didn't know about DataSourceRealm and will try that. Thanks for this suggestion.

> 
>> By default I use an embedded H2 database and everything works great.
>> The numActive count goes up during activity and decreases to 0 when
>> there is no activity. Also the count generally matches the actual
>> connections to the database as one would expect. If I use the same
>> application with PostgreSQL 8.4 however, the numActive count goes up and
>> up, and only sometimes down. Generally sitting around 25 even though
>> PostgreSQL only reports 4 idle connection.
>> 
>> I already tried adding the following to my datasource configuration,
>> however this did not make any difference so far.
>> removeAbandoned="true" removeAbandonedTimeout="60"
>> logAbandoned="true"
> 
> Did you look at your log files after setting these parameters?

Yes, I saw the initial messages, but nothing since.

> 
>> I am using Tomcat 6.0.18 on OS X 10.6.2 with Java 1.6.
>> 
>> Any suggestion on how I can debug this further why Tomcat reports an
>> incorrect numActive count and eventually runs out of possible
>> connections. Currently it has to be restarted several times a day with
>> PostgreSQL.
> 
> While there are other possibilities, you might want to read this:
> http://blog.christopherschultz.net/index.php/2009/03/16/properly-handling-pooled-jdbc-connections/
> 
> I would /highly/ recommend running with maxActive="1" in all
> environments except for production, as you will likely trigger any
> Connection leaks much more quickly that way.

Not really an option since this is on a production server. On my test system using H2 I am
not observing any issues.  Currently on the production environment using PostgreSQL I have
numActive = 13, numIdle =1  in jconsole.

However using the following query I see that 16 connections are all idle.
SELECT datname,usename,procpid,client_addr,waiting,query_start,current_query FROM pg_stat_activity
;
Thanks for your help so far. I will read the article and let you know what I find.

> 
> - -chris
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (MingW32)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
> 
> iEYEARECAAYFAktoq7QACgkQ9CaO5/Lv0PCUzwCgrDZffO+r8bHFsYLbwelii0mC
> rlgAoIfO6KH1IDzEZawRyHyW7+gKRdnD
> =zA4A
> -----END PGP SIGNATURE-----
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
> 

Regards
Alex


Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message