limiting hint bit I/O

I whipped up the attached patch tonight. It's pretty quick and dirty,
so it's possible I've missed something, but the intent is to suppress
writing of hint bits by buffers allocating backends, and by
checkpoints, and write them only from the background writer cleaning
scan. It therefore should (and does) avoid the problem that the first
scan of a relation after a bulk load is much slower than subsequent
scans. I used this test case:

create table s as select g,
random()::text||random()::text||random()::text||random()::text from
generate_series(1,1000000) g;

I didn't do any special configuration, so this was large enough to not
fit in shared_buffers, but small enough to fit in the OS cache. Then
I did this repeatedly:

select sum(1) from s;

Without the patch, the first run took 1602 ms, and subsequent runs
took 207-216 ms.

With the patch, the first run took 270 ms, and subsequent runs
declined very, very slowly. I got bored after getting down into the
240 ms range and ran VACUUM FREEZE, after which times dropped to about
197 ms. (This also happens without the patch - VACUUM FREEZE seems to
speed things up a bit more than just setting all the hint bits.)

I find these results pretty depressing. Obviously, the ~6x speedup on
the first run is great, but even after many runs subsequent runs it
was still 10-15% slower. Certainly, for some people this patch might
be an improvement, but on the whole I can't see applying it, unless
someone can spot something I've done wrong that casts a different
light on the situation. I am a little bit at a loss to explain how
I'm getting these results when others posted results that appeared to
show hint bits making very little difference.

Any insights appreciated.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> I whipped up the attached patch tonight.

This appears to remove the BM_JUST_DIRTIED logic. Please explain why
that's not completely broken. Even if it isn't completely broken,
it would seem better to do something like that as a separate patch.

regards, tom lane

On Thu, Jan 13, 2011 at 10:43 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> I whipped up the attached patch tonight.
>
> This appears to remove the BM_JUST_DIRTIED logic.  Please explain why
> that's not completely broken.  Even if it isn't completely broken,
> it would seem better to do something like that as a separate patch.

Well, the only point of BM_JUST_DIRTIED is to detect whether BM_DIRTY
has been set while a buffer write is in progress. With this patch,
only BM_HINT_BITS can be set while the buffer write is in progress;
BM_DIRTY cannot. Perhaps one could make the argument that this would
be a good cleanup anyway: in the unpatched code, BM_DIRTY can only be
set while a buffer I/O is in progress if it is set due to a hint-bit
update, and then we don't really care if the update gets lost.
Although that seems a bit confusing...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Thu, Jan 13, 2011 at 10:43 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> This appears to remove the BM_JUST_DIRTIED logic. Please explain why
>> that's not completely broken. Even if it isn't completely broken,
>> it would seem better to do something like that as a separate patch.

> Well, the only point of BM_JUST_DIRTIED is to detect whether BM_DIRTY
> has been set while a buffer write is in progress. With this patch,
> only BM_HINT_BITS can be set while the buffer write is in progress;
> BM_DIRTY cannot. Perhaps one could make the argument that this would
> be a good cleanup anyway: in the unpatched code, BM_DIRTY can only be
> set while a buffer I/O is in progress if it is set due to a hint-bit
> update, and then we don't really care if the update gets lost.
> Although that seems a bit confusing...

[ thinks some more... ] If memory serves, the BM_JUST_DIRTIED mechanism
dates from a time when checkpoints would write dirty buffers without
taking any lock on them; if somebody changed the page meanwhile, the
buffer was just considered to remain dirty. We later decided that was
a bad idea and set up the current arrangement whereby only hint-bit
changes are allowed while a write is in progress. So you're right that
it would be dead code if we don't consider that a hint-bit change is
really dirtying the page. I'm not for removing it altogether though,
because it seems like something we could possibly want again in the
future (for instance, we might decide to go back to write-without-lock
to reduce lock contention). It's not like we are short of buffer flag
bits. Moreover this whole business of not treating hint-bit setting as
a page-dirtying operation is completely experimental/unproven IMO, so it
would be better to keep the patch footprint as small as possible. I'd
suggest leaving BM_JUST_DIRTIED as-is and just adding BM_HINT_BITS_DIRTY
as a new flag.

regards, tom lane

