-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256 Dan,
On 4/18/14, 7:53 AM, Daniel Mikusa wrote: > 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. +1 Also note that setting defaultAutoCommit="false" changes the driver to be different than the JDBC spec default. That may confuse some Java programmers. >> - 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. It might not, if the connection isn't being "returned" to the pool, since it's just being checked. - -chris -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBCAAGBQJTUXJhAAoJEBzwKT+lPKRYvNQP/Ak2BPOwiW+xdCv99KKObYs7 232psJaox7GZg08Rn28Omyoqjzp+OmAUfT0iAy5cGKfRpX2+4SAQ4uG7qYyLEOkn jMy7tTk8B/V2ezuhLpxprXGOW6PT35xfEmakrDGMm6VrRwynZTUwVHDO9wijye8P 2kBETWUUwP1ML5u5bZzJ+yvFBBcpQZsMoo40jLvC0txTo0QzaJDfglLaZKa0+Xlv DYnseBcSvsuRnmJiLRSrVo3OcToTPzSERP8Rnn4adgSTISrbuKNmLrpIdtsCF+aO vxhTlJlfcK/ndl1+Kwin+kuMMbOAezzHpLcJnlmeZ+56MXsgjSF1W1ZBVsfosPZ2 gwVwmQCpQpRP3bNZPUXNtHCvXbIVKqF2PN6ExD4x34rtn+BE7St3Cv+Hyg36mrER 8O62t1y0fkk92S9vkGREVdtCw6XzwD6gApBunDtKqlilomjbwnf4FICfm+Oe4vgl EaTHLnKMlhzVhwWcXuHHyihGQysFi42HcTDbEcmx7M4btfYlGFoKqN2PSFsr1KfI 3T6C7mi8c24REXCXe9HzRp+UZ8HHVVqxCXZyNf04Zj42pmUnlPxR8R5Orp72TuIf 2wES9mW37tEZ0LkrfRWr6X2d4unmw7anaqZkUlIt12kD5h3XmqB7PXojYgchKF2V JWsVzpDt3HPGbQhBBxtr =2stW -----END PGP SIGNATURE----- --------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
