Re: [SQL] Questions about vacuum analyze

Tom,

I tried your suggestion for -S 32 through -S 1 running a select distinct against
my history table.  I normally run with -S 2048 on the startup of the master
process.  FYI: the history table uses the same layout as the currnt table, its
just an hourly summation of the minute by minute data in currnt.  history has
189,724 records.

Bottom line: The select never failed.

Thanks

--
Steven Wheeler
Mid-Range UNIX Engineering
Sabre Inc.
(918) 292-4119

Tom Lane wrote:

> "Steven M. Wheeler" <swheeler@sabre.com> writes:
> > 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
>
> Hmph --- just looking at the code that issues that message, it looks
> like it is a harmless gripe (it complains, but then goes and does the
> missing step).  However, it's suggestive that there may be a logic error
> in the sorting code that's calling the BufFileXXX routines.
>
> > And I get 0 records selected.
>
> And that's even more suggestive ;-)
>
> This is going to be tough to debug if it takes a multi-gig database to
> replicate.  But if I'm guessing right about where the problem is, it
> probably can be replicated on smaller tables if you use a smaller -S
> (sort memory limit) setting when starting the backend.  You can set -S
> as small as 32 (kb), and having done that the bug might show up on tables
> with a few hundred K of data.  Would you try that and let me know?
>
> The easiest way to experiment is to set -S on a per-backend basis
> via the PGOPTIONS environment variable, eg
>         setenv PGOPTIONS "-S 32"
>         psql ...
> The backend started for this psql session will set S=32 instead of
> whatever the prevailing default is.
>
>                         regards, tom lane