Here's another cut at this patch. This is mainly about the
infrastructure to pass the data around in autovacuum; the proposed
formulas probably need lot of work.
We still have two terms in autovacuum priority, the first one considers
dead tuples and the second one considers wraparound limit. I
On 1/31/13 2:18 PM, Alvaro Herrera wrote:
My intention was to apply a Nasby correction to Browne Strength and call
the resulting function Browne' (Browne prime). Does that sound better?
I suggest painting that bikeshed Browneby. :P
--
Sent via pgsql-hackers mailing list
On Sat, Feb 2, 2013 at 5:25 AM, Andres Freund and...@2ndquadrant.com wrote:
On 2013-02-01 15:09:34 -0800, Jeff Janes wrote:
On Fri, Feb 1, 2013 at 2:34 PM, Andres Freund and...@2ndquadrant.com wrote:
On 2013-02-01 14:05:46 -0800, Jeff Janes wrote:
As far as I can tell this bug kicks in when
Jeff Janes jeff.ja...@gmail.com writes:
On Sat, Feb 2, 2013 at 5:25 AM, Andres Freund and...@2ndquadrant.com wrote:
If the release notes are not already baked in, I would suggest this wording:
+ The main consequence of this mistake is that it
+ caused full-table vacuuming scans to
Andres Freund and...@2ndquadrant.com writes:
It obviously needs more polish:
- I opted for using the 64bit representation of xids, seems to be better
in a log which very well might be looked at only after some
wraparounds
- exporting 'txid' from adt/txid.c is pretty ugly. I don't like
On 2013-02-03 11:17:42 -0500, Tom Lane wrote:
Andres Freund and...@2ndquadrant.com writes:
It obviously needs more polish:
- I opted for using the 64bit representation of xids, seems to be better
in a log which very well might be looked at only after some
wraparounds
- exporting
Andres Freund and...@2ndquadrant.com wrote:
On 2013-02-01 15:09:34 -0800, Jeff Janes wrote:
Since freeze_min_age was mistakenly being used, the limit
would be 50 million in the past (rather than 150 million) under
defaults. But since the last full-table vacuum, whenever that was,
used
Andres Freund and...@2ndquadrant.com writes:
On 2013-02-03 11:17:42 -0500, Tom Lane wrote:
-1 on using txids here. If memory serves, we have had exactly this
discussion before and rejected spreading those into other parts
of the system. That gets rid of three of your problems right there,
On 2013-02-03 13:26:25 -0500, Tom Lane wrote:
Andres Freund and...@2ndquadrant.com writes:
On 2013-02-03 11:17:42 -0500, Tom Lane wrote:
-1 on using txids here. If memory serves, we have had exactly this
discussion before and rejected spreading those into other parts
of the system. That
On Sun, Feb 3, 2013 at 9:25 AM, Kevin Grittner kgri...@ymail.com wrote:
I was able to confirm two cases where this was a consequence of the
lazy truncate logic which Jan recently fixed, but there are clearly
other problems which I didn't have much of a grasp on prior to this
thread. The only
On 2013-02-01 15:09:34 -0800, Jeff Janes wrote:
On Fri, Feb 1, 2013 at 2:34 PM, Andres Freund and...@2ndquadrant.com wrote:
On 2013-02-01 14:05:46 -0800, Jeff Janes wrote:
As far as I can tell this bug kicks in when your cluster gets to be
older than freeze_min_age, and then lasts forever
On 2013-02-01 16:59:52 -0500, Robert Haas wrote:
I don't think I really understand the origin of the formula, so
perhaps if someone would try to characterize why it seems to behave
reasonably that would be helpful (at least to me).
f(deadtuples, relpages, age) =
deadtuples/relpages + e
On Fri, Feb 1, 2013 at 6:09 PM, Jeff Janes jeff.ja...@gmail.com wrote:
As an aside, it does seem like log_autovacuum_min_duration=0 should
log whether a scan_all was done, and if so what relfrozenxid got set
to.
That would be nifty.
[1] I don't know why it is that a scan_all vacuum with a
On Sat, Feb 2, 2013 at 8:41 AM, Andres Freund and...@2ndquadrant.com wrote:
- It's probably important to have a formula where we can be sure that
the wrap-around term will eventually dominate the dead-tuple term,
with enough time to spare to make sure nothing really bad happens; on
the other
On 2013-02-02 11:25:01 -0500, Robert Haas wrote:
On Sat, Feb 2, 2013 at 8:41 AM, Andres Freund and...@2ndquadrant.com wrote:
- It's probably important to have a formula where we can be sure that
the wrap-around term will eventually dominate the dead-tuple term,
with enough time to spare to
On Sat, Feb 2, 2013 at 1:49 PM, Andres Freund and...@2ndquadrant.com wrote:
You're right, this doesn't work superbly well, especially for
insert-only tables... But imo the place to fix it is not the
priorization logic but relation_needs_vacanalyze, since fixing it in
priorization won't prevent
On 2013-02-02 14:54:10 -0500, Robert Haas wrote:
On Sat, Feb 2, 2013 at 1:49 PM, Andres Freund and...@2ndquadrant.com wrote:
I think scheduling a table for a partial vacuum every min_freeze * 2
xids, even if its insert only, would go a long way of reducing the
impact of full-table vacuums.
On 2013-02-01 15:09:34 -0800, Jeff Janes wrote:
As an aside, it does seem like log_autovacuum_min_duration=0 should
log whether a scan_all was done, and if so what relfrozenxid got set
to. But looking at where the log message is generated, I don't know
where to retrieve that info.
What about
On 2013-02-03 02:40:04 +0100, Andres Freund wrote:
On 2013-02-01 15:09:34 -0800, Jeff Janes wrote:
As an aside, it does seem like log_autovacuum_min_duration=0 should
log whether a scan_all was done, and if so what relfrozenxid got set
to. But looking at where the log message is generated,
On Sat, Feb 2, 2013 at 2:54 PM, Robert Haas robertmh...@gmail.com wrote:
On Sat, Feb 2, 2013 at 1:49 PM, Andres Freund and...@2ndquadrant.com wrote:
You're right, this doesn't work superbly well, especially for
insert-only tables... But imo the place to fix it is not the
priorization logic but
On 03/02/13 15:08, Christopher Browne wrote:
On Sat, Feb 2, 2013 at 2:54 PM, Robert Haas robertmh...@gmail.com wrote:
On Sat, Feb 2, 2013 at 1:49 PM, Andres Freund and...@2ndquadrant.com wrote:
You're right, this doesn't work superbly well, especially for
insert-only tables... But imo the
Andres Freund wrote:
If youre careful you can also notice that there is an interesting typo
in the freeze table computation. Namely it uses freeze_min_age instead
of freeze_table_age. Which probably explains why I had so bad
performance results with lowering vacuum_freeze_min_age, it
On Thu, Jan 31, 2013 at 3:18 PM, Alvaro Herrera
alvhe...@2ndquadrant.com wrote:
My intention was to apply a Nasby correction to Browne Strength and call
the resulting function Browne' (Browne prime). Does that sound better?
/me rests head in hands. I'm not halfway clever enough to hang with
On Wed, Jan 30, 2013 at 6:55 AM, Andres Freund and...@2ndquadrant.com wrote:
c.f.
vacuum_set_xid_limits:
/*
* Determine the table freeze age to use: as specified by the
caller,
* or vacuum_freeze_table_age, but in any case not more than
On 2013-02-01 14:05:46 -0800, Jeff Janes wrote:
On Wed, Jan 30, 2013 at 6:55 AM, Andres Freund and...@2ndquadrant.com wrote:
c.f.
vacuum_set_xid_limits:
/*
* Determine the table freeze age to use: as specified by
the caller,
* or
On Fri, Feb 1, 2013 at 4:59 PM, Robert Haas robertmh...@gmail.com wrote:
On Thu, Jan 31, 2013 at 3:18 PM, Alvaro Herrera
alvhe...@2ndquadrant.com wrote:
My intention was to apply a Nasby correction to Browne Strength and call
the resulting function Browne' (Browne prime). Does that sound
Christopher Browne cbbro...@gmail.com writes:
I picked values that I knew could be easily grabbed, and we don't
have an immediate tuples-per-page estimate on pg_class.
Er, what? reltuples/relpages is exactly that estimate --- in fact,
it's only because of historical accident that we don't
On Fri, Feb 1, 2013 at 2:34 PM, Andres Freund and...@2ndquadrant.com wrote:
On 2013-02-01 14:05:46 -0800, Jeff Janes wrote:
As far as I can tell this bug kicks in when your cluster gets to be
older than freeze_min_age, and then lasts forever after. After that
point pretty much every
On Monday, January 28, 2013, Kevin Grittner wrote:
IMO, anything which changes an anti-wraparound vacuum of a
bulk-loaded table from read the entire table and rewrite nearly
the complete table with WAL-logging to rewriting a smaller portion
of the table with WAL-logging is an improvement.
Christopher Browne escribió:
On Fri, Jan 25, 2013 at 12:00 PM, Andres Freund and...@2ndquadrant.com
wrote:
I'd be inclined to do something a bit more sophisticated than just
age(relfrozenxid) for wraparound; I'd be inclined to kick off large tables'
wraparound vacuums earlier than those for
Alvaro Herrera escribió:
Okay, here's a patch along these lines. I haven't considered Jim's
suggestion downthread about discounting dead tuples from relpages; maybe
we can do that by subtracting the pages attributed to dead ones,
estimating via tuple density (reltuples/relpages).
Patch
On Thu, Jan 31, 2013 at 2:40 PM, Alvaro Herrera
alvhe...@2ndquadrant.com wrote:
Alvaro Herrera escribió:
Okay, here's a patch along these lines. I haven't considered Jim's
suggestion downthread about discounting dead tuples from relpages; maybe
we can do that by subtracting the pages
On Thu, Jan 31, 2013 at 2:36 PM, Alvaro Herrera
alvhe...@2ndquadrant.com wrote:
Instead, what I propose (and is not really in the patch), as a
backpatchable item, is an approach in which the functions to compute
each rel's Browne strength and sort are hooks. Normal behavior is not
to sort at
Alvaro Herrera alvhe...@2ndquadrant.com writes:
Instead, what I propose (and is not really in the patch), as a
backpatchable item, is an approach in which the functions to compute
each rel's Browne strength and sort are hooks. Normal behavior is not
to sort at all, as currently, and sites
Tom Lane escribió:
Alvaro Herrera alvhe...@2ndquadrant.com writes:
Instead, what I propose (and is not really in the patch), as a
backpatchable item, is an approach in which the functions to compute
each rel's Browne strength and sort are hooks. Normal behavior is not
to sort at all, as
Robert Haas escribió:
On Thu, Jan 31, 2013 at 2:40 PM, Alvaro Herrera
alvhe...@2ndquadrant.com wrote:
Alvaro Herrera escribió:
Okay, here's a patch along these lines. I haven't considered Jim's
suggestion downthread about discounting dead tuples from relpages; maybe
we can do that by
On Thu, Jan 31, 2013 at 3:18 PM, Alvaro Herrera
alvhe...@2ndquadrant.com wrote:
Robert Haas escribió:
On Thu, Jan 31, 2013 at 2:40 PM, Alvaro Herrera
alvhe...@2ndquadrant.com wrote:
Alvaro Herrera escribió:
Okay, here's a patch along these lines. I haven't considered Jim's
suggestion
On 2013-01-29 16:09:52 +1100, Josh Berkus wrote:
I have to admit, I fail to see why this is a good idea. There isn't much
of an efficiency bonus in freezing early (due to hint bits) and vacuums
over vacuum_freeze_table_age are considerably more expensive as they
have to scan the whole
Andres Freund and...@2ndquadrant.com wrote:
On 2013-01-29 16:09:52 +1100, Josh Berkus wrote:
I have to admit, I fail to see why this is a good idea. There
isn't much of an efficiency bonus in freezing early (due to
hint bits) and vacuums over vacuum_freeze_table_age are
considerably more
On 2013-01-30 05:39:29 -0800, Kevin Grittner wrote:
Andres Freund and...@2ndquadrant.com wrote:
On 2013-01-29 16:09:52 +1100, Josh Berkus wrote:
I have to admit, I fail to see why this is a good idea. There
isn't much of an efficiency bonus in freezing early (due to
hint bits) and
On 2013-01-30 14:58:24 +0100, Andres Freund wrote:
So reducing vacuum_freeze_min_age not only helps minimize the
writes that are needed when autovacuum needs to scan the entire
heap, but also decreases the frequency of those full-table scans.
But it increases the amount of pages that are
On Mon, Jan 28, 2013 at 9:09 PM, Josh Berkus j...@agliodbs.com wrote:
Let's do this by example. TableA is a large table which receives an
almost constant stream of individual row updates, inserts, and deletes.
DEFAULTS:
XID 1: First rows in TableA are updated.
XID 200m: Anti-wraparound
On Wed, Jan 30, 2013 at 5:39 AM, Kevin Grittner kgri...@ymail.com wrote:
Andres Freund and...@2ndquadrant.com wrote:
Don't think I did. I was talking about vacuum_freeze_table_age
because that influences the amount of full-table scans
Not any more than vacuum_freeze_min_age does.
There is a
Andres Freund and...@2ndquadrant.com wrote:
I can't imagine that anybody with a large database ran pg
successfully with a small freeze_min_age due to this.
I can't speak to this from personal experience, because at
Wisconsin Courts we found ourselves best served by running a
database VACUUM
On 2013-01-30 10:21:07 -0800, Kevin Grittner wrote:
It seems to be broken since the initial introduction of
freeze_table_age in 6587818542e79012276dcfedb2f97e3522ee5e9b.
Trivial patch attached.
I didn't see a patch attached.
The archive has it, so I for once haven't forgotten sending
Kevin Grittner kgri...@ymail.com wrote:
To: Andres Freund and...@2ndquadrant.com
Trivial patch attached.
I didn't see a patch attached.
Never mind; I was looking in the wrong spot. (I just switched
email providers again because the last one couldn't seem to get the
email headers right for
Jeff Janes jeff.ja...@gmail.com wrote:
Kevin Grittner kgri...@ymail.com wrote:
So reducing vacuum_freeze_min_age not only helps minimize the
writes that are needed when autovacuum needs to scan the entire
heap,
How does it do that? If the tuple doesn't need to frozen now
because it was
On 1/25/13 11:56 AM, Christopher Browne wrote:
With a little bit of noodling around, here's a thought for a joint function
that I*think* has reasonably common scales:
f(deadtuples, relpages, age) =
deadtuples/relpages + e ^ (age*ln(relpages)/2^32)
Be careful with dead/relpages, because
Jim Nasby j...@nasby.net wrote:
the only reason to freeze is XID wrap
The conclusions you draw seem to be based on a slightly different
premise than stated here; the conclusions are only supported by the
assumption that the only reason to freeze at any particular moment
is to assure that all
On Wed, Jan 30, 2013 at 4:05 PM, Jim Nasby j...@nasby.net wrote:
On 1/25/13 11:56 AM, Christopher Browne wrote:
With a little bit of noodling around, here's a thought for a joint
function
that I*think* has reasonably common scales:
f(deadtuples, relpages, age) =
deadtuples/relpages +
On 1/30/13 3:28 PM, Kevin Grittner wrote:
Jim Nasby j...@nasby.net wrote:
then the *ideal* time to start a freeze vacuum on a table is so
that the vacuum would end *exactly* as we were about to hit XID
wrap.
For a tuple which you know is going to survive long enough to be
frozen, the
On 1/30/13 4:37 PM, Christopher Browne wrote:
From a more practical standpoint, I think it would be extremely useful to
have a metric that showed how quickly a table churned. Something like dead
tuples per time period. Comparing that to the non-bloated table size should
give a very strong
You can get nearly all the benefits of your sane settings just by
increasing autovacuum_freeze_max_age and leaving vacuum_freeze_min_age
alone. (Assuming the table doesn't get vacuumed for other reasons)
Correct, it's the ratio that matters.
--
Josh Berkus
PostgreSQL Experts Inc.
So I think we need to sort by age(relfrozenxid) in tables that are over
the anti-wraparound limit. Given your code that doesn't seem to be that
hard?
I might also suggest that we think about changing the defaults for
wraparound vacuum behavior. Partcularly, the fact that
On 2013-01-29 00:11:12 +1100, Josh Berkus wrote:
So I think we need to sort by age(relfrozenxid) in tables that are over
the anti-wraparound limit. Given your code that doesn't seem to be that
hard?
I might also suggest that we think about changing the defaults for
wraparound vacuum
Andres Freund and...@2ndquadrant.com wrote:
On 2013-01-29 00:11:12 +1100, Josh Berkus wrote:
So I think we need to sort by age(relfrozenxid) in tables that
are over the anti-wraparound limit. Given your code that
doesn't seem to be that hard?
I might also suggest that we think about
On 2013-01-28 08:15:29 -0800, Kevin Grittner wrote:
Andres Freund and...@2ndquadrant.com wrote:
On 2013-01-29 00:11:12 +1100, Josh Berkus wrote:
So I think we need to sort by age(relfrozenxid) in tables that
are over the anti-wraparound limit. Given your code that
doesn't seem to be
On Sun, Jan 27, 2013 at 2:17 PM, Jeff Janes jeff.ja...@gmail.com wrote:
On Fri, Jan 25, 2013 at 9:19 AM, Robert Haas robertmh...@gmail.com wrote:
I think that to do this right, we need to consider not only the status
quo but the trajectory. For example, suppose we have two tables to
process,
On Mon, Jan 28, 2013 at 8:11 AM, Josh Berkus j...@agliodbs.com wrote:
So I think we need to sort by age(relfrozenxid) in tables that are over
the anti-wraparound limit. Given your code that doesn't seem to be that
hard?
I might also suggest that we think about changing the defaults for
I have to admit, I fail to see why this is a good idea. There isn't much
of an efficiency bonus in freezing early (due to hint bits) and vacuums
over vacuum_freeze_table_age are considerably more expensive as they
have to scan the whole heap instead of using the visibilitymap. And if
you
On 25 January 2013 17:19, Robert Haas robertmh...@gmail.com wrote:
We
could easily run across a system where pg_class order happens to be
better than anything else we come up with.
I think you should read that back to yourself and see if you still
feel the word easily applies here.
I agree
On Sun, Jan 27, 2013 at 4:17 AM, Simon Riggs si...@2ndquadrant.com wrote:
On 25 January 2013 17:19, Robert Haas robertmh...@gmail.com wrote:
We
could easily run across a system where pg_class order happens to be
better than anything else we come up with.
I think you should read that back to
On Fri, Jan 25, 2013 at 9:19 AM, Robert Haas robertmh...@gmail.com wrote:
I think that to do this right, we need to consider not only the status
quo but the trajectory. For example, suppose we have two tables to
process, one of which needs a wraparound vacuum and the other one of
which needs
On Fri, Jan 25, 2013 at 10:02 AM, Robert Haas robertmh...@gmail.com wrote:
I'm worried about the case of a very, very frequently updated table
getting put ahead of a table that needs a wraparound vacuum, but only
just. It doesn't sit well with me to think that the priority of that
goes from
On Thu, Jan 24, 2013 at 1:57 PM, Alvaro Herrera
alvhe...@2ndquadrant.com wrote:
Hi,
I have a bug pending that autovacuum fails to give priority to
for-wraparound tables. When xid consumption rate is high and dead tuple
creation is also high, it is possible that some tables are waiting for
On 27 January 2013 17:11, Robert Haas robertmh...@gmail.com wrote:
On Sun, Jan 27, 2013 at 4:17 AM, Simon Riggs si...@2ndquadrant.com wrote:
On 25 January 2013 17:19, Robert Haas robertmh...@gmail.com wrote:
We
could easily run across a system where pg_class order happens to be
better than
Tom Lane escribió:
As posted, what we've got here is sorting on a boolean condition, with
the behavior within each group totally up to the whims of qsort(). That
seems especially dangerous since the priority order is mostly undefined.
I was a bit surprised that Alvaro didn't propose
On 1/25/13 10:29 AM, Alvaro Herrera wrote:
And I do want to get something back-patchable.
Autovacuum has existed for N years and nobody complained about this
until just now, so I don't see a strong justification for backpatching.
Or is this a regression from an earlier release?
In general, I
Peter Eisentraut escribió:
On 1/25/13 10:29 AM, Alvaro Herrera wrote:
And I do want to get something back-patchable.
Autovacuum has existed for N years and nobody complained about this
until just now, so I don't see a strong justification for backpatching.
I disagree about people not
Alvaro Herrera alvhe...@2ndquadrant.com writes:
Peter Eisentraut escribió:
Autovacuum has existed for N years and nobody complained about this
until just now, so I don't see a strong justification for backpatching.
I disagree about people not complaining. Maybe the complaints have not
been
Alvaro Herrera alvhe...@2ndquadrant.com writes:
So if we're to discuss this, here's what I had in mind:
1. for-wraparound tables always go first; oldest age(relfrozenxid) are
sorted earlier. For tables of the same age, consider size as below.
It seems unlikely that age(relfrozenxid) will be
On 2013-01-25 11:51:33 -0500, Tom Lane wrote:
Alvaro Herrera alvhe...@2ndquadrant.com writes:
2. for other tables, consider floor(log(size)). This makes tables of
sizes in the same ballpark be considered together.
3. For tables of similar size, consider
(n_dead_tuples - threshold) /
On Fri, Jan 25, 2013 at 11:51 AM, Tom Lane t...@sss.pgh.pa.us wrote:
The floor(log(size)) part seems like it will have rather arbitrary
behavioral shifts when a table grows just past a log boundary. Also,
I'm not exactly sure whether you're proposing smaller tables first or
bigger tables
On 2013-01-25 12:19:25 -0500, Robert Haas wrote:
On Fri, Jan 25, 2013 at 11:51 AM, Tom Lane t...@sss.pgh.pa.us wrote:
The floor(log(size)) part seems like it will have rather arbitrary
behavioral shifts when a table grows just past a log boundary. Also,
I'm not exactly sure whether you're
Andres Freund and...@2ndquadrant.com writes:
I think if we backpatch this we should only prefer wraparound tables and
leave the rest unchanged.
That's not a realistic option, at least not with anything that uses this
approach to sorting the tables. You'd have to assume that qsort() is
stable
On 2013-01-25 12:52:46 -0500, Tom Lane wrote:
Andres Freund and...@2ndquadrant.com writes:
I think if we backpatch this we should only prefer wraparound tables and
leave the rest unchanged.
That's not a realistic option, at least not with anything that uses this
approach to sorting the
On Fri, Jan 25, 2013 at 12:35 PM, Andres Freund and...@2ndquadrant.com wrote:
I think that to do this right, we need to consider not only the status
quo but the trajectory. For example, suppose we have two tables to
process, one of which needs a wraparound vacuum and the other one of
which
Robert Haas robertmh...@gmail.com writes:
On Fri, Jan 25, 2013 at 12:35 PM, Andres Freund and...@2ndquadrant.com
wrote:
I don't think the first part is problematic. Which scenario do you have
in mind where that would really cause adverse behaviour? autovacuum
seldomly does full table vacuums
On Fri, Jan 25, 2013 at 1:17 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
On Fri, Jan 25, 2013 at 12:35 PM, Andres Freund and...@2ndquadrant.com
wrote:
I don't think the first part is problematic. Which scenario do you have
in mind where that would really
On 24.01.2013 23:57, Alvaro Herrera wrote:
I have a bug pending that autovacuum fails to give priority to
for-wraparound tables. When xid consumption rate is high and dead tuple
creation is also high, it is possible that some tables are waiting for
for-wraparound vacuums that don't complete in
On Thu, Jan 24, 2013 at 5:22 PM, Heikki Linnakangas
hlinnakan...@vmware.com wrote:
Backpatching sounds a bit scary. It's not a clear-cut bug, it's just that
autovacuum could be smarter about its priorities. There are other ways you
can still bump into the xid-wraparound issue, even with this
Christopher Browne cbbro...@gmail.com writes:
On Thu, Jan 24, 2013 at 5:22 PM, Heikki Linnakangas
hlinnakan...@vmware.com wrote:
Backpatching sounds a bit scary. It's not a clear-cut bug, it's just that
autovacuum could be smarter about its priorities. There are other ways you
can still bump
Hi Alvaro,
Nice to see a patch on this!
On 2013-01-24 18:57:15 -0300, Alvaro Herrera wrote:
I have a bug pending that autovacuum fails to give priority to
for-wraparound tables. When xid consumption rate is high and dead tuple
creation is also high, it is possible that some tables are
83 matches
Mail list logo
