From yudie@axiontech.com Wed Dec  1 18:09:03 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 80F9D3A5644
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  1 Dec 2004 18:09:03 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 08291-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  1 Dec 2004 18:08:56 +0000 (GMT)
Received: from axiontech.com (unknown [216.52.172.78])
	by svr1.postgresql.org (Postfix) with SMTP id 393B03A5609
	for <pgsql-sql@postgresql.org>; Wed,  1 Dec 2004 18:08:56 +0000 (GMT)
Received: (qmail 24592 invoked from network); 1 Dec 2004 18:09:03 -0000
Received: from pc-00161 (HELO yudie) (192.168.1.161)
	by mail.axiontech.com (192.168.1.6) with SMTP;
	01 Dec 2004 18:09:03 -0000
Message-ID: <002201c4d7d0$9af23330$a101a8c0@home.axiontech.com>
From: "Yudie" <yudie@axiontech.com>
To: <pgsql-sql@postgresql.org>
Subject: Found Large Files.. what objects are they?
Date: Wed, 1 Dec 2004 12:07:20 -0600
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_001F_01C4D79E.4F249A60"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200412/5
X-Sequence-Number: 19625

This is a multi-part message in MIME format.

------=_NextPart_000_001F_01C4D79E.4F249A60
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I found bunch of large files (more than 1 gb) in one of database =
directory.
The files looks like this:
69233123
69233123.1
69233123.2
69233123.3
69233123.4
...and so on.

These large files very delay the dumping process.=20
Anyone know what it could be & how to delete the object related? How to =
find a table by oid?

Thanks=20

Yudie

------=_NextPart_000_001F_01C4D79E.4F249A60
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1106" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>I found bunch of large files (more than =
1 gb) in=20
one of database directory.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>The files looks like this:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>
<DIV><FONT face=3DArial size=3D2>69233123</FONT></DIV></FONT></DIV>
<DIV><FONT face=3DArial size=3D2>69233123.1</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>
<DIV><FONT face=3DArial size=3D2>69233123.2
<DIV><FONT face=3DArial size=3D2>69233123.3
<DIV><FONT face=3DArial=20
size=3D2>69233123.4</FONT></DIV></FONT></DIV></FONT></DIV></FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT><FONT face=3DArial =
size=3D2>...and so=20
on.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>These large files&nbsp;very delay the =
dumping=20
process. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Anyone know what it could be &amp; how =
to delete=20
the object related? How to find a table by&nbsp;oid?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Thanks </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Yudie</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV></BODY></HTML>

------=_NextPart_000_001F_01C4D79E.4F249A60--





From mike@fuhr.org Thu Dec  2 02:15:35 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 914BB3A56D0
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  2 Dec 2004 02:15:35 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 49583-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  2 Dec 2004 02:15:26 +0000 (GMT)
Received: from www.postgresql.com (unknown [200.46.204.209])
	by svr1.postgresql.org (Postfix) with ESMTP id D47E13A5694
	for <pgsql-sql@postgresql.org>; Thu,  2 Dec 2004 02:15:02 +0000 (GMT)
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by www.postgresql.com (Postfix) with ESMTP id 7EC74738DB7
	for <pgsql-sql@postgresql.org>; Wed,  1 Dec 2004 20:11:08 +0000 (GMT)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.1/8.13.1) with ESMTP id iB1K9fGP022401
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Wed, 1 Dec 2004 13:09:43 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.1/8.13.1) with ESMTP id iB1K9eYE025212;
	Wed, 1 Dec 2004 13:09:40 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.1/8.13.1/Submit) id iB1K9eke025211;
	Wed, 1 Dec 2004 13:09:40 -0700 (MST) (envelope-from mfuhr)
Date: Wed, 1 Dec 2004 13:09:40 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Yudie <yudie@axiontech.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Found Large Files.. what objects are they?
Message-ID: <20041201200940.GA24852@winnie.fuhr.org>
References: <002201c4d7d0$9af23330$a101a8c0@home.axiontech.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <002201c4d7d0$9af23330$a101a8c0@home.axiontech.com>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=
X-Spam-Level: 
X-Archive-Number: 200412/16
X-Sequence-Number: 19636

On Wed, Dec 01, 2004 at 12:07:20PM -0600, Yudie wrote:

> I found bunch of large files (more than 1 gb) in one of database directory.
> The files looks like this:
> 69233123
> 69233123.1
> 69233123.2
> 69233123.3
> 69233123.4
> ...and so on.
> 
> These large files very delay the dumping process. 
> Anyone know what it could be & how to delete the object related?

When a file exceeds 1GB, PostgreSQL divides it into segments named
N, N.1, N.2, etc.

> How to find a table by oid?

contrib/oid2name should be helpful.  You could also query the system
catalogs: the files' parent directory should be the database's OID,
so for a file named .../12345/69233123 you could identify the
database with the following query:

SELECT datname FROM pg_database WHERE oid = 12345;

Connect to that database and find out which table or index uses the
files:

SELECT relname FROM pg_class WHERE relfilenode = 69233123;

Once you've done that, figure out why the files are so large:

Could it simply be that the table stores a lot of data?

