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?
Date: Wed, 1 Dec 2004 13:09:40 -0700
Message-ID: <20041201200940.GA24852@winnie.fuhr.org> (raw)
In-Reply-To: <002201c4d7d0$9af23330$a101a8c0@home.axiontech.com>
References: <002201c4d7d0$9af23330$a101a8c0@home.axiontech.com>
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/
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-sql@postgresql.org
Cc: mike@fuhr.org, yudie@axiontech.com
Subject: Re: Found Large Files.. what objects are they?
In-Reply-To: <20041201200940.GA24852@winnie.fuhr.org>
* 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