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 14:37:46 -0400
Message-ID: <28417.1182364666@sss.pgh.pa.us> (raw)
In-Reply-To: <4A9A1D1D-E97B-4E6F-8C80-684A9690631D@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>

Kurt Overberg <kurt@hotdogrecords.com> writes:
> Okay, so I did some digging with pg_filedump, and found the following:

> Block  406 ********************************************************
> Item   1 -- Length:  472  Offset: 7720 (0x1e28)  Flags: USED
>    XMIN: 1489323584  CMIN: 1  XMAX: 0  CMAX|XVAC: 0
>    Block Id: 406  linp Index: 1   Attributes: 6   Size: 32
>    infomask: 0x0912 (HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID)

This is pretty much what you'd expect for a never-updated tuple...

> mydb=# select * from _my_cluster.sl_log_1 where ctid = '(406,1)';
> log_origin | log_xid | log_tableid | log_actionseq | log_cmdtype |  
> log_cmddata
> ------------+---------+-------------+---------------+------------- 
> +-------------
> (0 rows)

so I have to conclude that you've got a wraparound problem.  What is the
current XID counter?  (pg_controldata will give you that, along with a
lot of other junk.)  It might also be interesting to take a look at
"ls -l $PGDATA/pg_clog"; the mod times on the files in there would give
us an idea how fast XIDs are being consumed.

			regards, tom lane



view thread (23+ messages)  latest in thread

Message-ID: <28417.1182364666@sss.pgh.pa.us>
Permalink:  ../28417.1182364666@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/28417.1182364666@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: <28417.1182364666@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