Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 8102B9FA356 for ; Tue, 11 Sep 2007 13:31:54 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 00998-02 for ; Tue, 11 Sep 2007 13:31:51 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from ldapmta.farheap.com (zsmtp.farheap.com [64.22.136.34]) by postgresql.org (Postfix) with ESMTP id 14F8B9FA307 for ; Tue, 11 Sep 2007 13:31:50 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by ldapmta.farheap.com (Postfix) with ESMTP id 9116334C4AF; Tue, 11 Sep 2007 09:27:59 -0700 (PDT) X-Virus-Scanned: amavisd-new at Received: from ldapmta.farheap.com ([127.0.0.1]) by localhost (ldapmta.farheap.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id Ug3J9s7dI9wj; Tue, 11 Sep 2007 09:27:57 -0700 (PDT) Received: from [10.1.129.88] (daimler.farheap.net [66.238.64.20]) by ldapmta.farheap.com (Postfix) with ESMTP id 2FCC834C4AC; Tue, 11 Sep 2007 09:27:57 -0700 (PDT) Message-ID: <46E6C2F2.7050105@farheap.com> Date: Tue, 11 Sep 2007 09:31:46 -0700 From: Brian Staszewski User-Agent: Thunderbird 2.0.0.6 (X11/20070808) MIME-Version: 1.0 To: Tom Lane CC: pgsql-admin@postgresql.org Subject: Re: Close open transactions soon to avoid wraparound problems References: <46E58BFD.80300@farheap.com> <8899.1189475645@sss.pgh.pa.us> In-Reply-To: <8899.1189475645@sss.pgh.pa.us> Content-Type: multipart/mixed; boundary="------------090404070409030009040801" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/66 X-Sequence-Number: 26827 This is a multi-part message in MIME format. --------------090404070409030009040801 Content-Type: multipart/alternative; boundary="------------000901000704010205010902" --------------000901000704010205010902 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Tom Lane wrote: > Brian Staszewski writes: > >> WARNING: oldest xmin is far in the past >> HINT: Close open transactions soon to avoid wraparound problems. >> > > Hmm ... which PG version is this? I think 8.2 will start to complain > about this much earlier than older versions (~ 100M transactions by > default, instead of 1000M). Though you've probably got a problem > here in any case. > > Yep, 8.2.4. >> What's been done so far: >> -Cluster-wide vacuum (these are done nightly, I ran one again just for gp) >> > > Not relevant > > >> -Ran the following query looking for old/suspicious >> connections/transactions, didn't find anything useful: >> select procpid, datname, client_addr, usename, current_query, >> query_start from pg_stat_activity order by query_start asc; >> > > Most likely the problem transaction is sitting idle and hence has null > query_start. Try "ps auxww" or local equivalent and look for postgres > processes that are very old and are in "idle in transaction" state. > > regards, tom lane > "ps auxww | grep trans " did in fact reveal three processes that were 2, 3, and 4 days old that the query above didn't show. The connections were from three different application servers and restarting the pools on those servers corrected the problem. Thanks, Brian --------------000901000704010205010902 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Tom Lane wrote:
Brian Staszewski <brian.staszewski@farheap.com> writes:
  
WARNING:  oldest xmin is far in the past
HINT:  Close open transactions soon to avoid wraparound problems.
    

Hmm ... which PG version is this?  I think 8.2 will start to complain
about this much earlier than older versions (~ 100M transactions by
default, instead of 1000M).  Though you've probably got a problem
here in any case.

  
Yep, 8.2.4.

  
What's been done so far:
-Cluster-wide vacuum (these are done nightly, I ran one again just for gp)
    

Not relevant

  
-Ran the following query looking for old/suspicious 
connections/transactions, didn't find anything useful:
select procpid, datname, client_addr, usename, current_query, 
query_start from pg_stat_activity order by query_start asc;
    

Most likely the problem transaction is sitting idle and hence has null
query_start.  Try "ps auxww" or local equivalent and look for postgres
processes that are very old and are in "idle in transaction" state.

			regards, tom lane
  
"ps auxww | grep trans " did in fact reveal three processes that were 2, 3, and 4 days old that the query above didn't show. The connections were from three different application servers and restarting the pools on those servers corrected the problem.

Thanks,
Brian
--------------000901000704010205010902-- --------------090404070409030009040801 Content-Type: text/x-vcard; charset=utf-8; name="brian.staszewski.vcf" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="brian.staszewski.vcf" begin:vcard fn:Brian Staszewski n:Staszewski;Brian org:Farheap Solutions, Inc.;Information Technologies email;internet:brian.staszewski@farheap.com title:Systems Administrator tel;work:(949) 417-1500 x948278 x-mozilla-html:TRUE version:2.1 end:vcard --------------090404070409030009040801--