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!
Changing the configration of tomcat-jdbc-pool to 'select 1 from dual;
commit;' seems to resolve the problem.
I'm going to ask on tomcat-jdbc-pool mailing-list if this is ok.
Thanks a lot for your help.
Franck
|
||
