Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ktsour@gmail.com>) id 1TGs0e-00009K-UU
	for pgsql-performance@postgresql.org; Wed, 26 Sep 2012 13:50:41 +0000
Received: from mail-wg0-f50.google.com ([74.125.82.50])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ktsour@gmail.com>) id 1TGs0b-0003cy-GI
	for pgsql-performance@postgresql.org; Wed, 26 Sep 2012 13:50:40 +0000
Received: by wgi16 with SMTP id 16so549547wgi.19
	for <pgsql-performance@postgresql.org>;
	Wed, 26 Sep 2012 06:50:33 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=subject:mime-version:content-type:from:in-reply-to:date:message-id
	:references:to:x-mailer;
	bh=mtgsicVQC27CZ532ExeBVBByKhHI9/rOhGYRmsfVTL8=;
	b=Sc26r/iaEDan5/iut4eyxcE3R+S11XqSxwrCTIUavYGiKRii4RTCQ6+VB0uaVSv1sR
	0yDSYtl1yDDcFUOwrpcvaUMdQTGmqMOefF2D4N9IHRxm0uqxUUSJ05CMf+CMzpc1mA9x
	v4K5ZnIJahSDeKaAmtJ/aFw27JAZUFZtpYtPrwFRYOJR4ydB6iHRlXDmhHzUAj8EgfKd
	WXy4GUPF6D3du0p/mgVTq8Ei0askqwH8b/E6oOJbyxSalVmjtbzBHYPB6FERZNCkJzo+
	c5CkGuK0tSUA0GeeA6OQh5cRTnSAUS6G+iiMJoIXoLORbKWTfs/rWbu3rE32Ci9Ho8oA
	INbA==
Received: by 10.180.107.163 with SMTP id hd3mr1590975wib.19.1348667433288;
	Wed, 26 Sep 2012 06:50:33 -0700 (PDT)
Received: from [192.168.1.103] (kstr60.static.otenet.gr. [79.129.45.250])
	by mx.google.com with ESMTPS id v3sm25759053wiw.7.2012.09.26.06.50.31
	(version=TLSv1/SSLv3 cipher=OTHER);
	Wed, 26 Sep 2012 06:50:32 -0700 (PDT)
Subject: Re: Postgres becoming slow, only full vacuum fixes it
Mime-Version: 1.0 (Apple Message framework v1278)
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_0BCE224D-2F83-4273-B132-FCE916238469"
From: Kiriakos Tsourapas <ktsour@gmail.com>
In-Reply-To: <20120926113953.221020@gmx.com>
Date: Wed, 26 Sep 2012 16:50:29 +0300
Message-Id: <34C83EA2-7004-4D37-9674-ADFB4A475C07@gmail.com>
References: <20120926113953.221020@gmx.com>
To: pgsql-performance@postgresql.org
X-Mailer: Apple Mail (2.1278)
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201209/129
X-Sequence-Number: 47875


--Apple-Mail=_0BCE224D-2F83-4273-B132-FCE916238469
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii

Hi Kevin,

On Sep 26, 2012, at 14:39, Kevin Grittner wrote:
>=20
> I am concerned that your initial email said that you had this
> setting:
>=20
> autovacuum_naptime =3D 28800
>=20
> This is much too high for most purposes; small, frequently-modified
> tables won't be kept in good shape with this setting.  Perhaps you're
> not having that problem at the moment, but it's risky to assume that
> you don't and never will.  When autovacuum wakes up and there is
> nothing to do it should go back to sleep very quickly.
>=20

I used the 28800 (8hours) setting after I realized that the default 1min =
was not helping.
I also changed other parameters when I changed it to 8 hours, to make =
sure tables would be auto vacuumed.
The problem with my setting was that autovacuum gets stopped if a lock =
is needed on the table. So, it was very bad choice to run it every 8 =
hours, because usually it got stopped and never did anything.
So, I turned back to the original setting of 1min but changed the =
autovacuum_vacuum_scale_factor to 1% instead of 20%. Hopefully tables =
will be more frequently vacuumed now and the problem will not appear =
again.

> Don't expect too much from just making autovacuum run more often
> until you have eliminated existing bloat (autovacuum generally just
> limits further growth of bloat) and updated to the latest 8.4 minor
> release.  The following bugs fixes are among many you are living
> without until you upgrade:

Can you please suggest of a way to=20
- find if there is existing bloat
- eliminate it

Thank you=

--Apple-Mail=_0BCE224D-2F83-4273-B132-FCE916238469
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=us-ascii

<html><head></head><body style=3D"word-wrap: break-word; =
-webkit-nbsp-mode: space; -webkit-line-break: after-white-space; =
"><div>Hi Kevin,</div><br><div><div>On Sep 26, 2012, at 14:39, Kevin =
Grittner wrote:</div><blockquote type=3D"cite"><div><font =
class=3D"Apple-style-span" color=3D"#000000"><br></font>I am concerned =
that your initial email said that you had =
this<br>setting:<br><br>autovacuum_naptime =3D 28800<br><br>This is much =
too high for most purposes; small, frequently-modified<br>tables won't =
be kept in good shape with this setting. &nbsp;Perhaps you're<br>not =
having that problem at the moment, but it's risky to assume that<br>you =
don't and never will. &nbsp;When autovacuum wakes up and there =
is<br>nothing to do it should go back to sleep very =
quickly.<br><br></div></blockquote><div><br></div><div>I used the 28800 =
(8hours) setting after I realized that the default 1min was not =
helping.</div><div>I also changed other parameters when I changed it to =
8 hours, to make sure tables would be auto vacuumed.</div><div>The =
problem with my setting was that autovacuum gets stopped if a lock is =
needed on the table. So, it was very bad choice to run it every 8 hours, =
because usually it got stopped and never did anything.</div><div>So, I =
turned back to the original setting of 1min but changed =
the&nbsp;autovacuum_vacuum_scale_factor&nbsp;to 1% instead of 20%. =
Hopefully tables will be more frequently vacuumed now and the problem =
will not appear again.</div><div><br></div><blockquote =
type=3D"cite"><div>Don't expect too much from just making autovacuum run =
more often<br>until you have eliminated existing bloat (autovacuum =
generally just<br>limits further growth of bloat) and updated to the =
latest 8.4 minor<br>release. &nbsp;The following bugs fixes are among =
many you are living<br>without until you =
upgrade:<br></div></blockquote></div><br><div>Can you please suggest of =
a way to&nbsp;</div><div>- find if there is existing bloat</div><div>- =
eliminate it</div><div><br></div><div>Thank you</div></body></html>=

--Apple-Mail=_0BCE224D-2F83-4273-B132-FCE916238469--