From: Ian Westmacott <ianw@intellivid.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@postgresql.org
Subject: Re: VACUUM and read-mostly tables
Date: Tue, 05 Apr 2005 11:13:10 -0400
Message-ID: <1112713990.8115.83.camel@spectre.intellivid.com> (raw)
In-Reply-To: <10364.1112683320@sss.pgh.pa.us>
References: <000201c53993$b07bb440$76d81e42@hsd1.ma.comcast.net>
<10364.1112683320@sss.pgh.pa.us>
On Tue, 2005-04-05 at 02:42, Tom Lane wrote:
> "Ian Westmacott" <ianw@intellivid.com> writes:
> > The problem is that we are writing rows every 1/15 second, 24x7. There
> > is no down time. I'm wondering if there is any way to avoid vacuuming
> > the old tables over and over. The documentation seems to indicate that
> > a full vacuum is needed to avoid XID wrap-around. Can vacuum freeze
> > help me?
>
> VACUUM FULL isn't really relevant. VACUUM FREEZE on a particular table
> should "fix" that table permanently, as long as you don't make any more
> changes to it. Keep in mind though that you still have to vacuum the
> system catalogs often enough to avoid wraparound in them. The real risk
> here is in overlooking any one table. You should probably use some kind
> of automated vacuum driver ... have you looked at pg_autovacuum?
We did look at pg_autovacuum. We found the cost of row-
level statistics too high to be workable. Looking at the
pg_autovacuum code, it appears that a full vacuum is
performed whenever the XIDs get old enough. The problem
is that it is unpredictable when this will occur.
So we have started work on our own automated driver,
primarily for the analyze side of things at the moment,
but I would like to extend it to vacuum as well. For
example, we could update the row-level statistics on a
less frequent basis than every insert.
But the question is whether vacuum freezing tables will
help me reduce the frequency of a full vacuum, or reduce
its cost when we do it? That is, if more transactions
are frozen, will a full vacuum be more efficient
(primarily in the I/O)?
> I believe Fujitsu is looking into what it'd take to make a variant
> Postgres with 64-bit XIDs. This'd probably imply also expanding CIDs,
> OIDs, and some other things, so the space penalty is not to be sneezed
> at ... but it might be worth it for installations like yours.
Thanks for the pointer, I'll take a look.
--Ian
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: ianw@intellivid.com, tgl@sss.pgh.pa.us
Subject: Re: VACUUM and read-mostly tables
In-Reply-To: <1112713990.8115.83.camel@spectre.intellivid.com>
References: <000201c53993$b07bb440$76d81e42@hsd1.ma.comcast.net> <10364.1112683320@sss.pgh.pa.us> <1112713990.8115.83.camel@spectre.intellivid.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox