From: Steven M. Wheeler <swheeler@sabre.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@hub.org
Subject: Re: [SQL] Questions about vacuum analyze
Date: Thu, 07 Oct 1999 06:29:38 -0500
Message-ID: <37FC8422.82295F9A@sabre.com> (raw)
References: <3427.939074616@sss.pgh.pa.us>
More information on the continuing vacuum saga:
The latest run after having processed an additional 40K+ msgs (4M+ inserts).
Start: 10/06/1999@07:28:07
complete: 10/06/1999@21:17:32
About 14 hours.
Two tables.
The currnt table has 39.5M+ rows in it. Select count(*) took 14m41s
The history table only has 190K rows.
I am now running into another problem. I need to do select distinct from the
currnt to get dates for maintenance activity (rows to summarize and move to
history). When ever I do I get this message in my server.log file:
NOTICE: BufFileRead: should have flushed after writing
And I get 0 records selected. Can't find this message in the docs. Any
suggestions? Should I submit this on the SQL list?
Thanks, you're help is greatly appreciated!
--
Steven Wheeler
Mid-Range UNIX Engineering
Sabre Inc.
(918) 292-4119
Tom Lane wrote:
> "Steven M. Wheeler" <swheeler@sabre.com> writes:
> > I believe that 6.5.2 and a little space reclamation in my DB directory may
> > have taken care of the vacuum problem. The last vacuum ran in about 10
> > minutes on an already vacuumed DB.
>
> Ah, that's more like it...
>
> > 1) With 6.5.2 should I still drop the indexes prior to running vacuum?
>
> Probably, but I'm not sure. Try it both ways and see.
>
> > 2) Is there a command/script that will cleanup the temp files and previous
> > table files in the DB directory. The reason I ask: I now have 3 copies of
> > my currnt table file (currnt, currnt.1, currnt.2) for a total usage of
> > about 3GB. Since it appears that currnt.2 is the one being accessed, can I
> > safely delete currnt & currnt.1?
>
> NO NO NO NO NO!!!!
>
> Files named like that are NOT temp files!! What they are are segments
> of a large table. We segment big tables into gigabyte-sized chunks
> to avoid problems on systems that have an int32-related limit on the
> size of individual files (which is most Unixes these days, I think).
>
> If you see anything named like pg_tempNNN.NNN, then that really is
> a temp file, and if it's not got a very recent mod time then it's
> probably left over from a crashed backend. Old temp files should be
> safe to get rid of. (IIRC, one of the components of the name is the
> PID of the creating backend, so you can check to be sure that the
> backend is no longer around if you want to be doubly sure.)
>
> regards, tom lane
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: swheeler@sabre.com, tgl@sss.pgh.pa.us, pgsql-sql@hub.org
Subject: Re: [SQL] Questions about vacuum analyze
In-Reply-To: <37FC8422.82295F9A@sabre.com>
* 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