Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 9A5129FB467 for ; Sun, 27 May 2007 13:08:36 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 15234-05-3 for ; Sun, 27 May 2007 13:08:34 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id F006F9FB426 for ; Sun, 27 May 2007 13:08:33 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id 545425645C; Sun, 27 May 2007 11:08:33 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Sun, 27 May 2007 11:08:31 -0500 Date: Sun, 27 May 2007 11:08:31 -0500 From: "Jim C. Nasby" To: Bastian Voigt Cc: pgsql-performance@postgresql.org Subject: Re: Performance Problem with Vacuum of bytea table (PG 8.0.13) Message-ID: <20070527160831.GQ92628@nasby.net> References: <46569E6A.2060509@bastian-voigt.de> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="ucfHZChuBC0NsER/" Content-Disposition: inline In-Reply-To: <46569E6A.2060509@bastian-voigt.de> X-Operating-System: FreeBSD 6.1-RELEASE-p10 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.13 (2006-08-11) X-Hashcash: 1:20:070527:post@bastian-voigt.de::tldv/sfOkD0gnFFs:0000000000000000 0000000000000000000000001c+3 X-Hashcash: 1:20:070527:pgsql-performance@postgresql.org::27mDy+Y4WZ33oH48:00000 0000000000000000000000001UFO X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/563 X-Sequence-Number: 24953 --ucfHZChuBC0NsER/ Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Fri, May 25, 2007 at 10:29:30AM +0200, Bastian Voigt wrote: > Hi *, > for caching large autogenerated XML files, I have created a bytea table= =20 > in my database so that the cached files can be used by multiple servers.= =20 > There are about 500 rows and 10-20 Updates per minute on the table. The= =20 > files stored in the bytea are anything from 10kB to 10MB. My PostgreSQL= =20 > version is 8.0.13 on Gentoo Linux (x86) with PostGIS 1.2.0. >=20 > For vacuum I use the pg_autovacuum daemon. It decided to vacuum my cache= =20 > table about every 3 hours, the vacuum process takes 20-30 minutes=20 > (oops!) every time. You'll want to decrease autovacum_vacuum_scale_factor to 0.2 if you're on anything less than 8.2. --=20 Jim Nasby decibel@decibel.org EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) --ucfHZChuBC0NsER/ Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.3 (FreeBSD) iD8DBQFGWaz/dO30qud8SkgRAuApAJ4pEztjovXL8AYw58RLN0cKcveuKwCbBP9Q plkEVrpsCcK30SqPFkn9v0I= =p93o -----END PGP SIGNATURE----- --ucfHZChuBC0NsER/--