On Fri, Jan 14, 2011 at 12:47 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Thu, Jan 13, 2011 at 10:43 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> This appears to remove the BM_JUST_DIRTIED logic.  Please explain why
>>> that's not completely broken.  Even if it isn't completely broken,
>>> it would seem better to do something like that as a separate patch.
>
>> Well, the only point of BM_JUST_DIRTIED is to detect whether BM_DIRTY
>> has been set while a buffer write is in progress.  With this patch,
>> only BM_HINT_BITS can be set while the buffer write is in progress;
>> BM_DIRTY cannot.  Perhaps one could make the argument that this would
>> be a good cleanup anyway: in the unpatched code, BM_DIRTY can only be
>> set while a buffer I/O is in progress if it is set due to a hint-bit
>> update, and then we don't really care if the update gets lost.
>> Although that seems a bit confusing...
>
> [ thinks some more... ]  If memory serves, the BM_JUST_DIRTIED mechanism
> dates from a time when checkpoints would write dirty buffers without
> taking any lock on them; if somebody changed the page meanwhile, the
> buffer was just considered to remain dirty.  We later decided that was
> a bad idea and set up the current arrangement whereby only hint-bit
> changes are allowed while a write is in progress.  So you're right that
> it would be dead code if we don't consider that a hint-bit change is
> really dirtying the page.  I'm not for removing it altogether though,
> because it seems like something we could possibly want again in the
> future (for instance, we might decide to go back to write-without-lock
> to reduce lock contention).  It's not like we are short of buffer flag
> bits.  Moreover this whole business of not treating hint-bit setting as
> a page-dirtying operation is completely experimental/unproven IMO, so it
> would be better to keep the patch footprint as small as possible.  I'd
> suggest leaving BM_JUST_DIRTIED as-is and just adding BM_HINT_BITS_DIRTY
> as a new flag.

I have some concerns about that proposal, but it might be the right
way to go. Before we get too far off into the weeds, though, let's
back up and talk about something more fundamental: this seems to be
speeding up the first run by 6x at the expense of slowing down many
subsequent runs by 10-15%. Does that make this whole idea dead on
arrival?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> this seems to be speeding up the first run by 6x at the expense of
> slowing down many subsequent runs by 10-15%.

If the overall throughput when measured far enough out to have hit a
steady state again is anywhere in the neighborhood of the unpatched
throughput, the leveling of the response times has enough value to
merit the change. At least in my world.

-Kevin

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Fri, Jan 14, 2011 at 12:47 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Moreover this whole business of not treating hint-bit setting as
>> a page-dirtying operation is completely experimental/unproven IMO, so it
>> would be better to keep the patch footprint as small as possible.

> I have some concerns about that proposal, but it might be the right
> way to go. Before we get too far off into the weeds, though, let's
> back up and talk about something more fundamental: this seems to be
> speeding up the first run by 6x at the expense of slowing down many
> subsequent runs by 10-15%. Does that make this whole idea dead on
> arrival?

Well, it reinforces my opinion that it's experimental ;-). But "first
run" of what, exactly? And are you sure you're taking a wholistic view
of the costs/benefits?

regards, tom lane

On Fri, Jan 14, 2011 at 1:02 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> this seems to be speeding up the first run by 6x at the expense of
>> slowing down many subsequent runs by 10-15%.
>
> If the overall throughput when measured far enough out to have hit a
> steady state again is anywhere in the neighborhood of the unpatched
> throughput, the leveling of the response times has enough value to
> merit the change.  At least in my world.

I think it would eventually settle down to the same speed, but it
might take a really long time. I got impatient before I got that far.
I'm hoping some will pick it up and play with it some more (hint,
hint).

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Jan 14, 2011 at 1:06 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Fri, Jan 14, 2011 at 12:47 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Moreover this whole business of not treating hint-bit setting as
>>> a page-dirtying operation is completely experimental/unproven IMO, so it
>>> would be better to keep the patch footprint as small as possible.
>
>> I have some concerns about that proposal, but it might be the right
>> way to go.  Before we get too far off into the weeds, though, let's
>> back up and talk about something more fundamental: this seems to be
>> speeding up the first run by 6x at the expense of slowing down many
>> subsequent runs by 10-15%.  Does that make this whole idea dead on
>> arrival?
>
> Well, it reinforces my opinion that it's experimental ;-).  But "first
> run" of what, exactly?

See the test case in my OP. The "runs" in question are "select sum(1) from s".

> And are you sure you're taking a wholistic view
> of the costs/benefits?

No.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Fri, Jan 14, 2011 at 1:06 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Well, it reinforces my opinion that it's experimental ;-). But "first
>> run" of what, exactly?

