X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (mx2.hub.org [200.46.204.254]) by postgresql.org (Postfix) with ESMTP id 42AB311F65B8 for ; Thu, 13 Apr 2006 02:57:07 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: -2.224 X-Spam-Level: X-Spam-Status: No, score=-2.224 required=5 tests=[BAYES_00=-2.599, HTML_30_40=0.374, HTML_MESSAGE=0.001] Received: from postgresql.org ([200.46.204.71]) by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024) with ESMTP id bzKQ7U6orUMG for ; Thu, 13 Apr 2006 02:57:03 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.clickspace.com (router2.clickspace.com [65.110.166.227]) by postgresql.org (Postfix) with ESMTP id 28CA811F65CD for ; Thu, 13 Apr 2006 02:57:01 -0300 (ADT) Received: from [10.0.1.4] ([68.147.204.179]) (authenticated user brendan@clickspace.com) by mail.clickspace.com (using TLSv1/SSLv3 with cipher RC4-SHA (128 bits)); Wed, 12 Apr 2006 23:56:56 -0600 In-Reply-To: <1d219a6f0604121353t1e997628i6e10d00bed4b2354@mail.gmail.com> References: <1d219a6f0604111602m62445e60h425c5dd59219606e@mail.gmail.com> <443C39ED.6050706@aurema.com> <1d219a6f0604120753h1cabb4c2n60f53403225dcf81@mail.gmail.com> <20060412151553.GD23855@surnet.cl> <1d219a6f0604121353t1e997628i6e10d00bed4b2354@mail.gmail.com> Mime-Version: 1.0 (Apple Message framework v749.3) Content-Type: multipart/alternative; boundary=Apple-Mail-1--903116679 Message-Id: Cc: "Anthony Ransley" , "pgsql-admin@postgresql.org" From: Brendan Duddridge Subject: Re: Autovacuum ideas Date: Wed, 12 Apr 2006 23:56:47 -0600 To: Chris Hoover X-Mailer: Apple Mail (2.749.3) X-Archive-Number: 200604/116 X-Sequence-Number: 21285 --Apple-Mail-1--903116679 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed What I'd like to see is a table exclusion list. I have a few very large history tables that are never updated or deleted, only inserts and selects. ____________________________________________________________________ Brendan Duddridge | CTO | 403-277-5591 x24 | brendan@clickspace.com ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com On Apr 12, 2006, at 2:53 PM, Chris Hoover wrote: > I love this idea. I think on initial consideration it might have a > lot of merit. You could have a setup something like: > > pg_av_grp -- table of group names > avgrpname varchar(50) -- group name > > pg_av_grp_window -- time windows when not to vacuum the group of > tables > avgrpwinrelid = pg_av_group.oid > no_run_start time - starting of exclusion block > no_run_stop time - stopping of exclusion block > no_run_day - day(s) of this exclusion > > pg_av_grp_rel -- the list of tables in a group > avgrprelid = pg_av_grp.oid > avgrptabid = pg_class.oid > > Something like this structure would allow you a lot of flexibility > in building your auto vacuum groups. You can define your groups > with the ability to have multiple windows per group. > > This would allow you to basically have rules like this: > big_group - table_a, table_b, table_f > no_run_start 08:00 > no_run_stop 17:00 > no_run_day (mon-fri) > -- don't vacuum the big group tables during the M-F work hours > > load_group - table_c, table_d, table_e > no_run_start 06:00 > no_run_stop 08:00 > no_run_day (mon-sun) > > no_run_start 18:00 > no_run_stop 20:00 > no_run_day (mon-sun) > > -- don't vacuum the main tables we load during the 2 load windows. > > Tables not in a group would be in a default group that could be > vacuumed at any time as needed. > > I'm not really a C programmer, so I don't know what the internals > would take to accomplish this, but maybe it can spur some thought > and development. > > Chris > > > > On 4/12/06, Alvaro Herrera wrote: > > I don't think autovacuum will be on by default until we have a way to > configure rush hours, maintenance windows, etc. > > The system I envision has tables grouped in some way, and maintenance > windows would operate on table groups. For example you will be > able to > include the fat table in a (possibly otherwise empty) group, and have > that group not be processed during "work hours"; the rest of the > tables > would continue to be vacuumed at all times. > > -- > Alvaro Herrera http:// > www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support > --Apple-Mail-1--903116679 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=ISO-8859-1 What I'd like to see is a table = exclusion list. I have a few very large history tables that are never = updated or deleted, only inserts and selects.

_________________________________________________________= ___________
Brendan Duddridge=A0| CTO | 403-277-5591 x24 | =A0brendan@clickspace.com =

ClickSpace = Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB = =A0T2G 0V9

http://www.clickspace.com=A0
=

On Apr 12, 2006, at 2:53 PM, Chris Hoover = wrote:

I love this idea.=A0 I think on initial consideration it = might have a lot of merit.=A0 You could have a setup something = like:

pg_av_grp -- table of group names
avgrpname varchar(50) = -- group name

pg_av_grp_window -- time windows when not to vacuum = the group of tables
avgrpwinrelid =3D = pg_av_group.oid
no_run_start time - starting of exclusion = block
no_run_stop time - stopping of exclusion block
no_run_day = <not sure of format> - day(s) of this = exclusion

pg_av_grp_rel -- the list of tables in a group =
avgrprelid =3D pg_av_grp.oid
avgrptabid =3D = pg_class.oid

Something like this structure would allow you a lot = of flexibility in building your auto vacuum groups.=A0 You can define = your groups with the ability to have multiple windows per group. =

This would allow you to basically have rules like = this:
big_group - table_a, table_b, table_f
no_run_start = 08:00
no_run_stop 17:00
no_run_day (mon-fri)
-- don't vacuum = the big group tables during the M-F work hours

load_group - = table_c, table_d, table_e
no_run_start 06:00
no_run_stop = 08:00
no_run_day (mon-sun)

no_run_start 18:00
no_run_stop = 20:00
no_run_day (mon-sun)

-- don't vacuum the main tables we = load during the 2 load windows.

Tables not in a group would be = in a default group that could be vacuumed at any time as = needed.

I'm not really a C programmer, so I don't know what the = internals would take to accomplish this, but maybe it can spur some = thought and development.

Chris



On 4/12/06, Alvaro = Herrera <alvherre@commandprompt.com&= gt; wrote:

I don't think autovacuum will be on by default until we have = a way to
configure rush hours, maintenance windows, etc.

The = system I envision has tables grouped in some way, and = maintenance
windows would operate on table groups.=A0=A0For example = you will be able to
include the fat table in a (possibly otherwise = empty) group, and have
that group not be processed during "work = hours"; the rest of the tables
would continue to be vacuumed at all = times.

--
Alvaro Herrera=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 http://www.CommandPrompt.com/PostgreSQL Replication, Consulting, Custom Development, 24x7 = support


= --Apple-Mail-1--903116679--