Hi, we recently switched to tomcat-jdbc-pool as our connection pool, and have since encountered performance problems with Postgresql. The problem is due to the fact that postgres is having hard time vacuuming, due to always ongoing idle transactions. See http://permalink.gmane.org/gmane.comp.db.postgresql.performance/37893 for the details.
It turns out that those idle transactions were coming from tomcat pool validation queries... here is our configuration: <property name="defaultAutoCommit" value="false"/> <property name="maxActive" value="30"/> <property name="maxIdle" value="30"/> <property name="initialSize" value="10"/> <property name="minIdle" value="10"/> <property name="testOnReturn" value="true"/> <property name="testOnBorrow" value="true"/> <property name="validationQuery" value="select 1 from rtelre"/> <property name="validationInterval" value="0"/> rtelre is a dummy table with 1 column and one row. We use it for compatibility reasons, since we deploy in several environments, and on oracle the from clause is mandatory (so select 1 does not fit). So the problem is that the select 1 from rtelre is never commited, nor rolled back. Adding a commit in the query seems to work : "select 1 from rtelre; commit;" does not leave idle transactions. That said, we never encountered such config example in the docs, nor on web. So here is my question(s): - is it expected that the validationQuery transaction are not terminated ? - is adding a commit (or rollback, for that matters) in the query a valid workaround, or even the only sensible configuration ? - if so, shouldn't it be mentionned somewhere in the docs ? Thanks, Franck --------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