> See the test case in my OP. The "runs" in question are "select sum(1) from s".

>> And are you sure you're taking a wholistic view
>> of the costs/benefits?

> No.

Well, IMO it would be a catastrophic mistake to evaluate a patch like
this on the basis of any single test case, let alone one as simplistic
as that. I would observe in particular that your test case creates a
table containing only one distinct value of xmin, which means that the
single-transaction cache in transam.c is 100% effective, which doesn't
seem to me to be a very realistic test condition. I think this is
vastly understating the cost of missing hint bits.

So what it needs now is a lot more testing. pg_bench might be worth
trying if you want something with minimal development effort, though
I'm not sure if its clog access pattern is particularly realistic
either.

regards, tom lane

Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> I'm hoping some will pick it up and play with it some more (hint,
> hint).

That was a bit of a pun, eh?

Anyway, there are so many ideas in this area, it's hard to keep them
all straight. Personally, if I was going to start with something,
it would probably be to better establish what the impact is on
various workloads of *eliminating* hint bits. If the impact is
negative to a significant degree, my next step might be to try
background *freezing* of tuples (in a manner somewhat similar to
what you've done in this test) with the hint bits gone.

I know some people find them useful for forensics to a degree that
they would prefer not to see this, but I think it makes sense to
establish what cost people are paying every day to maintain forensic
information in this format. In previous discussions there has been
some talk about being able to get better forensics from WAL files if
certain barriers could be overcome -- having hard numbers on the
performance benefits which might also accrue might put that work in
a different perspective.

-Kevin

On Fri, Jan 14, 2011 at 1:34 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
>> I'm hoping some will pick it up and play with it some more (hint,
>> hint).
>
> That was a bit of a pun, eh?

Unintentional...

> Anyway, there are so many ideas in this area, it's hard to keep them
> all straight.  Personally, if I was going to start with something,
> it would probably be to better establish what the impact is on
> various workloads of *eliminating* hint bits. If the impact is
> negative to a significant degree, my next step might be to try
> background *freezing* of tuples (in a manner somewhat similar to
> what you've done in this test) with the hint bits gone.

Background freezing plays havoc with Hot Standby, and this test is
sufficient to show that eliminating hint bits altogether would a
significant regression on some workloads. I don't think either of
those ideas can get off the ground.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Anyway, there are so many ideas in this area, it's hard to keep them
> all straight. Personally, if I was going to start with something,
> it would probably be to better establish what the impact is on
> various workloads of *eliminating* hint bits.

> I know some people find them useful for forensics to a degree that
> they would prefer not to see this,

Um, yeah, I think you're having a problem keeping all the ideas straight
;-). The argument about forensics has to do with how soon we're willing
to freeze tuples, ie replace the XID with a constant. Not about hint
bits.

regards, tom lane

On Fri, Jan 14, 2011 at 1:42 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>> Anyway, there are so many ideas in this area, it's hard to keep them
>> all straight.  Personally, if I was going to start with something,
>> it would probably be to better establish what the impact is on
>> various workloads of *eliminating* hint bits.
>
>> I know some people find them useful for forensics to a degree that
>> they would prefer not to see this,
>
> Um, yeah, I think you're having a problem keeping all the ideas straight
> ;-).  The argument about forensics has to do with how soon we're willing
> to freeze tuples, ie replace the XID with a constant.  Not about hint
> bits.

Those things are related, though. Freezing sooner could be viewed as
an alternative to hint bits. Trouble is, it breaks Hot Standby,
badly.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> Background freezing plays havoc with Hot Standby

I must have missed or forgotten the issue of background vacuums
and hot standby. Can you summarize why that's worse than hitting
thresholds where autovacuum is freezing things?

> this test is sufficient to show that eliminating hint bits
> altogether would a significant regression on some workloads.

That wasn't clear to me from what you posted -- I thought that the
reduced performance might be partly (largely? mostly?) due to
competition with the background writer's work pushing the hinted
pages out. Maybe I'm missing something or you didn't post
everything you observed in this regard....

-Kevin

Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> Freezing sooner could be viewed as an alternative to hint bits.

Exactly. And as your test showed, things run faster frozen than
unfrozen with hint bits set.

> Trouble is, it breaks Hot Standby, badly.

