Attached WIP patch series significantly simplifies the definition of
scanned_pages inside vacuumlazy.c. Apart from making several very
tricky things a lot simpler, and moving more complex code outside of
the big "blkno" loop inside lazy_scan_heap (building on the Postgres
14 work), this refactoring
Hi,
On 2021-11-21 18:13:51 -0800, Peter Geoghegan wrote:
> I have heard many stories about anti-wraparound/aggressive VACUUMs
> where the cure (which suddenly made autovacuum workers
> non-cancellable) was worse than the disease (not actually much danger
> of wraparound failure). For example:
>
>
On Mon, Nov 22, 2021 at 11:29 AM Andres Freund wrote:
> Hm. I'm a bit doubtful that there's all that many cases where it's worth not
> pruning during vacuum. However, it seems much more common for opportunistic
> pruning during non-write accesses.
Fair enough. I just wanted to suggest an explorat
Hi,
On 2021-11-22 17:07:46 -0800, Peter Geoghegan wrote:
> Sure, it wouldn't be okay to wait *indefinitely* for any one pin in a
> non-aggressive VACUUM -- so "at least waiting for one or two pins
> during non-aggressive VACUUM" might not have been the best way of
> expressing the idea that I want
On Mon, Nov 22, 2021 at 9:49 PM Andres Freund wrote:
> > For example, we can definitely afford to wait a few more milliseconds
> > to get a cleanup lock just once
>
> We currently have no infrastructure to wait for an lwlock or pincount for a
> limited time. And at least for the former it'd not be
Hi,
On 2021-11-23 17:01:20 -0800, Peter Geoghegan wrote:
> > On reason for my doubt is the following:
> >
> > We can set all-visible on a page without a FPW image (well, as long as hint
> > bits aren't logged). There's a significant difference between needing to WAL
> > log FPIs for every heap pag
On Tue, Nov 23, 2021 at 5:01 PM Peter Geoghegan wrote:
> > Behaviour that lead to a "sudden" falling over, rather than getting
> > gradually
> > worse are bad - they somehow tend to happen on Friday evenings :).
>
> These are among our most important challenges IMV.
I haven't had time to work th
On Tue, Nov 30, 2021 at 11:52 AM Peter Geoghegan wrote:
> I haven't had time to work through any of your feedback just yet --
> though it's certainly a priority for. I won't get to it until I return
> home from PGConf NYC next week.
Attached is v3, which works through most of your (Andres') feedb
On Thu, Dec 16, 2021 at 5:27 AM Peter Geoghegan wrote:
>
> On Fri, Dec 10, 2021 at 1:48 PM Peter Geoghegan wrote:
> > * I'm still working on the optimization that we discussed on this
> > thread: the optimization that allows the final relfrozenxid (that we
> > set in pg_class) to be determined dy
On Thu, Dec 16, 2021 at 10:46 PM Masahiko Sawada wrote:
> > My emphasis here has been on making non-aggressive VACUUMs *always*
> > advance relfrozenxid, outside of certain obvious edge cases. And so
> > with all the patches applied, up to and including the opportunistic
> > freezing patch, every
On Sat, Dec 18, 2021 at 11:29 AM Peter Geoghegan wrote:
>
> On Thu, Dec 16, 2021 at 10:46 PM Masahiko Sawada
> wrote:
> > > My emphasis here has been on making non-aggressive VACUUMs *always*
> > > advance relfrozenxid, outside of certain obvious edge cases. And so
> > > with all the patches app
On Mon, Dec 20, 2021 at 8:29 PM Masahiko Sawada wrote:
> > Can we fully get rid of vacuum_freeze_table_age?
>
> Does it mean that a vacuum always is an aggressive vacuum?
No. Just somewhat more like one. Still no waiting for cleanup locks,
though. Also, autovacuum is still cancelable (that's tech
On Sun, Mar 13, 2022 at 9:05 PM Peter Geoghegan wrote:
> Attached is v10. While this does still include the freezing patch,
> it's not in scope for Postgres 15. As I've said, I still think that it
> makes sense to maintain the patch series with the freezing stuff,
> since it's structurally related
On Wed, Mar 23, 2022 at 3:59 PM Peter Geoghegan wrote:
> In other words, since DISABLE_PAGE_SKIPPING doesn't *consistently*
> force lazy_scan_noprune to refuse to process a page on HEAD (it all
> depends on FreezeLimit/vacuum_freeze_min_age), it is logical for
> DISABLE_PAGE_SKIPPING to totally ge
On Wed, Mar 23, 2022 at 1:41 PM Robert Haas wrote:
> It seems to me that if DISABLE_PAGE_SKIPPING doesn't completely
> disable skipping pages, we have a problem.
It depends on how you define skipping. DISABLE_PAGE_SKIPPING was
created at a time when a broader definition of skipping made a lot
mor
On Wed, Mar 23, 2022 at 4:49 PM Peter Geoghegan wrote:
> On Wed, Mar 23, 2022 at 1:41 PM Robert Haas wrote:
> > It seems to me that if DISABLE_PAGE_SKIPPING doesn't completely
> > disable skipping pages, we have a problem.
>
> It depends on how you define skipping. DISABLE_PAGE_SKIPPING was
> cre
On Wed, Mar 23, 2022 at 1:53 PM Robert Haas wrote:
> I see what you mean about it depending on how you define "skipping".
> But I think that DISABLE_PAGE_SKIPPING is intended as a sort of
> emergency safeguard when you really, really don't want to leave
> anything out.
I agree.
> And therefore I
On Thu, Mar 24, 2022 at 9:59 AM Peter Geoghegan wrote:
> On Wed, Mar 23, 2022 at 1:53 PM Robert Haas wrote:
> > And therefore I favor defining it to mean that we don't
> > skip any work at all.
>
> But even today DISABLE_PAGE_SKIPPING won't do pruning when we cannot
> acquire a cleanup lock on a
On Wed, Mar 23, 2022 at 2:03 PM Thomas Munro wrote:
> Yeah, I found it confusing that DISABLE_PAGE_SKIPPING doesn't disable
> all page skipping, so 3414099c turned out to be not enough.
The proposed change to DISABLE_PAGE_SKIPPING is partly driven by that,
and partly driven by a similar concern a
On Wed, Mar 23, 2022 at 6:28 PM Peter Geoghegan wrote:
> It would be great if you could take a look v11-0002-*, Robert. Does it
> make sense to you?
You're probably not going to love hearing this, but I think you're
still explaining things here in ways that are too baroque and hard to
follow. I d
On Thu, Mar 24, 2022 at 10:21 AM Robert Haas wrote:
> You're probably not going to love hearing this, but I think you're
> still explaining things here in ways that are too baroque and hard to
> follow. I do think it's probably better.
There are a lot of dimensions to this work. It's hard to know
On Thu, Mar 24, 2022 at 3:28 PM Peter Geoghegan wrote:
> But non-aggressive VACUUMs have always been able to do that.
>
> How about: "Set relfrozenxid to oldest extant XID seen by VACUUM"
Sure, that sounds nice.
> Believe it or not, I avoided functional changes in 0002 -- at least in
> one impor
On Thu, Mar 24, 2022 at 1:21 PM Robert Haas wrote:
> > How about: "Set relfrozenxid to oldest extant XID seen by VACUUM"
>
> Sure, that sounds nice.
Cool.
> > What you're saying here boils down to this: it doesn't matter what the
> > visibility map would say right this microsecond (in the aggres
On Thu, Mar 24, 2022 at 2:40 PM Peter Geoghegan wrote:
> > > This is absolutely mandatory in the aggressive case, because otherwise
> > > relfrozenxid advancement might be seen as unsafe. My observation is:
> > > Why should we accept the same race in the non-aggressive case? Why not
> > > do essen
On Sun, Mar 27, 2022 at 11:24 PM Peter Geoghegan wrote:
> Attached is v12. My current goal is to commit all 3 patches before
> feature freeze. Note that this does not include the more complicated
> patch including with previous revisions of the patch series (the
> page-level freezing work that app
On Tue, Mar 29, 2022 at 10:03 AM Robert Haas wrote:
> I can understand this version of the commit message. Woohoo! I like
> understanding things.
That's good news.
> I think the header comments for FreezeMultiXactId() focus way too much
> on what the caller is supposed to do and not nearly enoug
On Tue, Mar 29, 2022 at 11:58 AM Peter Geoghegan wrote:
> > I think I understand what the first paragraph of the header comment
> > for heap_tuple_needs_freeze() is trying to say, but the second one is
> > quite confusing. I think this is again because it veers into talking
> > about what the call
+ diff = (int32) (vacrel->NewRelfrozenXid -
vacrel->relfrozenxid);
+ Assert(diff > 0);
Did you see that this crashed on windows cfbot?
https://api.cirrus-ci.com/v1/artifact/task/4592929254670336/log/tmp_check/postmaster.log
TRAP: Failed
On Tue, Mar 29, 2022 at 11:10 PM Justin Pryzby wrote:
>
> + diff = (int32) (vacrel->NewRelfrozenXid -
> vacrel->relfrozenxid);
> + Assert(diff > 0);
>
> Did you see that this crashed on windows cfbot?
>
> https://api.cirrus-ci.com/v1/art
On Wed, Mar 30, 2022 at 12:01 AM Peter Geoghegan wrote:
> Perhaps something is amiss inside vac_update_relstats(), where the
> boolean flag that indicates that pg_class.relfrozenxid was advanced is
> set:
>
> if (frozenxid_updated)
> *frozenxid_updated = false;
> if (TransactionIdI
Hi,
On 2022-03-30 17:50:42 -0700, Peter Geoghegan wrote:
> I tried several times to recreate this issue on CI. No luck with that,
> though -- can't get it to fail again after 4 attempts.
It's really annoying that we don't have Assert variants that show the compared
values, that might make it easi
On Wed, Mar 30, 2022 at 7:00 PM Andres Freund wrote:
> Something vaguely like EXPECT_EQ_U32 in regress.c. Maybe
> AssertCmp(type, a, op, b),
>
> Then the assertion could have been something like
>AssertCmp(int32, diff, >, 0)
I'd definitely use them if they were there.
> Does the line number
On Wed, Mar 30, 2022 at 7:37 PM Peter Geoghegan wrote:
> Yeah, a WARNING would be good here. I can write a new version of my
> patch series with a separation patch for that this evening. Actually,
> better make it a PANIC for now...
Attached is v14, which includes a new patch that PANICs like tha
Hi,
I was able to trigger the crash.
cat ~/tmp/pgbench-createdb.sql
CREATE DATABASE pgb_:client_id;
DROP DATABASE pgb_:client_id;
pgbench -n -P1 -c 10 -j10 -T100 -f ~/tmp/pgbench-createdb.sql
while I was also running
for i in $(seq 1 100); do echo iteration $i; make -Otarget -C contrib/ -s
in
On Wed, Mar 30, 2022 at 8:28 PM Andres Freund wrote:
> I triggered twice now, but it took a while longer the second time.
Great.
I wonder if you can get an RR recording...
--
Peter Geoghegan
Hi,
On 2022-03-30 20:28:44 -0700, Andres Freund wrote:
> I was able to trigger the crash.
>
> cat ~/tmp/pgbench-createdb.sql
> CREATE DATABASE pgb_:client_id;
> DROP DATABASE pgb_:client_id;
>
> pgbench -n -P1 -c 10 -j10 -T100 -f ~/tmp/pgbench-createdb.sql
>
> while I was also running
>
> for
Hi,
On 2022-03-30 20:35:25 -0700, Peter Geoghegan wrote:
> On Wed, Mar 30, 2022 at 8:28 PM Andres Freund wrote:
> > I triggered twice now, but it took a while longer the second time.
>
> Great.
>
> I wonder if you can get an RR recording...
Started it, but looks like it's too slow.
(gdb) p MyPr
On Wed, Mar 30, 2022 at 9:04 PM Andres Freund wrote:
> (gdb) p vacrel->NewRelfrozenXid
> $3 = 717
> (gdb) p vacrel->relfrozenxid
> $4 = 717
> (gdb) p OldestXmin
> $5 = 5112
> (gdb) p aggressive
> $6 = false
Does this OldestXmin seem reasonable at this point in execution, based
on context? Does it
Hi,
On 2022-03-30 21:04:07 -0700, Andres Freund wrote:
> On 2022-03-30 20:35:25 -0700, Peter Geoghegan wrote:
> > On Wed, Mar 30, 2022 at 8:28 PM Andres Freund wrote:
> > > I triggered twice now, but it took a while longer the second time.
> >
> > Great.
> >
> > I wonder if you can get an RR reco
Hi,
On 2022-03-30 21:11:48 -0700, Peter Geoghegan wrote:
> On Wed, Mar 30, 2022 at 9:04 PM Andres Freund wrote:
> > (gdb) p vacrel->NewRelfrozenXid
> > $3 = 717
> > (gdb) p vacrel->relfrozenxid
> > $4 = 717
> > (gdb) p OldestXmin
> > $5 = 5112
> > (gdb) p aggressive
> > $6 = false
>
> Does this O
On Wed, Mar 30, 2022 at 9:20 PM Andres Freund wrote:
> But the debug elog reports that
>
> relfrozenxid updated 714 -> 717
> relminmxid updated 1 -> 6
>
> Tthe problem is that the crashing backend reads the relfrozenxid/relminmxid
> from the shared relcache init file written by another backend:
W
On Wed, Mar 30, 2022 at 9:29 PM Peter Geoghegan wrote:
> > Perhaps we should just fetch the horizons from the "local" catalog for
> > shared
> > rels?
>
> Not sure what you mean.
Wait, you mean use vacrel->relfrozenxid directly? Seems kind of ugly...
--
Peter Geoghegan
Hi,
On 2022-03-30 21:29:16 -0700, Peter Geoghegan wrote:
> On Wed, Mar 30, 2022 at 9:20 PM Andres Freund wrote:
> > But the debug elog reports that
> >
> > relfrozenxid updated 714 -> 717
> > relminmxid updated 1 -> 6
> >
> > Tthe problem is that the crashing backend reads the relfrozenxid/relmin
Hi,
On 2022-03-30 21:59:15 -0700, Andres Freund wrote:
> On 2022-03-30 21:29:16 -0700, Peter Geoghegan wrote:
> > On Wed, Mar 30, 2022 at 9:20 PM Andres Freund wrote:
> > > Perhaps we should just fetch the horizons from the "local" catalog for
> > > shared
> > > rels?
> >
> > Not sure what you
On Thu, Mar 31, 2022 at 9:37 AM Andres Freund wrote:
> Perhaps we should explicitly mask out parts of relcache entries in the shared
> init file that we know to be unreliable. I.e. set relfrozenxid, relminmxid to
> Invalid* or such.
That has the advantage of being more honest. If you're going to
Hi,
On 2022-03-31 09:58:18 -0700, Peter Geoghegan wrote:
> On Thu, Mar 31, 2022 at 9:37 AM Andres Freund wrote:
> > The only place that appears to access rd_rel->relfrozenxid outside of DDL is
> > heap_abort_speculative().
>
> I wonder how necessary that really is. Even if the XID is before
> re
On Wed, Mar 30, 2022 at 9:59 PM Andres Freund wrote:
> I'm not sure there's a proper bug on HEAD here. I think at worst it can delay
> the horizon increasing a bunch, by falsely not using an aggressive vacuum when
> we should have - might even be limited to a single autovacuum cycle.
So, to be cl
On Thu, Mar 31, 2022 at 10:11 AM Andres Freund wrote:
> I don't think we should weaken defenses against xids from before relfrozenxid
> in vacuum / amcheck / If anything we should strengthen them.
>
> Isn't it also just plainly required for correctness? We'd not necessarily
> trigger a vacuum
Hi,
On 2022-03-31 10:12:49 -0700, Peter Geoghegan wrote:
> On Wed, Mar 30, 2022 at 9:59 PM Andres Freund wrote:
> > I'm not sure there's a proper bug on HEAD here. I think at worst it can
> > delay
> > the horizon increasing a bunch, by falsely not using an aggressive vacuum
> > when
> > we sho
On Thu, Mar 31, 2022 at 10:50 AM Andres Freund wrote:
> > So, to be clear: vac_update_relstats() never actually considered the
> > new relfrozenxid value from its vacuumlazy.c caller to be "in the
> > future"?
>
> No, I added separate debug messages for those, and also applied your patch,
> and it
On Thu, Mar 31, 2022 at 11:19 AM Peter Geoghegan wrote:
> The assert is "Assert(diff > 0)", and not "Assert(diff >= 0)".
Attached is v15. I plan to commit the first two patches (the most
substantial two patches by far) in the next couple of days, barring
objections.
v15 removes this "Assert(diff
Hi,
On 2022-04-01 10:54:14 -0700, Peter Geoghegan wrote:
> On Thu, Mar 31, 2022 at 11:19 AM Peter Geoghegan wrote:
> > The assert is "Assert(diff > 0)", and not "Assert(diff >= 0)".
>
> Attached is v15. I plan to commit the first two patches (the most
> substantial two patches by far) in the next
On Sun, Apr 3, 2022 at 12:05 PM Andres Freund wrote:
> Just saw that you committed: Wee! I think this will be a substantial
> improvement for our users.
I hope so! I think that it's much more useful as the basis for future
work than as a standalone thing. Users of Postgres 15 might not notice
a h
On Fri, Apr 1, 2022 at 10:54 AM Peter Geoghegan wrote:
> I also refined the WARNING patch in v15. It now actually issues
> WARNINGs (rather than PANICs, which were just a temporary debugging
> measure in v14).
Going to commit this remaining patch tomorrow, barring objections.
--
Peter Geoghegan
Hi,
On 2022-04-04 19:32:13 -0700, Peter Geoghegan wrote:
> On Fri, Apr 1, 2022 at 10:54 AM Peter Geoghegan wrote:
> > I also refined the WARNING patch in v15. It now actually issues
> > WARNINGs (rather than PANICs, which were just a temporary debugging
> > measure in v14).
>
> Going to commit t
On Mon, Apr 4, 2022 at 8:18 PM Andres Freund wrote:
> The remaining patch are the warnings in vac_update_relstats(), correct? I
> guess one could argue they should be LOG rather than WARNING, but I find the
> project stance on that pretty impractical. So warning's ok with me.
Right. The reason I
On Mon, Apr 4, 2022 at 8:25 PM Peter Geoghegan wrote:
> Right. The reason I used WARNINGs was because it matches vaguely
> related WARNINGs in vac_update_relstats()'s sibling function,
> vacuum_set_xid_limits().
Okay, pushed the relfrozenxid warning patch.
Thanks
--
Peter Geoghegan
On Thu, Apr 14, 2022 at 4:19 PM Jim Nasby wrote:
> > - percentage of non-yet-removable vs removable tuples
>
> This'd give you an idea how bad your long-running-transaction problem is.
VACUUM fundamentally works by removing those tuples that are
considered dead according to an XID-based cutoff es
On Fri, Dec 17, 2021 at 9:30 PM Peter Geoghegan wrote:
> Can we fully get rid of vacuum_freeze_table_age? Maybe even get rid of
> vacuum_freeze_min_age, too? Freezing tuples is a maintenance task for
> physical blocks, but we use logical units (XIDs).
I don't see how we can get rid of these. We k
On Thu, Jan 6, 2022 at 12:54 PM Robert Haas wrote:
> On Fri, Dec 17, 2021 at 9:30 PM Peter Geoghegan wrote:
> > Can we fully get rid of vacuum_freeze_table_age? Maybe even get rid of
> > vacuum_freeze_min_age, too? Freezing tuples is a maintenance task for
> > physical blocks, but we use logical
On Thu, Jan 6, 2022 at 2:45 PM Peter Geoghegan wrote:
> But the "freeze early" heuristics work a bit like that anyway. We
> won't freeze all the tuples on a whole heap page early if we won't
> otherwise set the heap page to all-visible (not all-frozen) in the VM
> anyway.
I believe that applicati
On Thu, Jan 6, 2022 at 5:46 PM Peter Geoghegan wrote:
> One obvious reason for this is that the opportunistic freezing stuff
> is expected to be the thing that usually forces freezing -- not
> vacuum_freeze_min_age, nor FreezeLimit, nor any other XID-based
> cutoff. As you more or less pointed out
On Fri, Jan 7, 2022 at 12:24 PM Robert Haas wrote:
> This seems like a weak argument. Sure, you COULD hard-code the limit
> to be autovacuum_freeze_max_age/2 rather than making it a separate
> tunable, but I don't think it's better. I am generally very skeptical
> about the idea of using the same
On Fri, Jan 7, 2022 at 5:20 PM Peter Geoghegan wrote:
> I thought I was being conservative by suggesting
> autovacuum_freeze_max_age/2. My first thought was to teach VACUUM to
> make its FreezeLimit "OldestXmin - autovacuum_freeze_max_age". To me
> these two concepts really *are* the same thing: v
On Thu, Jan 13, 2022 at 12:19 PM Robert Haas wrote:
> I can't follow this. If the idea is that we're going to
> opportunistically freeze a page whenever that allows us to mark it
> all-visible, then the remaining question is what XID age we should use
> to force freezing when that rule doesn't app
On Thu, Jan 13, 2022 at 4:27 PM Peter Geoghegan wrote:
> 1. Cases where our inability to get a cleanup lock signifies nothing
> at all about the page in question, or any page in the same table, with
> the same workload.
>
> 2. Pathological cases. Cases where we're at least at the mercy of the
> ap
On Mon, Jan 17, 2022 at 7:12 AM Robert Haas wrote:
> On Thu, Jan 13, 2022 at 4:27 PM Peter Geoghegan wrote:
> > 1. Cases where our inability to get a cleanup lock signifies nothing
> > at all about the page in question, or any page in the same table, with
> > the same workload.
> >
> > 2. Patholo
On Mon, Jan 17, 2022 at 4:28 PM Peter Geoghegan wrote:
> Updating relfrozenxid should now be thought of as a continuous thing,
> not a discrete thing.
I think that's pretty nearly 100% wrong. The most simplistic way of
expressing that is to say - clearly it can only happen when VACUUM
runs, which
On Mon, Jan 17, 2022 at 2:13 PM Robert Haas wrote:
> On Mon, Jan 17, 2022 at 4:28 PM Peter Geoghegan wrote:
> > Updating relfrozenxid should now be thought of as a continuous thing,
> > not a discrete thing.
>
> I think that's pretty nearly 100% wrong. The most simplistic way of
> expressing that
On Mon, Jan 17, 2022 at 5:41 PM Peter Geoghegan wrote:
> That just seems like semantics to me. The very next sentence after the
> one you quoted in your reply was "And so it's highly unlikely that any
> given VACUUM will ever *completely* fail to advance relfrozenxid".
> It's continuous *within* e
On Mon, Jan 17, 2022 at 8:13 PM Robert Haas wrote:
> On Mon, Jan 17, 2022 at 5:41 PM Peter Geoghegan wrote:
> > That just seems like semantics to me. The very next sentence after the
> > one you quoted in your reply was "And so it's highly unlikely that any
> > given VACUUM will ever *completely*
On Tue, Jan 18, 2022 at 12:14 AM Peter Geoghegan wrote:
> I quite clearly said that you'll only get an anti-wraparound VACUUM
> with the patch applied when the only factor that *ever* causes *any*
> autovacuum worker to VACUUM the table (assuming the workload is
> stable) is the anti-wraparound/au
On Tue, Jan 18, 2022 at 6:11 AM Robert Haas wrote:
> On Tue, Jan 18, 2022 at 12:14 AM Peter Geoghegan wrote:
> > I quite clearly said that you'll only get an anti-wraparound VACUUM
> > with the patch applied when the only factor that *ever* causes *any*
> > autovacuum worker to VACUUM the table (
On Tue, Jan 18, 2022 at 1:48 PM Peter Geoghegan wrote:
> That's what I was reacting to -- it had nothing to do with any
> concerns you may have had. I wasn't thinking about long-idle cursors
> at all. I was defending myself, because I was put in a position where
> I had to defend myself.
I don't
On Wed, Jan 19, 2022 at 6:56 AM Robert Haas wrote:
> I don't think I've said anything on this thread that is an attack on
> you. I am getting pretty frustrated with the tenor of the discussion,
> though. I feel like you're the one attacking me, and I don't like it.
"Attack" is a strong word (much
On Wed, Jan 19, 2022 at 2:54 PM Peter Geoghegan wrote:
> > On the other hand if that user is going to close that
> > cursor after 10 minutes and open a new one in the same place 10
> > seconds later, the best thing to do is to keep FreezeLimit as low as
> > possible, because the first time we wait
On Thu, Jan 20, 2022 at 6:55 AM Robert Haas wrote:
> Great, I'm glad we agree on that much. I would be interested in
> hearing what other people think about this scenario.
Agreed.
> I'm just being honest here when I say that I can't see any huge
> reduction in risk. Nor a huge increase in risk.
On Thu, Jan 20, 2022 at 11:45 AM Peter Geoghegan wrote:
> My thinking on vacuum_freeze_min_age has shifted very slightly. I now
> think that I'll probably need to keep it around, just so things like
> VACUUM FREEZE (which sets vacuum_freeze_min_age to 0 internally)
> continue to work. So maybe its
On Thu, 20 Jan 2022 at 17:01, Peter Geoghegan wrote:
>
> Then there's the fact that you
> really cannot think about the rate of XID consumption intuitively --
> it has at best a weak, unpredictable relationship with anything that
> users can understand, such as data stored or wall clock time.
Thi
On Fri, Jan 21, 2022 at 12:07 PM Greg Stark wrote:
> This confuses me. "Transactions per second" is a headline database
> metric that lots of users actually focus on quite heavily -- rather
> too heavily imho.
But transactions per second is for the whole database, not for
individual tables. It's
On Sat, Jan 29, 2022 at 11:43 PM Peter Geoghegan wrote:
>
> On Thu, Jan 20, 2022 at 2:00 PM Peter Geoghegan wrote:
> > I do see some value in that, too. Though it's not going to be a way of
> > turning off the early freezing stuff, which seems unnecessary (though
> > I do still have work to do on
On Fri, Feb 4, 2022 at 2:00 PM John Naylor wrote:
> Without having looked at the latest patches, there was something in
> the back of my mind while following the discussion upthread -- the
> proposed opportunistic freezing made a lot more sense if the
> earlier-proposed open/closed pages concept w
On Sat, Jan 29, 2022 at 11:43 PM Peter Geoghegan wrote:
> When VACUUM sees that all remaining/unpruned tuples on a page are
> all-visible, it isn't just important because of cost control
> considerations. It's deeper than that. It's also treated as a
> tentative signal from the application itself,
On Fri, Feb 4, 2022 at 2:45 PM Robert Haas wrote:
> While I agree that there's some case to be made for leaving settled
> pages well enough alone, your criterion for settled seems pretty much
> accidental.
I fully admit that I came up with the FSM heuristic with TPC-C in
mind. But you have to sta
On Fri, Feb 4, 2022 at 3:31 PM Peter Geoghegan wrote:
> Application B will already block pruning by VACUUM operations against
> application A's table, and so effectively blocks recording of the
> resultant free space in the FSM in your scenario. And so application A
> and application B should be c
On Fri, Feb 4, 2022 at 4:18 PM Robert Haas wrote:
> On Fri, Feb 4, 2022 at 3:31 PM Peter Geoghegan wrote:
> > Application B will already block pruning by VACUUM operations against
> > application A's table, and so effectively blocks recording of the
> > resultant free space in the FSM in your sce
On Wed, 15 Dec 2021 at 15:30, Peter Geoghegan wrote:
>
> My emphasis here has been on making non-aggressive VACUUMs *always*
> advance relfrozenxid, outside of certain obvious edge cases. And so
> with all the patches applied, up to and including the opportunistic
> freezing patch, every autovacuu
On Fri, Feb 4, 2022 at 10:21 PM Greg Stark wrote:
> On Wed, 15 Dec 2021 at 15:30, Peter Geoghegan wrote:
> > My emphasis here has been on making non-aggressive VACUUMs *always*
> > advance relfrozenxid, outside of certain obvious edge cases. And so
> > with all the patches applied, up to and incl
On Fri, Feb 4, 2022 at 10:44 PM Peter Geoghegan wrote:
> Right - it's practically inevitable that you'll need an
> anti-wraparound VACUUM to advance relfrozenxid right now. Technically
> it's possible to advance relfrozenxid in any VACUUM, but in practice
> it just never happens on a large table.
On Fri, Feb 4, 2022 at 10:21 PM Greg Stark wrote:
> By far the majority of anti-wraparound vacuums are triggered by tables
> that are very large and so don't trigger regular vacuums for "long
> periods" of time and consistently hit the anti-wraparound threshold
> first.
That's interesting, becaus
On Fri, Feb 4, 2022 at 10:45 PM Peter Geoghegan wrote:
> > While I've seen all the above cases triggering anti-wraparound cases
> > by far the majority of the cases are not of these pathological forms.
>
> Right - it's practically inevitable that you'll need an
> anti-wraparound VACUUM to advance
On Mon, Feb 7, 2022 at 10:08 AM Robert Haas wrote:
> But ... if I'm not mistaken, in the kind of case that Greg is
> describing, relfrozenxid will be advanced exactly as often as it is
> today.
But what happens today in a scenario like Greg's is pathological,
despite being fairly common (common i
On Mon, Feb 7, 2022 at 11:43 AM Peter Geoghegan wrote:
> > That's because, if VACUUM is only ever getting triggered by XID
> > age advancement and not by bloat, there's no opportunity for your
> > patch set to advance relfrozenxid any sooner than we're doing now.
>
> We must distinguish between:
>
On Mon, Feb 7, 2022 at 12:21 PM Robert Haas wrote:
>
> On Mon, Feb 7, 2022 at 11:43 AM Peter Geoghegan wrote:
> > > That's because, if VACUUM is only ever getting triggered by XID
> > > age advancement and not by bloat, there's no opportunity for your
> > > patch set to advance relfrozenxid any s
On Sat, Jan 29, 2022 at 8:42 PM Peter Geoghegan wrote:
> Attached is v7, a revision that overhauls the algorithm that decides
> what to freeze. I'm now calling it block-driven freezing in the commit
> message. Also included is a new patch, that makes VACUUM record zero
> free space in the FSM for
On Fri, Feb 11, 2022 at 8:30 PM Peter Geoghegan wrote:
> Attached is v8. No real changes -- just a rebased version.
Concerns about my general approach to this project (and even the
Postgres 14 VACUUM work) were expressed by Robert and Andres over on
the "Nonrandom scanned_pages distorts pg_class.
On Fri, Feb 18, 2022 at 3:41 PM Peter Geoghegan wrote:
> Concerns about my general approach to this project (and even the
> Postgres 14 VACUUM work) were expressed by Robert and Andres over on
> the "Nonrandom scanned_pages distorts pg_class.reltuples set by
> VACUUM" thread. Some of what was said
On Fri, Feb 18, 2022 at 12:54 PM Robert Haas wrote:
> I'd like to have a clearer idea of exactly what is in each of the
> remaining patches before forming a final opinion.
Great.
> What's tricky about 0001? Does it change any other behavior, either as
> a necessary component of advancing relfroz
On Fri, Feb 18, 2022 at 4:10 PM Peter Geoghegan wrote:
> It does not change any other behavior. It's totally mechanical.
>
> 0001 is tricky in the sense that there are a lot of fine details, and
> if you get any one of them wrong the result might be a subtle bug. For
> example, the heap_tuple_need
Hi,
On 2022-02-18 13:09:45 -0800, Peter Geoghegan wrote:
> 0001 is tricky in the sense that there are a lot of fine details, and
> if you get any one of them wrong the result might be a subtle bug. For
> example, the heap_tuple_needs_freeze() code path is only used when we
> cannot get a cleanup l
1 - 100 of 133 matches
Mail list logo