Re: Unbounded (Possibly) Database Size Increase - Test Case

Mark kirkwood <markir(at)slingshot(dot)co(dot)nz> writes:
>> Was the FSM size parameter set large enough to cover the amount of space
>> you need the system to be able to recycle --- viz, the amount used
>> between vacuum runs? As with most everything else in PG, the default
>> value is not real large: 10000 pages = 80MB.

> I thought I was generous here ...~ 960M free space map

> max_fsm_relations = 100 # min 10, fsm is free space map
> max_fsm_pages = 120000 # min 1000, fsm is free space map

> I think I need to count how many vacuums performed during the test, so I
> can work out if this amount should have been enough. I timed a vacuum
> now at 12 minutes. (So with 10 concurrent threads it could take a lot
> longer during the run )

Keep in mind also that you need enough FSM entries to keep track of
partially-full pages. To really lock things down and guarantee no
table growth you might need one FSM slot for every page in your
relations. In practice you should be able to get away with much less
than that: you certainly don't need entries for pages with no free
space, and pages with only a little free space shouldn't be worth
tracking either. But if your situation is 100% update turnover between
vacuums then you could have a worst-case situation where all the pages
have roughly 50% free space right after a vacuum, and if you fail to
track them *all* then you're probably going to see some table growth
in the next cycle.

I believe that with a more reasonable vacuum frequency (vacuum after
10% to 25% turnover, say) the FSM requirements should be a lot less.
But I have not had time to do any experimentation to arrive at a rule
of thumb for vacuum frequency vs. FSM requirements. If you or someone
could run some experiments, it'd be a big help.

regards, tom lane