Date: Wed, 23 Jan 2008 13:02:21 -0600
From: Decibel! <decibel@decibel.org>
To: Thomas Lozza <thomas.lozza@nexustelecom.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Vacuum and FSM page size
Message-ID: <20080123190220.GO37748@decibel.org>
References: <F6B04D920899DF47B1E1A4F40415167E3D4299@gaia.nexus-ag.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="DejVYFcqCV4p9T4J"
Content-Disposition: inline
In-Reply-To: <F6B04D920899DF47B1E1A4F40415167E3D4299@gaia.nexus-ag.com>
User-Agent: Mutt/1.5.13 (2006-08-11)


--DejVYFcqCV4p9T4J
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Wed, Jan 23, 2008 at 07:29:16PM +0100, Thomas Lozza wrote:
> hi
>=20
> 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.=20
>=20
> 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.=20
>=20
> 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.=20
> Auto vacuum is enabled. Here are the settings:
>=20
> autovacuum =3D true=09
> autovacuum_naptime =3D 900
Why'd you change that? That's pretty high.

> autovacuum_vacuum_threshold =3D 2000
> autovacuum_analyze_threshold =3D 1000

Both of those seem high...

> autovacuum_vacuum_scale_factor =3D 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 =3D 0.18
This also seems pretty high.

> autovacuum_vacuum_cost_delay =3D 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 =3D 120
Why'd you reduce this? I'd put it back to 200...
--=20
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org=20
Give your computer some brain candy! www.distributed.net Team #1828

--DejVYFcqCV4p9T4J
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.3 (FreeBSD)

iD8DBQFHl488dO30qud8SkgRAiINAJ9BamXtZudx4c0mp2khjxYQQZUooQCdGg/p
fOn1efbpMXbBRLobDCSgCRo=
=DHGb
-----END PGP SIGNATURE-----

--DejVYFcqCV4p9T4J--