From aa@devshock.com Wed Dec 7 15:34:48 2011 Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 4901B9ED581 for ; Wed, 7 Dec 2011 11:34:48 -0400 (AST) Received: from mail-gy0-f174.google.com ([209.85.160.174]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RYJWB-0006xq-HU for pgsql-performance@postgresql.org; Wed, 07 Dec 2011 15:34:48 +0000 Received: by ghbz2 with SMTP id z2so594600ghb.19 for ; Wed, 07 Dec 2011 07:34:35 -0800 (PST) Received: by 10.236.173.202 with SMTP id v50mr27925444yhl.102.1323272075062; Wed, 07 Dec 2011 07:34:35 -0800 (PST) Received: from AnibalVAIO ([200.85.32.68]) by mx.google.com with ESMTPS id f17sm5825959ang.20.2011.12.07.07.34.31 (version=TLSv1/SSLv3 cipher=OTHER); Wed, 07 Dec 2011 07:34:32 -0800 (PST) From: "Anibal David Acosta" To: Subject: autovacuum, any log? Date: Wed, 7 Dec 2011 12:34:30 -0300 Message-ID: <00c501ccb4f5$b89966b0$29cc3410$@devshock.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00C6_01CCB4DC.934D1910" X-Mailer: Microsoft Outlook 14.0 Thread-Index: Acy09OK4BxXZUfPMRdmVckagRltcTQ== Content-Language: es-py X-Pg-Spam-Score: -0.7 (/) X-Archive-Number: 201112/61 X-Sequence-Number: 45860 This is a multipart message in MIME format. ------=_NextPart_000_00C6_01CCB4DC.934D1910 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello, I have a postgres 9.0.2 installation. Every works fine, but in some hours of day I got several timeout in my application (my application wait X seconds before throw a timeout). Normally hours are not of intensive use, so I think that the autovacuum could be the problem. Is threre any log where autovacuum write information about it self like "duration for each table" or any other relevante information. Another inline question, should I exclude bigger tables from autovacuum or there are some mechanism to tell autovacuum to not run often on bigger tables (tables with more than 400 millions of rows) Thanks! ------=_NextPart_000_00C6_01CCB4DC.934D1910 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hello, I = have a postgres 9.0.2 installation.

Every works fine, but in some hours of day I got = several timeout in my application (my application wait X seconds before = throw a timeout).

Normally hours are = not of intensive use, so I think that the autovacuum could be the = problem.

Is threre any log where autovacuum write information = about it self like “duration for each table” or any other = relevante information.

Another = inline question, should I exclude bigger tables from autovacuum or there = are some mechanism to tell autovacuum to not run often on bigger tables = (tables with more than 400 millions of rows)

Thanks!

------=_NextPart_000_00C6_01CCB4DC.934D1910-- From scott.marlowe@gmail.com Wed Dec 7 20:19:43 2011 Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id A26B09ED582 for ; Wed, 7 Dec 2011 16:19:43 -0400 (AST) Received: from mail-bw0-f46.google.com ([209.85.214.46]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RYNxu-0003gQ-I3 for pgsql-performance@postgresql.org; Wed, 07 Dec 2011 20:19:44 +0000 Received: by bkbzv3 with SMTP id zv3so884864bkb.19 for ; Wed, 07 Dec 2011 12:19:29 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; bh=TO+PlOgW9VPeeAxwpjnwO9NzdmkJOvPRB57hDKbeFpo=; b=SmsabP2mq/vwrBGWEMn27nlWdERwsxrlxE9TEwLopWtNwbUGpWBqhxUah2JfvkT03I sc0hYc1H16K7Qdd8IR+Wa0lJzxHxKiF6uCD8sHOzTDvPSErRY8xY/t2D5LhCItLfImzC xJF9akM1P9y1G4A9bp36P5EXYlAqV3Bpayn5s= MIME-Version: 1.0 Received: by 10.204.155.141 with SMTP id s13mr136170bkw.40.1323289169544; Wed, 07 Dec 2011 12:19:29 -0800 (PST) Received: by 10.204.38.131 with HTTP; Wed, 7 Dec 2011 12:19:29 -0800 (PST) In-Reply-To: <00c501ccb4f5$b89966b0$29cc3410$@devshock.com> References: <00c501ccb4f5$b89966b0$29cc3410$@devshock.com> Date: Wed, 7 Dec 2011 13:19:29 -0700 Message-ID: Subject: Re: autovacuum, any log? From: Scott Marlowe To: Anibal David Acosta Cc: pgsql-performance@postgresql.org Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201112/63 X-Sequence-Number: 45862 On Wed, Dec 7, 2011 at 8:34 AM, Anibal David Acosta wrote= : > Hello, I have a postgres 9.0.2 installation. > > Every works fine, but in some hours of day I got several timeout in my > application (my application wait X seconds before throw a timeout). > > Normally hours are not of intensive use, so I think that the autovacuum > could be the problem. > > > > Is threre any log where autovacuum write information about it self like > =93duration for each table=94 or any other relevante information. > > > > Another inline question, should I exclude bigger tables from autovacuum o= r > there are some mechanism to tell autovacuum to not run often on bigger > tables (tables with more than 400 millions of rows) More often than not not the problem will be checkpoint segments not autovacuum. log vacuum and checkpoints, and then run something like iostat in the background and keep an eye on %util to see if one or the other is slamming your IO subsystem. Default tuning for autovac is pretty conservative, to the point that it won't usually hurt your IO, but may not keep up with vaccuming, leading to table bloating.