Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 0D73A9FBE52 for ; Wed, 20 Jun 2007 15:37:55 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 16131-02 for ; Wed, 20 Jun 2007 15:37:50 -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 B5D8F9FBE38 for ; Wed, 20 Jun 2007 15:37:51 -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 l5KIbkcs028418; Wed, 20 Jun 2007 14:37:47 -0400 (EDT) To: Kurt Overberg cc: Chris Browne , pgsql-performance@postgresql.org Subject: Re: Maintenance question / DB size anomaly... In-reply-to: <4A9A1D1D-E97B-4E6F-8C80-684A9690631D@hotdogrecords.com> References: <607iq0dlns.fsf@dba2.int.libertyrms.com> <1FF54A1D-9085-4C9B-81F3-D860FB132D44@hotdogrecords.com> <16441.1182288819@sss.pgh.pa.us> <18495.1182295570@sss.pgh.pa.us> <4D1C51F2-79EA-4468-BF2D-1D5591BD8A3D@hotdogrecords.com> <1876.1182307875@sss.pgh.pa.us> <18180.1182352957@sss.pgh.pa.us> <4A9A1D1D-E97B-4E6F-8C80-684A9690631D@hotdogrecords.com> Comments: In-reply-to Kurt Overberg message dated "Wed, 20 Jun 2007 14:09:43 -0400" Date: Wed, 20 Jun 2007 14:37:46 -0400 Message-ID: <28417.1182364666@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/445 X-Sequence-Number: 25478 Kurt Overberg 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