On Fri, Apr 18, 2014 at 3:07 AM, Franck Routier <franck.routier@xxxxxxxxx> wrote: > I have found the problem, using this query (found here > http://stackoverflow.com/questions/3312929/postgresql-idle-in-transaction-diagnosis-and-reading-pg-locks) > > select pg_class.relname, pg_locks.transactionid, pg_locks.mode, > pg_locks.granted as "g", pg_stat_activity.current_query, > pg_stat_activity.query_start, > age(now(),pg_stat_activity.query_start) as "age", > pg_stat_activity.procpid > from pg_stat_activity,pg_locks > left outer join pg_class on (pg_locks.relation = pg_class.oid) > where pg_locks.pid=pg_stat_activity.procpid > and pg_stat_activity.procpid = <AN IDLE TRANSACTION PROCESS> > order by query_start; > > > And indeed, we constantly have idle transcations. They all use the same > dummy table, a dual table substitute containing only one column, and one > row. > We use this table with tomcat-jdbc-pool to check connections health with > 'select 1 from dual' (we don't use 'select 1' for portability reasons, to > work with oracle also). > And these transactions are never commited. So we have a bunch of running > transactions, constantly running and recreated by tomcat-jdbc-pool. Some of > them run for hours. > This seems to impact significally the ability of postgresql to vacuum... and > thus to keep efficient indexes! It affects a lot of other things too. All locks held by those transactions are still held. Failure to release transactions is a major application error that can and will explode the database. It's similar in severity to a memory leak. The basic rule of thumb is that transactions should be held for the shortest possible duration -- especially those that write to the database. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
