Hi Tom - thanks for the additional/confirming info.
>So you definitely have got a problem of needing more vacuuming...
Yes, we're going to nightly, as I said in last message, however,
it worse than this.....
I found that *1* vacuum analyze works well in many instances to
help optimize query performance (which in one example was running
in lightening speed on 2 of our 5 identical software/hardware/configs
Pg 8.1.4 servers). However, in several cases, a *2nd* vacuum
analyze was necessary. (btw - first vacuum was after adjusting
max_fsm_pages, and getting no error msgs from vacuum).
I *think* - please advise, I may be able to affect configs
for a more effective vacuum analyze the first time around (??)
Perhaps an increase to deafult_statistics_target (set to 100??).
I'd read that when performing a vacuum analyze, Pg doesn't actually
go through all values in each table and update statistics, rather,
it samples some of the values and uses that statistical sample.
Thus, different runs of the vacuum analyze might generate different
statistics (on different dbs on different servers) since the same db
may be used differently on a different server. Is this correct??
Thanks for any advice....I'm hoping regular duplicate vacuum
analyze isn't the solution...
Susan
>From [EMAIL PROTECTED] Wed May 23 14:31:02 2007
X-Spam-Checker-Version: SpamAssassin 3.1.4 (2006-07-25) on borise.harvard.edu
X-Spam-Status: No, score=-0.1 required=3.0 tests=AWL,BAYES_50,SPF_HELO_PASS
autolearn=unavailable version=3.1.4
X-Spam-Level:
To: Susan Russo <[EMAIL PROTECTED]>
cc: [EMAIL PROTECTED], pgsql-performance@postgresql.org
Subject: Re: [PERFORM] does VACUUM ANALYZE complete with this error?
Comments: In-reply-to Susan Russo <[EMAIL PROTECTED]>
message dated "Wed, 23 May 2007 14:18:41 -0400"
Date: Wed, 23 May 2007 14:31:01 -0400
From: Tom Lane <[EMAIL PROTECTED]>
Susan Russo <[EMAIL PROTECTED]> writes:
>> What PG version is that? I recall we fixed a problem recently that
>> caused the requested max_fsm_pages to increase some more when you'd
>> increased it to what the message said.
> 8.1.4
OK, I checked the CVS history and found this:
2006-09-21 16:31 tgl
* contrib/pg_freespacemap/README.pg_freespacemap,
contrib/pg_freespacemap/pg_freespacemap.c,
contrib/pg_freespacemap/pg_freespacemap.sql.in,
src/backend/access/gin/ginvacuum.c,
src/backend/access/gist/gistvacuum.c,
src/backend/access/nbtree/nbtree.c, src/backend/commands/vacuum.c,
src/backend/commands/vacuumlazy.c,
src/backend/storage/freespace/freespace.c,
src/include/storage/freespace.h: Fix free space map to correctly
track the total amount of FSM space needed even when a single
relation requires more than max_fsm_pages pages. Also, make VACUUM
emit a warning in this case, since it likely means that VACUUM FULL
or other drastic corrective measure is needed. Per reports from
Jeff Frost and others of unexpected changes in the claimed
max_fsm_pages need.
This is in 8.2, but we didn't back-patch because it made incompatible
changes in the contrib/pg_freespacemap views.
As the commit message says, the behavior of having the requested
max_fsm_pages value move up after you increase the setting is triggered
by having individual tables that need more than max_fsm_pages. So you
definitely have got a problem of needing more vacuuming...
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
