pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kurt Overberg <kurt@hotdogrecords.com>
Cc: Chris Browne <cbbrowne@acm.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Maintenance question / DB size anomaly...
Date: Wed, 20 Jun 2007 17:08:41 -0400
Message-ID: <724.1182373721@sss.pgh.pa.us> (raw)
In-Reply-To: <1DF5E326-38EA-45E1-8B9B-AED1A2D8B7CF@hotdogrecords.com>
References: <A6D35557-74A3-47CF-B426-143C8CE01A13@hotdogrecords.com>
	<607iq0dlns.fsf@dba2.int.libertyrms.com>
	<1FF54A1D-9085-4C9B-81F3-D860FB132D44@hotdogrecords.com>
	<16441.1182288819@sss.pgh.pa.us>
	<A0763165-9B49-42C7-BBE7-4A670D74B7EE@hotdogrecords.com>
	<18495.1182295570@sss.pgh.pa.us>
	<4D1C51F2-79EA-4468-BF2D-1D5591BD8A3D@hotdogrecords.com>
	<1876.1182307875@sss.pgh.pa.us>
	<E5281888-721F-441C-9DC3-40EEFF4A4952@hotdogrecords.com>
	<18180.1182352957@sss.pgh.pa.us>
	<4A9A1D1D-E97B-4E6F-8C80-684A9690631D@hotdogrecords.com>
	<28417.1182364666@sss.pgh.pa.us>
	<1DF5E326-38EA-45E1-8B9B-AED1A2D8B7CF@hotdogrecords.com>

Kurt Overberg <kurt@hotdogrecords.com> writes:
> Drat!  I'm wrong again.  I thought for sure there wouldn't be a  
> wraparound problem.

Well, I'm not sure what it is now.  You showed some invisible tuples
with XMINs of
   XMIN: 1489323584  CMIN: 1  XMAX: 0  CMAX|XVAC: 0
   XMIN: 1489323590  CMIN: 2  XMAX: 0  CMAX|XVAC: 0
   XMIN: 1489323592  CMIN: 1  XMAX: 0  CMAX|XVAC: 0
but the nextXID is
         1490547335
which is not that far ahead of those --- about 1.2 million transactions,
or less than a day's time according to the clog timestamps, which
suggest that you're burning several million XIDs a day.  Perhaps you've
wrapped past them since your earlier check --- if you try the same
"select where ctid = " queries now, do they show rows?

The other thing that's strange here is that an 8.0 installation should
be pretty aggressive about recycling pg_clog segments, and yet you've
got a bunch there.  How far back do the files in pg_clog go --- what's
the numeric range of the filenames, and the date range of their mod
times?  Have you checked the postmaster log to see if you're getting any
complaints about checkpoint failures or anything like that?  It would
also be useful to look at the output of
select datname, age(datfrozenxid) from pg_database;

			regards, tom lane



view thread (23+ messages)  latest in thread

Message-ID: <724.1182373721@sss.pgh.pa.us>
Permalink:  ../724.1182373721@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/724.1182373721@sss.pgh.pa.us

 · 

reply
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: tgl@sss.pgh.pa.us, kurt@hotdogrecords.com, cbbrowne@acm.org
  Subject: Re: Maintenance question / DB size anomaly...
  In-Reply-To: <724.1182373721@sss.pgh.pa.us>

* 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