You're really starting to worry me here. Both for performance and
to reduce the WAN bandwidth demands of our backup strategy we are
very aggressive with our freezing. Do off-hours VACUUM (FREEZE)
runs break hot standby? Autovacuum freezing? What are the
symptoms?

-Kevin

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Fri, Jan 14, 2011 at 1:42 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Um, yeah, I think you're having a problem keeping all the ideas straight
>> ;-). The argument about forensics has to do with how soon we're willing
>> to freeze tuples, ie replace the XID with a constant. Not about hint
>> bits.

> Those things are related, though. Freezing sooner could be viewed as
> an alternative to hint bits.

Freezing sooner isn't likely to reduce I/O compared to hint bits. What
that does is create I/O that you *have* to execute ... both in the pages
themselves, and in WAL.

regards, tom lane

On Fri, Jan 14, 2011 at 2:01 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>> Trouble is, it breaks Hot Standby, badly.
>
> You're really starting to worry me here.  Both for performance and
> to reduce the WAN bandwidth demands of our backup strategy we are
> very aggressive with our freezing.  Do off-hours VACUUM (FREEZE)
> runs break hot standby?  Autovacuum freezing?  What are the
> symptoms?

Freezing removes XIDs, so latestRemovedXid advances. VACUUM (FREEZE)
is fine if you do it when there are no queries running on your Hot
Standby server, but if there ARE queries running on the Hot Standby
server, they'll be cancelled once max_standby_delay expires.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Jan 14, 2011 at 1:52 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
>> Background freezing plays havoc with Hot Standby
>
> I must have missed or forgotten the issue of background vacuums
> and hot standby.  Can you summarize why that's worse than hitting
> thresholds where autovacuum is freezing things?

The critical issue is whether the tuples get frozen while they're
still invisible to some transactions on the standby server. That's
when you get query cancellations.

>> this test is sufficient to show that eliminating hint bits
>> altogether would a significant regression on some workloads.
>
> That wasn't clear to me from what you posted -- I thought that the
> reduced performance might be partly (largely? mostly?) due to
> competition with the background writer's work pushing the hinted
> pages out.  Maybe I'm missing something or you didn't post
> everything you observed in this regard....

Well, let me put together a quick patch that obliterates hint bits
entirely, and we can measure that. The background writer has always
pushed out hint bit pages; I think the reduced performance was
probably due to needing to reset hint bits on pages that we threw away
without pushing them out.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Jan 14, 2011 at 2:09 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Fri, Jan 14, 2011 at 1:42 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Um, yeah, I think you're having a problem keeping all the ideas straight
>>> ;-).  The argument about forensics has to do with how soon we're willing
>>> to freeze tuples, ie replace the XID with a constant.  Not about hint
>>> bits.
>
>> Those things are related, though.  Freezing sooner could be viewed as
>> an alternative to hint bits.
>
> Freezing sooner isn't likely to reduce I/O compared to hint bits.  What
> that does is create I/O that you *have* to execute ... both in the pages
> themselves, and in WAL.

It depends on which way you tilt your head - right now, we rewrite
each table 3x - once to populate, once to hint, and once to freeze.
If the table is doomed to survive long enough to go through all three
of those, then freezing is better than hinting. Of course, that's not
always the case, but people keep complaining about the way this shakes
out.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:

>> Those things are related, though. Freezing sooner could be
>> viewed as an alternative to hint bits.
>
> Freezing sooner isn't likely to reduce I/O compared to hint bits.
> What that does is create I/O that you *have* to execute ... both
> in the pages themselves, and in WAL.

In an environment where the vast majority of tuples live long enough
to need to be frozen anyway, freezing sooner doesn't really do that
to you. Granted, explicit freezing off-hours prevents autovacuum
from doing that to you in large bursts at unexpected times, but if
you're comparing background writer freezing to autovacuum freezing,
I'm not clear on where the extra pain comes from.

I am assuming that the background writer would be sane about how it
did this, of course. We could all set up straw man implementations
which would clobber performance. I suspect that you can envision a
hueristic which would be no more bothersome than autovacuum.

-Kevin

Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> The critical issue is whether the tuples get frozen while they're
> still invisible to some transactions on the standby server.
> That's when you get query cancellations.

Oh, OK; I get that. That seems easy enough to at least mitigate to
a large degree by some threshold GUC. But of course, the longer you
wait to freeze so that you don't cancel queries on the standby, the
more you pay to recalculate visibility, so it'd be a fussy thing to
tune. Perhaps such freeze information could be queued until a safe
time on the standby. (Now that I've learned the joys of SLRU, I can
see all sorts of possible uses for it....)

