Re: Autovacuum / full vacuum

On Tue, Jan 17, 2006 at 03:50:38PM +0100, Michael Riess wrote:
> always wondered why there are no examples for common postgresql
> configurations.

You mean like this one? (for 8.0):

<http://www.powerpostgresql.com/Downloads/annotated_conf_80.html>

> All I know is that the default configuration seems to be
> too low for production use.

Define "production use". It may be too low for you.

> chance to see if my FSM settings are too low other than to run vacuum
> full verbose in psql, pipe the result to a text file and grep for some

Not true. You don't need a FULL on there to figure this out.

> about the FSM: You say that increasing the FSM is fairly cheap - how
> should I know that?

Do the math. The docs say this:

--snip---
max_fsm_pages (integer)

Sets the maximum number of disk pages for which free space will
be tracked in the shared free-space map. Six bytes of shared memory
are consumed for each page slot. This setting must be more than 16 *
max_fsm_relations. The default is 20000. This option can only be set
at server start.

max_fsm_relations (integer)

Sets the maximum number of relations (tables and indexes) for
which free space will be tracked in the shared free-space map.
Roughly seventy bytes of shared memory are consumed for each slot.
The default is 1000. This option can only be set at server start.

---snip---

So by default, you have 6 B * 20,000 = 120,000 bytes for the FSM pages.

By default, you have 70 B * 1,000 = 70,000 bytes for the FSM
relations.

Now, there are two knobs. One of them tracks the number of
relations. How many relations do you have? Count the number of
indexes and tables you have, and give yourself some headroom in case
you add some more, and poof, you have your number for the relations.

Now all you need to do is figure out what your churn rate is on
tables, and count up how many disk pages that's likely to be. Give
yourself a little headroom, and the number of FSM pages is done, too.

This churn rate is often tough to estimate, though, so you may have
to fiddle with it from time to time.

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
I remember when computers were frustrating because they *did* exactly what
you told them to. That actually seems sort of quaint now.
--J.D. Baldwin