From: | Ian Westmacott <ianw(at)intellivid(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: VACUUM and read-mostly tables |
Date: | 2005-04-05 17:47:08 |
Message-ID: | 1112723228.8115.110.camel@spectre.intellivid.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
On Tue, 2005-04-05 at 11:34, Tom Lane wrote:
> Ian Westmacott <ianw(at)intellivid(dot)com> writes:
> > 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'm not sure if you are actually confused, or are just using confusing
> terminology ... but there's a significant difference between VACUUM FULL
> (ie, vacuum and try to reclaim space) and a database-wide vacuum. I
> *think* you are using "full vacuum" to mean "database-wide vacuum" but
> it's not entirely clear.
Yes, sorry, bad choice of words. By "full vacuum" I meant
"database-wide".
> Anyway, the frequency with which you have to do database-wide vacuums to
> avoid XID wraparound is determined entirely by the rate at which you use
> up XIDs. Doing piecemeal VACUUM FREEZEs would reduce the amount of work
> that needs to be done in the eventual database-wide vacuum, but it's not
> clear that it'd be a net win given the added work of the extra VACUUM
> scans.
But potentially I could spread the cost out over time,
making it less disruptive when it occurs, right?
> Have you looked at whether you can slow down the rate of XID consumption
> (ie, by bundling operations into larger transactions)? That might be a
> more useful route to limiting the costs involved.
Yes, I'd like to explore that. Essentially what happens
now is that a number of rows are written to each of about
two dozen tables, each with a COPY FROM STDIN, and then
there are a few INSERTS and UPDATES to boot. As far as
we are concerned, all of this could be a single transaction.
My understanding is that all the inserts resulting from a
COPY are a single transaction, but is it possible to make
multiple COPYs a single transaction? Or would I have to
do them all as individual INSERTs and make the whole thing
a single transaction? Would that be more costly?
Thanks,
--Ian
From | Date | Subject | |
---|---|---|---|
Next Message | Ian Westmacott | 2005-04-05 17:56:05 | Re: VACUUM and read-mostly tables |
Previous Message | Jim C. Nasby | 2005-04-05 15:39:04 | Re: VACUUM and read-mostly tables |