> Well, let me put together a quick patch that obliterates hint bits
> entirely, and we can measure that. The background writer has
> always pushed out hint bit pages; I think the reduced performance
> was probably due to needing to reset hint bits on pages that we
> threw away without pushing them out.

It would be good to confirm and quantify.

-Kevin

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Fri, Jan 14, 2011 at 2:09 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Freezing sooner isn't likely to reduce I/O compared to hint bits. What
>> that does is create I/O that you *have* to execute ... both in the pages
>> themselves, and in WAL.

> It depends on which way you tilt your head - right now, we rewrite
> each table 3x - once to populate, once to hint, and once to freeze.
> If the table is doomed to survive long enough to go through all three
> of those, then freezing is better than hinting. Of course, that's not
> always the case, but people keep complaining about the way this shakes
> out.

The people whose tables are mostly insert-only complain about it, but
that's not the majority of our userbase IMO. We just happen to have a
couple of particularly vocal ones, like Berkus.

regards, tom lane

On Fri, Jan 14, 2011 at 2:51 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Fri, Jan 14, 2011 at 2:09 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Freezing sooner isn't likely to reduce I/O compared to hint bits.  What
>>> that does is create I/O that you *have* to execute ... both in the pages
>>> themselves, and in WAL.
>
>> It depends on which way you tilt your head - right now, we rewrite
>> each table 3x - once to populate, once to hint, and once to freeze.
>> If the table is doomed to survive long enough to go through all three
>> of those, then freezing is better than hinting.  Of course, that's not
>> always the case, but people keep complaining about the way this shakes
>> out.
>
> The people whose tables are mostly insert-only complain about it, but
> that's not the majority of our userbase IMO.  We just happen to have a
> couple of particularly vocal ones, like Berkus.

True.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> The critical issue is whether the tuples get frozen while they're
>> still invisible to some transactions on the standby server.
>> That's when you get query cancellations.

> Oh, OK; I get that. That seems easy enough to at least mitigate to
> a large degree by some threshold GUC. But of course, the longer you
> wait to freeze so that you don't cancel queries on the standby, the
> more you pay to recalculate visibility, so it'd be a fussy thing to
> tune.

Yeah. Also, most of the argument for early freezing hinges on the hope
that it could happen before the tuples go to disk the first time, which
makes the window even narrower.

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>> Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>> The critical issue is whether the tuples get frozen while
>>> they're still invisible to some transactions on the standby
>>> server. That's when you get query cancellations.
>
>> Oh, OK; I get that. That seems easy enough to at least mitigate
>> to a large degree by some threshold GUC. But of course, the
>> longer you wait to freeze so that you don't cancel queries on the
>> standby, the more you pay to recalculate visibility, so it'd be a
>> fussy thing to tune.
>
> Yeah. Also, most of the argument for early freezing hinges on the
> hope that it could happen before the tuples go to disk the first
> time, which makes the window even narrower.

Is there any merit to the idea that the hot standbys could be
enhanced (in some post-9.1 version) to stash a list of tuples to
freeze in a persistent SLRU, applying them when GLobalXmin passes
the associated xid? It seems as though this would eliminate the
need to roll back transactions based on freezing without slowing
down the master or compromising the usability of the standby
(assuming that any pending ones get applied as part of promotion,
although I suppose if that time could be non-negligible, that might
be the fatal flaw).

This is more of a brainstorming thought than a well-researched
proposal, so I won't be too surprised if there's a hole in the idea
big enough to drive a truck through....

-Kevin

On 1/14/11 11:51 AM, Tom Lane wrote:
> The people whose tables are mostly insert-only complain about it, but
> that's not the majority of our userbase IMO. We just happen to have a
> couple of particularly vocal ones, like Berkus.

It might or might not be the majority, but it's an extremely common case
affecting a lot of users. Many, if not most, software applications have
a "log" table (or two, or three) which just accumulates rows, and when
that log table gets a vacuum freeze it pretty much halts the database in
its tracks. Between my client practice and IRC, I run across complaints
about this issue around 3 times a month.

And data warehousing is a significant portion of our user base, and
*all* DW users are affected by this. In some cases, vacuum issues are
sufficient to prevent people from using PostgreSQL for data warehousing.

I'd dare say that there are more users who would like autovacuum to
handle big tables better than want synchronous replication, for example.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

