From brian.staszewski@farheap.com Mon Sep 10 18:57:51 2007 Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 33FA19F9D22 for ; Mon, 10 Sep 2007 15:57:51 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 61270-03 for ; Mon, 10 Sep 2007 15:57:47 -0300 (ADT) X-Greylist: delayed 00:32:41.97743 by SQLgrey-1.7.5 Received: from ldapmta.farheap.com (zsmtp.farheap.com [64.22.136.34]) by postgresql.org (Postfix) with ESMTP id 797069F9A71 for ; Mon, 10 Sep 2007 15:57:47 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by ldapmta.farheap.com (Postfix) with ESMTP id 0F8C534C031 for ; Mon, 10 Sep 2007 11:21:16 -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 QNMrnZw8GTAj for ; Mon, 10 Sep 2007 11:21:13 -0700 (PDT) Received: from [10.1.129.88] (daimler.farheap.net [66.238.64.20]) by ldapmta.farheap.com (Postfix) with ESMTP id B01C234C4A5 for ; Mon, 10 Sep 2007 11:21:13 -0700 (PDT) Message-ID: <46E58BFD.80300@farheap.com> Date: Mon, 10 Sep 2007 11:25:01 -0700 From: Brian Staszewski User-Agent: Thunderbird 2.0.0.6 (X11/20070808) MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Close open transactions soon to avoid wraparound problems Content-Type: multipart/mixed; boundary="------------090809020700030402070502" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.002 tagged_above=0 required=5 tests=BAYES_50=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200709/54 X-Sequence-Number: 26815 This is a multi-part message in MIME format. --------------090809020700030402070502 Content-Type: multipart/alternative; boundary="------------070401070802060306040503" --------------070401070802060306040503 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Vacuumdb is giving me these messages: Vacuuming public.files... WARNING: oldest xmin is far in the past HINT: Close open transactions soon to avoid wraparound problems. WARNING: oldest xmin is far in the past HINT: Close open transactions soon to avoid wraparound problems. VACUUM Two questions: 1. Is there a way for me to see what application/host/user has open transactions that need to be closed? 2. If #1 is not possible then what can I do about this? Searching the web via normal methods only turns up indexed source code files for vacuum. What's been done so far: -Cluster-wide vacuum (these are done nightly, I ran one again just for gp) -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; Thanks in advance, Brian --------------070401070802060306040503 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Vacuumdb is giving me these messages:

Vacuuming public.files...
WARNING:  oldest xmin is far in the past
HINT:  Close open transactions soon to avoid wraparound problems.
WARNING:  oldest xmin is far in the past
HINT:  Close open transactions soon to avoid wraparound problems.
VACUUM


Two questions:
1. Is there a way for me to see what application/host/user has open transactions that need to be closed?
2. If #1 is not possible then what can I do about this?

Searching the web via normal methods only turns up indexed source code files for vacuum.

What's been done so far:
-Cluster-wide vacuum (these are done nightly, I ran one again just for gp)
-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;

Thanks in advance,
Brian
--------------070401070802060306040503-- --------------090809020700030402070502 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 --------------090809020700030402070502-- From tgl@sss.pgh.pa.us Tue Sep 11 01:54:11 2007 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 22E989F9EDC for ; Mon, 10 Sep 2007 22:54:11 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 75760-04 for ; Mon, 10 Sep 2007 22:53:59 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id AE2D59F9E1D for ; Mon, 10 Sep 2007 22:54:07 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l8B1s5qG008900; Mon, 10 Sep 2007 21:54:05 -0400 (EDT) To: Brian Staszewski cc: pgsql-admin@postgresql.org Subject: Re: Close open transactions soon to avoid wraparound problems In-reply-to: <46E58BFD.80300@farheap.com> References: <46E58BFD.80300@farheap.com> Comments: In-reply-to Brian Staszewski message dated "Mon, 10 Sep 2007 11:25:01 -0700" Date: Mon, 10 Sep 2007 21:54:05 -0400 Message-ID: <8899.1189475645@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/57 X-Sequence-Number: 26818 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. > 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 From brian.staszewski@farheap.com Tue Sep 11 16:31:54 2007 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--