Re: Vacuum and FSM page size

On Wed, Jan 23, 2008 at 07:29:16PM +0100, Thomas Lozza wrote:
> hi
>
> We have an installation of Postgres 8.1.2 (32bit on Solaris 9) with a DB
> size of about 250GB on disk. The DB is subject to fair amount of
> inserts, deletes and updates per day.
>
> Running VACUUM VERBOSE tells me that I should allocate around 20M pages
> to FSM (max_fsm_pages)! This looks like a really large amount to me.
>
> Has anyone gone ever that high with max_fsm_pages?

No, that's telling me that you have a lot of bloat. A 250G database is
about 31M pages. If you have 20M pages with free space then you've got a
lot of bloat. Ideally, with a autovac_vacuum_scale_factor of .25 you
should only need 4M FSM pages. At most you should only need 8M.

> The other question is why such a large number is required in the first
> place.
> Auto vacuum is enabled. Here are the settings:
>
> autovacuum = true
> autovacuum_naptime = 900
Why'd you change that? That's pretty high.

> autovacuum_vacuum_threshold = 2000
> autovacuum_analyze_threshold = 1000

Both of those seem high...

> autovacuum_vacuum_scale_factor = 0.25
That means that 12.5% of your database (on average) will be dead
space... I'd probably cut that back to 0.2.

> autovacuum_analyze_scale_factor = 0.18
This also seems pretty high.

> autovacuum_vacuum_cost_delay = 150

Woah, that's *really* high. That means at most you'll get 6 vacuum
rounds in per second; with default cost settings that means you'd be
able to actually vacuum about 50 dirty pages per second, tops. Of course
not all pages will be dirty, but still...

I normally use between 10 and 20 for cost_delay (lower values for faster
drive arrays).

> autovacuum_vacuum_cost_limit = 120
Why'd you reduce this? I'd put it back to 200...
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828