Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Wb3qF-0006mJ-M3 for pgsql-performance@arkaria.postgresql.org; Fri, 18 Apr 2014 08:08:11 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Wb3qE-0004YJ-SQ for pgsql-performance@arkaria.postgresql.org; Fri, 18 Apr 2014 08:08:10 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Wb3qD-0004YC-HJ for pgsql-performance@postgresql.org; Fri, 18 Apr 2014 08:08:09 +0000 Received: from smtp09.smtpout.orange.fr ([80.12.242.131] helo=smtp.smtpout.orange.fr) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Wb3qA-0007T2-3y for pgsql-performance@postgresql.org; Fri, 18 Apr 2014 08:08:08 +0000 Received: from axege.com ([82.127.232.162]) by mwinf5d69 with ME id rL841n00N3WtTCJ03L84p0; Fri, 18 Apr 2014 10:08:04 +0200 X-ME-Helo: axege.com X-ME-Date: Fri, 18 Apr 2014 10:08:04 +0200 X-ME-IP: 82.127.232.162 Received: from localhost (localhost [127.0.0.1]) by axege.com (Postfix) with ESMTP id 7C9E650109 for ; Fri, 18 Apr 2014 10:08:04 +0200 (CEST) X-Virus-Scanned: Debian amavisd-new at myhostname="axege.com." Received: from axege.com ([127.0.0.1]) by localhost (axege.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id CTC89sr0YYEc for ; Fri, 18 Apr 2014 10:07:59 +0200 (CEST) Received: from [10.0.0.75] (unknown [10.0.0.75]) (using TLSv1 with cipher DHE-RSA-AES128-SHA (128/128 bits)) (No client certificate requested) by axege.com (Postfix) with ESMTPSA id 0D16F4FF14 for ; Fri, 18 Apr 2014 10:07:59 +0200 (CEST) Message-ID: <5350DD5E.6050008@axege.com> Date: Fri, 18 Apr 2014 10:07:58 +0200 From: Franck Routier User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:24.0) Gecko/20100101 Thunderbird/24.4.0 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Fast distinct not working as expected References: <534FEF1D.5010706@axege.com> <53500CBC.9030002@axege.com> <5350D2D0.2080702@axege.com> In-Reply-To: <5350D2D0.2080702@axege.com> Content-Type: multipart/alternative; boundary="------------070102060008040206010002" X-Pg-Spam-Score: 0.8 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multi-part message in MIME format. --------------070102060008040206010002 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit 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 = 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 | || --------------070102060008040206010002 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit 
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
--------------070102060008040206010002--