pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Close open transactions soon to avoid wraparound problems 3+ messages / 2 participants [nested] [flat]
* Close open transactions soon to avoid wraparound problems @ 2007-09-10 18:25 Brian Staszewski <brian.staszewski@farheap.com> 0 siblings, 1 reply; 3+ messages in thread From: Brian Staszewski @ 2007-09-10 18:25 UTC (permalink / raw) To: pgsql-admin 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 Attachments:^ permalink raw reply [nested|flat] 3+ messages in threadbrian.staszewski.vcf (text/x-vcard, 249B) download
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
* Re: Close open transactions soon to avoid wraparound problems @ 2007-09-11 01:54 Tom Lane <tgl@sss.pgh.pa.us> parent: Brian Staszewski <brian.staszewski@farheap.com> 0 siblings, 1 reply; 3+ messages in thread From: Tom Lane @ 2007-09-11 01:54 UTC (permalink / raw) To: Brian Staszewski <brian.staszewski@farheap.com>; +Cc: pgsql-admin 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. > 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Close open transactions soon to avoid wraparound problems @ 2007-09-11 16:31 Brian Staszewski <brian.staszewski@farheap.com> parent: Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 3+ messages in thread From: Brian Staszewski @ 2007-09-11 16:31 UTC (permalink / raw) To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin 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 Attachments:^ permalink raw reply [nested|flat] 3+ messages in threadbrian.staszewski.vcf (text/x-vcard, 249B) download
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
end of thread, other threads:[~2007-09-11 16:31 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2007-09-10 18:25 Close open transactions soon to avoid wraparound problems Brian Staszewski <brian.staszewski@farheap.com> 2007-09-11 01:54 ` Tom Lane <tgl@sss.pgh.pa.us> 2007-09-11 16:31 ` Brian Staszewski <brian.staszewski@farheap.com>
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