From: Decibel! <decibel@decibel.org>
To: Thomas Lozza <thomas.lozza@nexustelecom.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Vacuum and FSM page size
Date: Wed, 23 Jan 2008 13:02:21 -0600
Message-ID: <20080123190220.GO37748@decibel.org> (raw)
In-Reply-To: <F6B04D920899DF47B1E1A4F40415167E3D4299@gaia.nexus-ag.com>
References: <F6B04D920899DF47B1E1A4F40415167E3D4299@gaia.nexus-ag.com>
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@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
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: pgsql-performance@postgresql.org
Cc: decibel@decibel.org, thomas.lozza@nexustelecom.com
Subject: Re: Vacuum and FSM page size
In-Reply-To: <20080123190220.GO37748@decibel.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox