From aa@devshock.com Mon Dec 12 14:26:01 2011 Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 211586E08E2 for ; Mon, 12 Dec 2011 10:26:01 -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 1Ra6pN-0004Lf-AP for pgsql-performance@postgresql.org; Mon, 12 Dec 2011 14:26:01 +0000 Received: by ghbf1 with SMTP id f1so1031935ghb.19 for ; Mon, 12 Dec 2011 06:25:48 -0800 (PST) Received: by 10.236.173.131 with SMTP id v3mr26070190yhl.112.1323699948605; Mon, 12 Dec 2011 06:25:48 -0800 (PST) Received: from AnibalVAIO ([200.85.32.68]) by mx.google.com with ESMTPS id 32sm50671588ant.12.2011.12.12.06.25.45 (version=TLSv1/SSLv3 cipher=OTHER); Mon, 12 Dec 2011 06:25:47 -0800 (PST) From: "Anibal David Acosta" To: Subject: autovacuum, exclude table Date: Mon, 12 Dec 2011 11:25:43 -0300 Message-ID: <00f501ccb8d9$f07dc4d0$d1794e70$@devshock.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00F6_01CCB8C0.CB317730" X-Mailer: Microsoft Outlook 14.0 Thread-Index: Acy42TEV8ee6/+4QQFKfYl2lMw+Rlg== Content-Language: es-py X-Pg-Spam-Score: -0.7 (/) X-Archive-Number: 201112/91 X-Sequence-Number: 45890 This is a multipart message in MIME format. ------=_NextPart_000_00F6_01CCB8C0.CB317730 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit I have a couple of tables with about 400millions of records increasing about 5 millions per day. I think that disabling autovac over those tables, and enabling daily manual vacuum (in some idle hour) will be better. I am right? Is possible to exclude autovacuum over some tables? Thanks! Anibal ------=_NextPart_000_00F6_01CCB8C0.CB317730 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

I have a = couple of tables with about 400millions of records increasing about 5 = millions per day.

 

I think that = disabling autovac over those tables, and enabling daily manual vacuum = (in some idle hour) will be better.

 

I am = right?

 

Is possible to exclude autovacuum over some = tables?

 

Thanks!

 

Anibal

------=_NextPart_000_00F6_01CCB8C0.CB317730-- From ringerc@ringerc.id.au Mon Dec 12 14:45:41 2011 Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id D7D686E08E5 for ; Mon, 12 Dec 2011 10:45:41 -0400 (AST) Received: from mail-iy0-f174.google.com ([209.85.210.174]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Ra78M-0000cs-Sj for pgsql-performance@postgresql.org; Mon, 12 Dec 2011 14:45:41 +0000 Received: by iaeh11 with SMTP id h11so3288611iae.19 for ; Mon, 12 Dec 2011 06:45:24 -0800 (PST) MIME-Version: 1.0 Received: by 10.42.158.132 with SMTP id h4mr13118911icx.0.1323701124050; Mon, 12 Dec 2011 06:45:24 -0800 (PST) Received: by 10.42.77.69 with HTTP; Mon, 12 Dec 2011 06:45:24 -0800 (PST) X-Originating-IP: [124.169.254.28] Received: by 10.42.77.69 with HTTP; Mon, 12 Dec 2011 06:45:24 -0800 (PST) In-Reply-To: <00f501ccb8d9$f07dc4d0$d1794e70$@devshock.com> References: <00f501ccb8d9$f07dc4d0$d1794e70$@devshock.com> Date: Mon, 12 Dec 2011 22:45:24 +0800 Message-ID: Subject: Re: autovacuum, exclude table From: Craig Ringer To: Anibal David Acosta Cc: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=90e6ba6e889849b07f04b3e62f5d X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201112/92 X-Sequence-Number: 45891 --90e6ba6e889849b07f04b3e62f5d Content-Type: text/plain; charset=UTF-8 Top-posting because this is context free: You need to provide more info for anybody to help you. Are the tables append-only or are deletes/updates also performed? Also this: http://wiki.postgresql.org/wiki/Guide_to_reporting_problems On Dec 12, 2011 10:26 PM, "Anibal David Acosta" wrote: > I have a couple of tables with about 400millions of records increasing > about 5 millions per day.**** > > ** ** > > I think that disabling autovac over those tables, and enabling daily > manual vacuum (in some idle hour) will be better.**** > > ** ** > > I am right?**** > > ** ** > > Is possible to exclude autovacuum over some tables?**** > > ** ** > > Thanks!**** > > ** ** > > Anibal**** > --90e6ba6e889849b07f04b3e62f5d Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

Top-posting because this is context free:

You need to provide more info for anybody to help you. Are the tables ap= pend-only or are deletes/updates also performed? Also this:

http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

On Dec 12, 2011 10:26 PM, "Anibal David Aco= sta" <aa@devshock.com> wr= ote:

I have a couple of tables with about 400millions of records increasing = about 5 millions per day.

= =C2=A0

I think that disabling autovac over those tables, an= d enabling daily manual vacuum (in some idle hour) will be better.

=C2=A0

I am right?

=C2=A0

Is po= ssible to exclude autovacuum over some tables?

=C2=A0

Thanks!

=C2=A0

Anibal

--90e6ba6e889849b07f04b3e62f5d-- From aa@devshock.com Mon Dec 12 14:55:20 2011 Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 9F2A16E08E4 for ; Mon, 12 Dec 2011 10:55:20 -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 1Ra7Hj-0004lz-J7 for pgsql-performance@postgresql.org; Mon, 12 Dec 2011 14:55:20 +0000 Received: by ghbf1 with SMTP id f1so1059047ghb.19 for ; Mon, 12 Dec 2011 06:55:07 -0800 (PST) Received: by 10.236.193.70 with SMTP id j46mr25605106yhn.108.1323701705476; Mon, 12 Dec 2011 06:55:05 -0800 (PST) Received: from AnibalVAIO ([200.85.32.68]) by mx.google.com with ESMTPS id d5sm31000950yhl.19.2011.12.12.06.55.02 (version=TLSv1/SSLv3 cipher=OTHER); Mon, 12 Dec 2011 06:55:04 -0800 (PST) From: "Anibal David Acosta" To: "'Craig Ringer'" Cc: References: <00f501ccb8d9$f07dc4d0$d1794e70$@devshock.com> In-Reply-To: Subject: Re: autovacuum, exclude table Date: Mon, 12 Dec 2011 11:55:00 -0300 Message-ID: <010a01ccb8de$07c0ad20$17420760$@devshock.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_010B_01CCB8C4.E27549E0" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQGkUYcacb18vEnKcZx6PE9iGoSoXgHRkFW0lhqKykA= Content-Language: es-py X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201112/93 X-Sequence-Number: 45892 This is a multipart message in MIME format. ------=_NextPart_000_010B_01CCB8C4.E27549E0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable The postgres version is 9.0.4 on a Windows Server 2008 (planning to = upgrade to 9.1) Tables has insert (in bulk every 3 minutes) and delete one per day = (delete records older than XX days) =20 There are not much additional relevant information. =20 Thanks! =20 =20 De: Craig Ringer [mailto:ringerc@ringerc.id.au]=20 Enviado el: lunes, 12 de diciembre de 2011 11:45 a.m. Para: Anibal David Acosta CC: pgsql-performance@postgresql.org Asunto: Re: [PERFORM] autovacuum, exclude table =20 Top-posting because this is context free: You need to provide more info for anybody to help you. Are the tables = append-only or are deletes/updates also performed? Also this: http://wiki.postgresql.org/wiki/Guide_to_reporting_problems On Dec 12, 2011 10:26 PM, "Anibal David Acosta" wrote: I have a couple of tables with about 400millions of records increasing = about 5 millions per day. =20 I think that disabling autovac over those tables, and enabling daily = manual vacuum (in some idle hour) will be better. =20 I am right? =20 Is possible to exclude autovacuum over some tables? =20 Thanks! =20 Anibal ------=_NextPart_000_010B_01CCB8C4.E27549E0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

The postgres version is 9.0.4 on a Windows Server 2008 (planning to = upgrade to 9.1)

Tables has insert (in bulk every 3 minutes) and delete one per day = (delete records older than XX days)

 

There are not much additional relevant = information.

 

Thanks!

 

 

De: Craig = Ringer [mailto:ringerc@ringerc.id.au]
Enviado el: lunes, 12 = de diciembre de 2011 11:45 a.m.
Para: Anibal David = Acosta
CC: pgsql-performance@postgresql.org
Asunto: = Re: [PERFORM] autovacuum, exclude table

 

Top-posting because this is = context free:

You need to provide more info for anybody = to help you. Are the tables append-only or are deletes/updates also = performed? Also this:

http= ://wiki.postgresql.org/wiki/Guide_to_reporting_problems

On Dec 12, 2011 10:26 PM, "Anibal David = Acosta" <aa@devshock.com> = wrote:

I have a = couple of tables with about 400millions of records increasing about 5 = millions per day.

 <= /o:p>

I think = that disabling autovac over those tables, and enabling daily manual = vacuum (in some idle hour) will be better.

 <= /o:p>

I am = right?

 <= /o:p>

Is possible = to exclude autovacuum over some tables?

 <= /o:p>

Thanks!=

 <= /o:p>

Anibal<= /o:p>

------=_NextPart_000_010B_01CCB8C4.E27549E0-- From heikki.linnakangas@enterprisedb.com Mon Dec 12 15:16:27 2011 Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id CF34E6E08E2 for ; Mon, 12 Dec 2011 11:16:27 -0400 (AST) Received: from exprod7og117.obsmtp.com ([64.18.2.6]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Ra7c9-0005BM-R8 for pgsql-performance@postgresql.org; Mon, 12 Dec 2011 15:16:27 +0000 Received: from mail-ee0-f45.google.com ([74.125.83.45]) (using TLSv1) by exprod7ob117.postini.com ([64.18.6.12]) with SMTP ID DSNKTuYavN9PODt+ziRmr5kk/XYNjWSD+01H@postini.com; Mon, 12 Dec 2011 07:16:25 PST Received: by eekb45 with SMTP id b45so1751682eek.32 for ; Mon, 12 Dec 2011 07:16:11 -0800 (PST) Received: by 10.213.19.72 with SMTP id z8mr3643554eba.35.1323702971330; Mon, 12 Dec 2011 07:16:11 -0800 (PST) Received: from [192.168.1.101] (dsl-hkibrasgw2-ff6cc300-200.dhcp.inet.fi. [88.195.108.200]) by mx.google.com with ESMTPS id z54sm76318028eeh.5.2011.12.12.07.16.09 (version=TLSv1/SSLv3 cipher=OTHER); Mon, 12 Dec 2011 07:16:10 -0800 (PST) Message-ID: <4EE61AB8.1010202@enterprisedb.com> Date: Mon, 12 Dec 2011 17:16:08 +0200 From: Heikki Linnakangas User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; fi-FI; rv:1.9.2.24) Gecko/20111114 Icedove/3.1.16 MIME-Version: 1.0 To: Anibal David Acosta CC: pgsql-performance@postgresql.org Subject: Re: autovacuum, exclude table References: <00f501ccb8d9$f07dc4d0$d1794e70$@devshock.com> In-Reply-To: <00f501ccb8d9$f07dc4d0$d1794e70$@devshock.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -4.2 (----) X-Archive-Number: 201112/94 X-Sequence-Number: 45893 On 12.12.2011 16:25, Anibal David Acosta wrote: > I have a couple of tables with about 400millions of records increasing about > 5 millions per day. > > I think that disabling autovac over those tables, and enabling daily manual > vacuum (in some idle hour) will be better. > > I am right? Possibly. If the system is otherwise idle, it sounds sensible to do routine maintenance at that time. > Is possible to exclude autovacuum over some tables? Sure, see http://www.postgresql.org/docs/9.1/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS ALTER TABLE foo SET (autovacuum_enabled=false, toast.autovacuum_enabled = false); It might be better, though, to let autovacuum enabled, and just do the additional manual VACUUM in the idle period. If the daily manual VACUUM is enough to keep the bloat within the autovacuum thresholds, autovacuum will never kick in. If it's not enough, then you probably want autovacuum to run. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com From tgl@sss.pgh.pa.us Mon Dec 12 15:26:26 2011 Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id B998F6E08E2 for ; Mon, 12 Dec 2011 11:26:26 -0400 (AST) Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Ra7lo-0001SA-Kw for pgsql-performance@postgresql.org; Mon, 12 Dec 2011 15:26:26 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id pBCFQAAR018793; Mon, 12 Dec 2011 10:26:10 -0500 (EST) To: "Anibal David Acosta" cc: "'Craig Ringer'" , pgsql-performance@postgresql.org Subject: Re: autovacuum, exclude table In-reply-to: <010a01ccb8de$07c0ad20$17420760$@devshock.com> References: <00f501ccb8d9$f07dc4d0$d1794e70$@devshock.com> <010a01ccb8de$07c0ad20$17420760$@devshock.com> Comments: In-reply-to "Anibal David Acosta" message dated "Mon, 12 Dec 2011 11:55:00 -0300" Date: Mon, 12 Dec 2011 10:26:10 -0500 Message-ID: <18792.1323703570@sss.pgh.pa.us> From: Tom Lane X-Pg-Spam-Score: -2.5 (--) X-Archive-Number: 201112/95 X-Sequence-Number: 45894 "Anibal David Acosta" writes: > Tables has insert (in bulk every 3 minutes) and delete one per day (delete records older than XX days) No updates at all, just inserts and a daily delete? If so, you're wasting your time even thinking about suppressing autovacuum, because it won't fire on this table except after the daily delete, which is exactly when you need it to. Also, if you suppress autovacuum you also suppress autoanalyze, which is something that *will* fire after large inserts, and probably should. At least, this usage pattern doesn't suggest to me that it's clearly safe to run without up-to-date stats. Right offhand, I'm not convinced either that you have a problem, or that turning off autovacuum would fix it. regards, tom lane