From: Franck Routier <franck.routier@axege.com>
To: pgsql-performance@postgresql.org
Subject: Re: Fast distinct not working as expected
Date: Fri, 18 Apr 2014 10:07:58 +0200
Message-ID: <5350DD5E.6050008@axege.com> (raw)
In-Reply-To: <5350D2D0.2080702@axege.com>
References: <534FEF1D.5010706@axege.com>
<CAMkU=1wTdkHmNkt9euasQux8LZ=JbWBcVb+5y1=XH3cYCpPTZw@mail.gmail.com>
<53500CBC.9030002@axege.com>
<CAMkU=1y6bd7h6oTLK5BAVS1N3ayNcUCrzRBVUv1OykkNeo6P=w@mail.gmail.com>
<5350D2D0.2080702@axege.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
I have found the problem, using this query |(found here http://stackoverflow.com/questions/3312929/postgresql-idle-in-transaction-diagnosis-and-reading-pg-l...)|
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
|
||
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: franck.routier@axege.com
Subject: Re: Fast distinct not working as expected
In-Reply-To: <5350DD5E.6050008@axege.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox