On Apr 18, 2014, at 5:05 AM, Franck Routier <franck.rout...@axege.com> wrote:
> 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 ? I don’t know what the intended behavior is here since I didn’t write the code, but given that you have set defaultAutoCommit to false, I could see this happening. > - is adding a commit (or rollback, for that matters) in the query a > valid workaround, or even the only sensible configuration ? Another option you might try would be to set “rollbackOnReturn" to “true”. That is supposed to instruct the pool to rollback an open transactions when the connection is returned to the pool. I haven’t tried it, but it seems like it would work here. Dan > - 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 > --------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
