public inbox for [email protected]  

help / color / mirror / Atom feed

From: Gavin Sherry <[email protected]>
To: Marc G. Fournier <[email protected]>
Cc: Justin Clift <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: [email protected]
Subject: Re: [pgsql-www] pg_autovacuum is nice ... but ...
Date: Fri, 5 Nov 2004 10:09:05 +1100 (EST)
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>

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

view thread (24+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: [pgsql-www] pg_autovacuum is nice ... but ...
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox