From: | Franck Routier <franck(dot)routier(at)axege(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Fast distinct not working as expected |
Date: | 2014-04-18 08:07:58 |
Message-ID: | 5350DD5E.6050008@axege.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
|
||
From | Date | Subject | |
---|---|---|---|
Next Message | Vishalakshi Navaneethakrishnan | 2014-04-18 08:23:46 | Hot standby 9.2.1 PANIC: WAL contains references to invalid pages |
Previous Message | Franck Routier | 2014-04-18 07:22:56 | Re: Fast distinct not working as expected |