Skip site navigation (1) Skip section navigation (2)

Re: Vacuum and FSM page size

From: Decibel! <decibel(at)decibel(dot)org>
To: Thomas Lozza <thomas(dot)lozza(at)nexustelecom(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Vacuum and FSM page size
Date: 2008-01-23 19:02:21
Message-ID: 20080123190220.GO37748@decibel.org (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: RichDate: 2008-01-23 19:06:02
Subject: Re: Making the most of memory?
Previous:From: Scott MarloweDate: 2008-01-23 18:44:38
Subject: Re: Making the most of memory?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group