Re: [pgsql-www] pg_autovacuum is nice ... but ...

On Thu, 4 Nov 2004, Marc G. Fournier wrote:

>
> Moved to -hackers where this belongs :)
>
> On Fri, 5 Nov 2004, Justin Clift wrote:
>
> > Tom Lane wrote:
> > <snip>
> >> Yup. 20000 < 23072, so you're losing some proportion of FSM entries.
> >> What's worse, the FSM relation table is maxed out (1000 = 1000) which
> >> suggests that there are relations not being tracked at all; you have
> >> no idea how much space is getting leaked in those.
> >>
> >> You can determine the number of relations potentially needing FSM
> >> entries by
> >> select count(*) from pg_class where relkind in ('r','i','t');
> >> --- sum over all databases in the cluster to get the right result.
> >>
> >> Once you've fixed max_fsm_relations, do vacuums in all databases, and
> >> then vacuum verbose should give you a usable lower bound for
> >> max_fsm_pages.
> >
> > Would making max_fsm_relations and max_fsm_pages dynamically update
> > themselves whilst PostgreSQL runs be useful? Sounds like they're the
> > kind of things that many people would receive maximum benefit if
> > PostgreSQL altered these settings as needed itself.
>
> I'm not sure if I like this one too much ... but it would be nice if
> something like this triggered a warning in the logs, maybe a feature of
> pg_autovacuum itself?

Without a bit of hacking, its hard to increase the size of the free
space map dynamically. This is because the free space map resides in
shared memory and its the reason why the FSM GUC vars can only be changed
on postmaster restart -- because its at that time we can calculate how
much shared memory we need (for caching, fsm, other global resources) and
allocate it.

I think a contrib script which ran through each database and generated
some optimal FSM settings for a target database would be a good medium
term solution.

Thanks,

Gavin