Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 0FB429FC034 for ; Wed, 20 Jun 2007 18:08:52 -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 54476-02 for ; Wed, 20 Jun 2007 18:08:47 -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 4E1659FC04C for ; Wed, 20 Jun 2007 18:08:46 -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 l5KL8fJP000725; Wed, 20 Jun 2007 17:08:41 -0400 (EDT) To: Kurt Overberg cc: Chris Browne , pgsql-performance@postgresql.org Subject: Re: Maintenance question / DB size anomaly... In-reply-to: <1DF5E326-38EA-45E1-8B9B-AED1A2D8B7CF@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> <28417.1182364666@sss.pgh.pa.us> <1DF5E326-38EA-45E1-8B9B-AED1A2D8B7CF@hotdogrecords.com> Comments: In-reply-to Kurt Overberg message dated "Wed, 20 Jun 2007 16:22:10 -0400" Date: Wed, 20 Jun 2007 17:08:41 -0400 Message-ID: <724.1182373721@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/450 X-Sequence-Number: 25483 Kurt Overberg 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