Have you been running VACUUM?  If not, and if you've made a lot of
updates or deletes, then you'll have a lot of dead tuples;
contrib/pgstattuple can be useful for checking on that.  If that's
the case, then VACUUM FULL and/or REINDEX should recover the dead
space, but be aware that they'll acquire exclusive locks on the
objects they're working with and they might take a long time (hours)
to run.  Also, after a REINDEX a table's indexes will probably be
stored in different files; you can find out the new file names by
querying pg_class and looking at the relfilenode field.

Another possibility would be to dump, drop, recreate, and restore
the table and see if the file sizes shrink.  Again, the file names
will probably change, so query pg_class to see what the new ones
are.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/



From tgl@sss.pgh.pa.us Thu Dec  2 02:49:20 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 9D5363A5710
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  2 Dec 2004 02:49:20 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 60689-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  2 Dec 2004 02:49:14 +0000 (GMT)
Received: from www.postgresql.com (unknown [200.46.204.209])
	by svr1.postgresql.org (Postfix) with ESMTP id 722013A56E0
	for <pgsql-sql@postgresql.org>; Thu,  2 Dec 2004 02:48:24 +0000 (GMT)
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by www.postgresql.com (Postfix) with ESMTP id E37257391AC
	for <pgsql-sql@postgresql.org>; Wed,  1 Dec 2004 20:11:57 +0000 (GMT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id iB1KACwr018504;
	Wed, 1 Dec 2004 15:10:12 -0500 (EST)
To: "Yudie" <yudie@axiontech.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Found Large Files.. what objects are they? 
In-reply-to: <002201c4d7d0$9af23330$a101a8c0@home.axiontech.com> 
References: <002201c4d7d0$9af23330$a101a8c0@home.axiontech.com>
Comments: In-reply-to "Yudie" <yudie@axiontech.com>
	message dated "Wed, 01 Dec 2004 12:07:20 -0600"
Date: Wed, 01 Dec 2004 15:10:12 -0500
Message-ID: <18503.1101931812@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200412/19
X-Sequence-Number: 19639

"Yudie" <yudie@axiontech.com> writes:
> Anyone know what it could be & how to delete the object related? How to =
> find a table by oid?

Look at pg_class.relfilenode, not OID.  Or try contrib/oid2name.

			regards, tom lane



From cbbrowne@acm.org Wed Dec  1 22:53:30 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id A7D6F3A49EC
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  1 Dec 2004 22:53:30 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 90188-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  1 Dec 2004 22:53:19 +0000 (GMT)
Received: from news.hub.org (news.hub.org [200.46.204.72])
	by svr1.postgresql.org (Postfix) with ESMTP id 86E0C3A5466
	for <pgsql-sql@postgresql.org>; Wed,  1 Dec 2004 22:53:19 +0000 (GMT)
Received: from news.hub.org (news.hub.org [200.46.204.72])
	by news.hub.org (8.13.1/8.13.1) with ESMTP id iB1MrIrh095628
	for <pgsql-sql@postgresql.org>; Wed, 1 Dec 2004 22:53:18 GMT
	(envelope-from news@news.hub.org)
Received: (from news@localhost)
	by news.hub.org (8.13.1/8.13.1/Submit) id iB1Mo1Rw094688
	for pgsql-sql@postgresql.org; Wed, 1 Dec 2004 22:50:01 GMT
	(envelope-from news)
From: Christopher Browne <cbbrowne@acm.org>
X-Newsgroups: pgsql.sql
Subject: Re: Found Large Files.. what objects are they?
Date: Wed, 01 Dec 2004 17:46:45 -0500
Organization: cbbrowne Computing Inc
Lines: 39
Message-ID: <m3eki9skkq.fsf@knuth.knuth.cbbrowne.com>
References: <002201c4d7d0$9af23330$a101a8c0@home.axiontech.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
X-Complaints-To: usenet@news.hub.org
X-message-flag: Outlook is rather hackable, isn't it?
X-Home-Page: http://www.cbbrowne.com/info/
X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne
User-Agent: Gnus/5.1006 (Gnus v5.10.6) XEmacs/21.4 (Security Through
 Obscurity,
	linux)
Cancel-Lock: sha1:1Kzb0MzkKRTtDQtSeI23ETuFCNU=
To: pgsql-sql@postgresql.org
X-Virus-Scanned: by amavisd-new at hub.org
X-Archive-Number: 200412/10
X-Sequence-Number: 19630

Centuries ago, Nostradamus foresaw when yudie@axiontech.com ("Yudie") would write:
> I found bunch of large files (more than 1 gb) in one of database directory.
>
> The files looks like this:
>
>
> 69233123
>
> 69233123.1
>
>
> 69233123.2
>
> 69233123.3
>
> 69233123.4
>
> ...and so on.
>
> These large files very delay the dumping process.
>
> Anyone know what it could be & how to delete the object related? How to find a table by oid?

The phenomenon you are seeing occurs when there is a table with a
great deal of data.

Look for the table via the query:

  select * from pg_class where oid = 69233123;

Presumably these files are for a table that contains multiple GB of
data.  Or they could be for an index on a very large table.

If the data in the table/index is useless to you, you might consider
dropping the table/index.
-- 
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://www3.sympatico.ca/cbbrowne/wp.html
DO IT -- it's easier to get forgiveness than permission.