Could we somehow track how many pages we _couldn't_ get into the free
space map, then when the map is empty _and_ we find we have found there
are some pages that we couldn't store during the last vacuum, we throw a
message to the server logs? (Just thinnking out loud.)
---------------------------------------------------------------------------
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Is there any way we can warn users when their fsm parameters are too
> > small?
>
> Not until we understand what too small is :-( If anyone's undertaken
> any experiments to figure out what an appropriate FSM size setting is,
> I'm not aware of it.
>
> The default setting is 10000 pages which would certainly cover all the
> free space in 8K*10000 = 80meg of tables, and in practice would cover
> significantly more space as long as most of your pages weren't updated
> often (and hence didn't have free space to worry about). But obviously
> this number is on the low side for production databases, especially
> large ones. We need to put "pay attention to FSM size" right after
> "pay attention to shared_buffers" in the standard list of tuning tips.
>
> Presumably there's some tradeoff curve that says max_fsm_pages should
> cover X% of your physical database page count if you update Y% of the
> database rows between vacuums. I'm not sure what the curve looks like
> though --- the real issue is how many distinct pages are likely to be
> touched when you update so-and-so many rows?
>
> regards, tom lane
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026