On Fri, Jan 14, 2011 at 05:24:31PM -0800, Josh Berkus wrote:
> On 1/14/11 11:51 AM, Tom Lane wrote:
> > The people whose tables are mostly insert-only complain about it, but
> > that's not the majority of our userbase IMO. We just happen to have a
> > couple of particularly vocal ones, like Berkus.
>
> It might or might not be the majority, but it's an extremely common case
> affecting a lot of users. Many, if not most, software applications have
> a "log" table (or two, or three) which just accumulates rows, and when
> that log table gets a vacuum freeze it pretty much halts the database in
> its tracks. Between my client practice and IRC, I run across complaints
> about this issue around 3 times a month.

If the problem is that all the freezing happens at once, then ISTM the
solution is to add a random factor. Say, when a tuple just passes the
lower threshold it has a 1% chance of being frozen. The chance grows
until it is 100% as it reaches the upper threshold.

This should reduce the freezing traffic to a constant (hopefully
manageable) stream, since as the chance of freezing increases the
amount of data to be frozen goes down, so they should cancel somewhat.

To avoid rewriting pages multiple times, if one tuple can be frozen on
a page, we should freeze as many as possible, but the logic may do that
already.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patriotism is when love of your own people comes first; nationalism,
> when hate for people other than your own comes first.
> - Charles de Gaulle

> If the problem is that all the freezing happens at once, then ISTM the
> solution is to add a random factor. Say, when a tuple just passes the
> lower threshold it has a 1% chance of being frozen. The chance grows
> until it is 100% as it reaches the upper threshold.

Doesn't have to be random; it could be determinative. That is, we could
have a vacuum_freeze_max_size parameter ... and accompanying autovacuum
parameter ... which allowed the user to limit freezing scans to, say,
1GB of the table at a time. If I could, say, call a manual freeze of
10% of the largest tables ever night, then I might actually be able to
schedule it. It's a full scan of the whole table which is fatal.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

On Sat, Jan 15, 2011 at 6:28 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> If the problem is that all the freezing happens at once, then ISTM the
>> solution is to add a random factor. Say, when a tuple just passes the
>> lower threshold it has a 1% chance of being frozen. The chance grows
>> until it is 100% as it reaches the upper threshold.
>
> Doesn't have to be random; it could be determinative.  That is, we could
> have a vacuum_freeze_max_size parameter ... and accompanying autovacuum
> parameter ... which allowed the user to limit freezing scans to, say,
> 1GB of the table at a time.  If I could, say, call a manual freeze of
> 10% of the largest tables ever night, then I might actually be able to
> schedule it.  It's a full scan of the whole table which is fatal.

I think this is worth pursuing at some point, though of course one
needs to devise an algorithm that spreads out the freezing enough but
not too much. But it's fairly off-topic from the original subject of
this thread, which was a quick-and-dirty attempt to limit the amount
of I/O caused by hint bits. I'm still very interested in knowing what
people think about that.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Jan 14, 2011, at 7:24 PM, Josh Berkus wrote:
> On 1/14/11 11:51 AM, Tom Lane wrote:
>> The people whose tables are mostly insert-only complain about it, but
>> that's not the majority of our userbase IMO. We just happen to have a
>> couple of particularly vocal ones, like Berkus.
>
> It might or might not be the majority, but it's an extremely common case
> affecting a lot of users. Many, if not most, software applications have
> a "log" table (or two, or three) which just accumulates rows, and when
> that log table gets a vacuum freeze it pretty much halts the database in
> its tracks. Between my client practice and IRC, I run across complaints
> about this issue around 3 times a month.
>
> And data warehousing is a significant portion of our user base, and
> *all* DW users are affected by this. In some cases, vacuum issues are
> sufficient to prevent people from using PostgreSQL for data warehousing.

This also affects us every time we stand up a new londiste replica, and I expect Slony folks would suffer the same thing. When you copy everything over, that's going to happen in a relatively short range of XIDs, so when those XIDs start hitting freeze age suddenly *everything* needs to get frozen.

As for hint bits, you're generally not going to have anyone reading from a slave that's still being built, so you won't see any hint bit setting until you actually open up for users. So for the first X amount of time, performance takes a big hit because you have to write all the hints out. Granted, you can technically VACUUM FREEZE after the slave is built, but that means more time before you can start using the slave and it's something you have to remember to do.
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net