Date: Tue, 11 Sep 2007 17:52:42 -0500
From: Decibel! <decibel@decibel.org>
To: Ruben Rubio <ruben@rentalia.com>
Cc: db@zigo.dhs.org, pgsql-performance@postgresql.org
Subject: Re: [Again] Postgres performance problem
Message-ID: <20070911225242.GG38801@decibel.org>
References: <46E636CB.8080008@rentalia.com>
	<43393.192.121.104.48.1189495883.squirrel@zigo.dhs.org>
	<46E64891.9080005@rentalia.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="6EJeIxieNG5JWVPi"
Content-Disposition: inline
In-Reply-To: <46E64891.9080005@rentalia.com>
User-Agent: Mutt/1.5.13 (2006-08-11)


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

On Tue, Sep 11, 2007 at 09:49:37AM +0200, Ruben Rubio wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>=20
> db@zigo.dhs.org escribi?:
> >> Last time I had this problem i solved it stopping website,  restarting
> >> database, vacuumm it, run again website. But I guess this is going to
> >> happen again.
> >>
> >> I would like to detect and solve the problem. Any ideas to detect it?
> >=20
> > Do you have very long transactions? Maybe some client that is connected
> > all the time that is idle in transaction?
>=20
> There should not be long transactions. I ll keep an eye on Idle transacti=
ons
>=20
> I m detecting it using:
>=20
> echo 'SELECT current_query  FROM pg_stat_activity;' |
> /usr/local/pgsql/bin/psql vacadb  | grep IDLE | wc -l

If you're using VACUUM FULL, you're doing something wrong. :) Run lazy
vacuum frequently enough (better yet, autovacuum, but cut all of 8.1's
autovac parameters in half), and make sure your FSM is big enough
(periodic vacuumdb -av | tail is an easy way to check that).

Try a REINDEX. VACUUM FULL is especially hard on the indexes, and it's
easy for them to seriously bloat.
--=20
Decibel!, aka Jim Nasby                        decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

--6EJeIxieNG5JWVPi
Content-Type: application/pgp-signature
Content-Disposition: inline

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

iD8DBQFG5xw6dO30qud8SkgRAkvJAJ9ye3n/fo/QLWAVgQtJFs1AZ0plUwCghGnr
nkSUMnsN6WRMerovsDu4j5o=
=l6IL
-----END PGP SIGNATURE-----

--6EJeIxieNG5JWVPi--