getting rid of freezing
Hi,
after having discussed $subject shortly over dinner yesterday, while I
should have been preparing the slides for my talk I noticed that there
might be a rather easy way to get rid of freezing.
I think that the existence of hint bits and the crash safe visibility
maps should provide sufficient tooling to make freezing unneccessary
without loosing much information for debugging if we modify the way
vacuum works a bit.
Currently, aside from recovery, we only set all visible in vacuum.
vacuumlazy.c's lazy_scan_heap currently works like:
for (blkno = 0; blkno < nblocks; blkno++)
{
if (!scan_all && invisible)
continue;
/* cannot lock buffer immediately */
if (!ConditionalLockBufferForCleanup(buf))
{
if (!scan_all)
continue;
/* don't block if we don't need freezing */
if (!lazy_check_needs_freeze(buf))
continue;
/* now wait for cleanup lock */
LockBufferForCleanup(buf);
}
for (tuple in all_tuples)
{
cleanup_tuple();
}
if (nfrozen > 0)
log_heap_freeze()
if (all_visible)
{
PageSetAllVisible(page);
visibilitymap_set(page);
}
}
In other words, if we don't need to make sure there aren't any old
tuples, we only scan visible parts of the relation. If we are making a
freeze vacuum we scan the whole relation, waiting for a cleanup lock on
the relation if necessary.
We currently need to make sure we scanned the whole relation and have
frozen everything to have a sensible relfrozenxid for a relation.
So, what I propose instead is basically:
1) only vacuum non-all-visible pages, even when doing it for
anti-wraparound
2) When we can set all-visible guarantee that all tuples on the page are
fully hinted. During recovery do the same, so we don't need to log
all hint bits.
We can do this with only an exclusive lock on the buffer, we don't
need a cleanup lock.
3) When we cannot mark a page all-visible or we cannot get the cleanup
lock, remember the oldest xmin on that page. We could set all visible
in the former case, but we want the page to be cleaned up sometime
soonish.
4) If we can get the cleanup lock, purge dead tuples from the page and
the indexes, just as today. Set the page as all-visible.
That way we know that any page that is all-visible doesn't ever need to
look at xmin/xmax since we are sure to have set all relevant hint
bits.
We don't even necessarily need to log the hint bits for all items since
the redo for all_visible could make sure all items are hinted. The only
problem is knowing up to where we can truncate pg_clog...
Makes sense?
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2013-05-23 19:51:48 +0200, Andres Freund wrote:
I think that the existence of hint bits and the crash safe visibility
maps should provide sufficient tooling to make freezing unneccessary
without loosing much information for debugging if we modify the way
vacuum works a bit.
That way we know that any page that is all-visible doesn't ever need to
look at xmin/xmax since we are sure to have set all relevant hint
bits.
One case that would make this problematic is row level locks on
tuples. We would need to unset all visible for them, otherwise we might
do the wrong thing when looking at xmax...
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2013-05-23 19:51:48 +0200, Andres Freund wrote:
We currently need to make sure we scanned the whole relation and have
frozen everything to have a sensible relfrozenxid for a relation.So, what I propose instead is basically:
1) only vacuum non-all-visible pages, even when doing it for
anti-wraparound
2) When we can set all-visible guarantee that all tuples on the page are
fully hinted. During recovery do the same, so we don't need to log
all hint bits.
We can do this with only an exclusive lock on the buffer, we don't
need a cleanup lock.
3) When we cannot mark a page all-visible or we cannot get the cleanup
lock, remember the oldest xmin on that page. We could set all visible
in the former case, but we want the page to be cleaned up sometime
soonish.
4) If we can get the cleanup lock, purge dead tuples from the page and
the indexes, just as today. Set the page as all-visible.That way we know that any page that is all-visible doesn't ever need to
look at xmin/xmax since we are sure to have set all relevant hint
bits.
Heikki noticed that I made quite the omission here which is that you
would need to mark tuples as all visible as well. I was thinking about
using HEAP_MOVED_OFF | HEAP_MOVED_IN as a hint for that.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, May 23, 2013 at 1:51 PM, Andres Freund <andres@2ndquadrant.com> wrote:
So, what I propose instead is basically:
1) only vacuum non-all-visible pages, even when doing it for
anti-wraparound
Check. We might want an option to force a scan of the whole relation.
2) When we can set all-visible guarantee that all tuples on the page are
fully hinted. During recovery do the same, so we don't need to log
all hint bits.
We can do this with only an exclusive lock on the buffer, we don't
need a cleanup lock.
I don't think this works. Emitting XLOG_HEAP_VISIBLE for a heap page
does not emit an FPI for the heap page, only (if needed) for the
visibility map page. So a subsequent crash that tears the page could
keep XLOG_HEAP_VISIBLE but lose other changes on the page - i.e. the
hint bits.
3) When we cannot mark a page all-visible or we cannot get the cleanup
lock, remember the oldest xmin on that page. We could set all visible
in the former case, but we want the page to be cleaned up sometime
soonish.
I think you mean "in the latter case" not "in the former case". If
not, then I'm confused.
4) If we can get the cleanup lock, purge dead tuples from the page and
the indexes, just as today. Set the page as all-visible.That way we know that any page that is all-visible doesn't ever need to
look at xmin/xmax since we are sure to have set all relevant hint
bits.We don't even necessarily need to log the hint bits for all items since
the redo for all_visible could make sure all items are hinted. The only
problem is knowing up to where we can truncate pg_clog...
The redo for all_visible cannot make sure all items are hinted.
Again, there's no FPI on the heap page. The heap page could in fact
contain dead tuples at the time we mark it all-visible. Consider, for
example:
0. Checkpoint.
1. The buffer becomes all visible.
2. A tuple is inserted, making the buffer not-all-visible.
3. The page is written by the OS.
4. Crash.
Now, recovery will first find the record marking the buffer
all-visible, and will mark it all-visible. Now the all-visible bit on
the page is flat-out wrong, but it doesn't matter because we haven't
reached consistency. Next we'll find the heap-insert record, which
will have an FPI, since it's the first WAL-logged change to the buffer
since the last checkpoint. Now the FPI fixes everything and we're
back in a sane state.
Now in this particular case it wouldn't hurt anything if the redo
routine that set the all-visible bit also hinted all the tuples,
because the FPI is going to overwrite it anyway. But suppose in lieu
of steps (3) and (4) we write half of the page and then crash, leaving
behind a torn page. Now it's pretty crazy to think about trying to
hint tuples; the page may be in a completely insane state.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 05/23/2013 10:03 PM, Andres Freund wrote:
On 2013-05-23 19:51:48 +0200, Andres Freund wrote:
We currently need to make sure we scanned the whole relation and have
frozen everything to have a sensible relfrozenxid for a relation.So, what I propose instead is basically:
1) only vacuum non-all-visible pages, even when doing it for
anti-wraparound
2) When we can set all-visible guarantee that all tuples on the page are
fully hinted. During recovery do the same, so we don't need to log
all hint bits.
We can do this with only an exclusive lock on the buffer, we don't
need a cleanup lock.
3) When we cannot mark a page all-visible or we cannot get the cleanup
lock, remember the oldest xmin on that page. We could set all visible
in the former case, but we want the page to be cleaned up sometime
soonish.
4) If we can get the cleanup lock, purge dead tuples from the page and
the indexes, just as today. Set the page as all-visible.That way we know that any page that is all-visible doesn't ever need to
look at xmin/xmax since we are sure to have set all relevant hint
bits.Heikki noticed that I made quite the omission here which is that you
would need to mark tuples as all visible as well. I was thinking about
using HEAP_MOVED_OFF | HEAP_MOVED_IN as a hint for that.
We could have a "vacuum_less=true" mode, where instead of marking tuples
all visible
here you actually freeze them, that is set the xid to frozen. You will
get less forensic
capability in exchange of less vacuuming.
Maybe also add an "early_freeze" hint bit to mark this situation.
Or maybe set the tuples frozenxid when un-marking the page as all
visible to delay
the effects a little ?
Hannu
Greetings,
Andres Freund
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2013-05-23 22:09:02 -0400, Robert Haas wrote:
On Thu, May 23, 2013 at 1:51 PM, Andres Freund <andres@2ndquadrant.com> wrote:
So, what I propose instead is basically:
1) only vacuum non-all-visible pages, even when doing it for
anti-wraparoundCheck. We might want an option to force a scan of the whole relation.
Yea, thought of that as well. VACUUM (DEEP) ;).
3) When we cannot mark a page all-visible or we cannot get the cleanup
lock, remember the oldest xmin on that page. We could set all visible
in the former case, but we want the page to be cleaned up sometime
soonish.
I think you mean "in the latter case" not "in the former case". If
not, then I'm confused.
Uh. Yes.
We don't even necessarily need to log the hint bits for all items since
the redo for all_visible could make sure all items are hinted. The only
problem is knowing up to where we can truncate pg_clog...
[all-visible cannot restore hint bits without FPI because of torn pages]
I haven't yet thought about this sufficiently yet. I think we might have
a chance of working around this, let me ponder a bit.
But even if that means needing a full page write via the usual mechanism
for all visible if any hint bits needed to be set we are still out far
ahead of the current state imo.
* cleanup would quite possibly do an FPI shortly after in vacuum
anyway. If we do it for all visible, it possibly does not need to be
done for it.
* freezing would FPI almost guaranteedly since we do it so much
later.
* Not having to rescan the whole heap will be a bigger cost saving...
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 5/24/13 9:53 AM, Andres Freund wrote:
We don't even necessarily need to log the hint bits for all items since
the redo for all_visible could make sure all items are hinted. The only
problem is knowing up to where we can truncate pg_clog...[all-visible cannot restore hint bits without FPI because of torn pages]
I haven't yet thought about this sufficiently yet. I think we might have
a chance of working around this, let me ponder a bit.But even if that means needing a full page write via the usual mechanism
for all visible if any hint bits needed to be set we are still out far
ahead of the current state imo.
* cleanup would quite possibly do an FPI shortly after in vacuum
anyway. If we do it for all visible, it possibly does not need to be
done for it.
* freezing would FPI almost guaranteedly since we do it so much
later.
* Not having to rescan the whole heap will be a bigger cost saving...
Would we only set all the hint bits within vacuum? If so I don't think the WAL hit matters at all, because vacuum is almost always a background, throttled process.
--
Jim C. Nasby, Data Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, May 24, 2013 at 10:53 AM, Andres Freund <andres@2ndquadrant.com> wrote:
[all-visible cannot restore hint bits without FPI because of torn pages]
I haven't yet thought about this sufficiently yet. I think we might have
a chance of working around this, let me ponder a bit.
Yeah. I too feel like there might be a solution. But I don't know
have something specific in mind, yet anyway.
But even if that means needing a full page write via the usual mechanism
for all visible if any hint bits needed to be set we are still out far
ahead of the current state imo.
* cleanup would quite possibly do an FPI shortly after in vacuum
anyway. If we do it for all visible, it possibly does not need to be
done for it.
* freezing would FPI almost guaranteedly since we do it so much
later.
* Not having to rescan the whole heap will be a bigger cost saving...
The basic problem is that if the data is going to be removed before it
would have gotten frozen, then the extra FPIs are just overhead. In
effect, we're just deciding to freeze a lot sooner. And while that
might well be beneficial in some use cases (e.g. the data's already in
cache) it might also not be so beneficial (the table is larger than
cache and would have been dropped before freezing kicked in).
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2013-05-24 11:29:10 -0400, Robert Haas wrote:
But even if that means needing a full page write via the usual mechanism
for all visible if any hint bits needed to be set we are still out far
ahead of the current state imo.
* cleanup would quite possibly do an FPI shortly after in vacuum
anyway. If we do it for all visible, it possibly does not need to be
done for it.
* freezing would FPI almost guaranteedly since we do it so much
later.
* Not having to rescan the whole heap will be a bigger cost saving...The basic problem is that if the data is going to be removed before it
would have gotten frozen, then the extra FPIs are just overhead. In
effect, we're just deciding to freeze a lot sooner.
Well, freezing without removing information for debugging.
And while that
might well be beneficial in some use cases (e.g. the data's already in
cache) it might also not be so beneficial (the table is larger than
cache and would have been dropped before freezing kicked in).
Not sure how caching comes into play here? At this point we know the
page to be in cache already since vacuum is looking at it anyway?
I think it's not really comparable since in those situations we a)
already do an XLogInsert(). b) already dirty the page. so the only
change is that we possibly write an additionall full page image. If
there is actually near future DML write activity that would make the
all-visible superflous that would have to FPI likely anyway.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, May 24, 2013 at 11:29 AM, Robert Haas <robertmhaas@gmail.com> wrote:
On Fri, May 24, 2013 at 10:53 AM, Andres Freund <andres@2ndquadrant.com> wrote:
[all-visible cannot restore hint bits without FPI because of torn pages]
I haven't yet thought about this sufficiently yet. I think we might have
a chance of working around this, let me ponder a bit.Yeah. I too feel like there might be a solution. But I don't know
have something specific in mind, yet anyway.
One thought I had is that it might be beneficial to freeze when a page
ceases to be all-visible, rather than when it becomes all-visible.
Any operation that makes the page not-all-visible is going to emit an
FPI anyway, so we don't have to worry about torn pages in that case.
Under such a scheme, we'd have to enforce the rule that xmin and xmax
are ignored for any page that is all-visible; and when a page ceases
to be all-visible, we have to go back and really freeze the
pre-existing tuples. I think we might be able to use the existing
all_visible_cleared/new_all_visible_cleared flags to trigger this
behavior, without adding anything new to WAL at all.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, May 24, 2013 at 11:52 AM, Andres Freund <andres@2ndquadrant.com> wrote:
The basic problem is that if the data is going to be removed before it
would have gotten frozen, then the extra FPIs are just overhead. In
effect, we're just deciding to freeze a lot sooner.Well, freezing without removing information for debugging.
Sure, but what I'm trying to avoid is incurring the WAL cost of
freezing. If we didn't mind paying that sooner, we could just drop
vacuum_freeze_min/table_age. But we do mind that.
And while that
might well be beneficial in some use cases (e.g. the data's already in
cache) it might also not be so beneficial (the table is larger than
cache and would have been dropped before freezing kicked in).Not sure how caching comes into play here? At this point we know the
page to be in cache already since vacuum is looking at it anyway?
OK, true.
I think it's not really comparable since in those situations we a)
already do an XLogInsert(). b) already dirty the page. so the only
change is that we possibly write an additionall full page image. If
there is actually near future DML write activity that would make the
all-visible superflous that would have to FPI likely anyway.
Well, if there's near-future write activity, then freezing is pretty
worthless anyway. What I'm trying to avoid is adding WAL overhead in
the case where there *isnt* any near-future write activity, like
inserting 100MB of data into an existing table.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Andres,
If I understand your solution correctly, though, this doesn't really
help the pathological case for freezing, which is the time-oriented
append-only table. For data which isn't being used, allvisible won't be
set either because it won't have been read, no? Is it still cheaper to
set allvisible than vacuum freeze even in that case?
Don't get me wrong, I'm in favor of this if it fixes the other (more
common) cases. I just want to be clear on the limitations.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Import Notes
Reply to msg id not found: WM11a4cc75adad1d90bc50891a73083381b9f8ce9288bac98cbdfc931e29d0c7be65d1a1ccdc7ea3881350d35e88ccf795@asav-2.01.com
On 2013-05-24 15:49:31 -0400, Josh Berkus wrote:
If I understand your solution correctly, though, this doesn't really
help the pathological case for freezing, which is the time-oriented
append-only table. For data which isn't being used, allvisible won't be
set either because it won't have been read, no? Is it still cheaper to
set allvisible than vacuum freeze even in that case?
all visible is only set in vacuum and it determines which parts of a
table will be scanned in a non full table vacuum. So, since we won't
regularly start vacuum in the insert only case there will still be a
batch of work at once. But nearly all of that work is *already*
performed. We would just what the details of that around for a
bit. *But* since we now would only need to vacuum the non all-visible
part that would get noticeably cheaper as well.
I think for that case we should run vacuum more regularly for insert
only tables since we currently don't do regularly enough which a) increases
the amount of work needed at once and b) prevents index only scans from
working there.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 05/24/2013 07:00 PM, Robert Haas wrote:
On Fri, May 24, 2013 at 11:29 AM, Robert Haas <robertmhaas@gmail.com> wrote:
On Fri, May 24, 2013 at 10:53 AM, Andres Freund <andres@2ndquadrant.com> wrote:
[all-visible cannot restore hint bits without FPI because of torn pages]
I haven't yet thought about this sufficiently yet. I think we might have
a chance of working around this, let me ponder a bit.Yeah. I too feel like there might be a solution. But I don't know
have something specific in mind, yet anyway.One thought I had is that it might be beneficial to freeze when a page
ceases to be all-visible, rather than when it becomes all-visible.
That what I aimed to describe in my mail earlier, but your
description is much clearer :)
Any operation that makes the page not-all-visible is going to emit an
FPI anyway, so we don't have to worry about torn pages in that case.
Under such a scheme, we'd have to enforce the rule that xmin and xmax
are ignored for any page that is all-visible;
Agreed. We already relay on all-visible pages enough that we
can trust it to be correct. Making that universal rule should not
add any risks .
The rule "page all-visible ==> assume all tuples frozen" would
also enable VACUUM FREEZE to only work only on the
non-all-visible pages .
and when a page ceases
to be all-visible, we have to go back and really freeze the
pre-existing tuples.
We can do this unconditionally, or in milder case use vacuum_freeze_min_age
if we want to retain xids for forensic purposes.
I think we might be able to use the existing
all_visible_cleared/new_all_visible_cleared flags to trigger this
behavior, without adding anything new to WAL at all.
This seems to be easiest
--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic O�
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 24 May 2013 17:00, Robert Haas <robertmhaas@gmail.com> wrote:
On Fri, May 24, 2013 at 11:29 AM, Robert Haas <robertmhaas@gmail.com> wrote:
On Fri, May 24, 2013 at 10:53 AM, Andres Freund <andres@2ndquadrant.com> wrote:
[all-visible cannot restore hint bits without FPI because of torn pages]
I haven't yet thought about this sufficiently yet. I think we might have
a chance of working around this, let me ponder a bit.Yeah. I too feel like there might be a solution. But I don't know
have something specific in mind, yet anyway.One thought I had is that it might be beneficial to freeze when a page
ceases to be all-visible, rather than when it becomes all-visible.
Any operation that makes the page not-all-visible is going to emit an
FPI anyway, so we don't have to worry about torn pages in that case.
Under such a scheme, we'd have to enforce the rule that xmin and xmax
are ignored for any page that is all-visible; and when a page ceases
to be all-visible, we have to go back and really freeze the
pre-existing tuples. I think we might be able to use the existing
all_visible_cleared/new_all_visible_cleared flags to trigger this
behavior, without adding anything new to WAL at all.
I like the idea but it would mean we'd have to freeze in the
foreground path rather in a background path.
Have we given up on the double buffering idea to remove FPIs
completely? If we did that, then this wouldn't work.
Anyway, I take it the direction of this idea is that "we don't need a
separate freezemap, just use the vismap". That seems to be forcing
ideas down a particular route we may regret. I'd rather just keep
those things separate, even if we manage to merge the WAL actions for
most of the time.
Some other related thoughts:
ISTM that if we really care about keeping xids for debug purposes that
it could be a parameter. For the mainline, we just freeze blocks at
the same time we do page pruning.
I think the right way is actually to rethink and simplify all this
complexity of Freezing/Pruning/Hinting/Visibility
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Andres,
all visible is only set in vacuum and it determines which parts of a
table will be scanned in a non full table vacuum. So, since we won't
regularly start vacuum in the insert only case there will still be a
batch of work at once. But nearly all of that work is *already*
performed. We would just what the details of that around for a
bit. *But* since we now would only need to vacuum the non all-visible
part that would get noticeably cheaper as well.
Yeah, I can see that. Seems worthwhile, then.
I think for that case we should run vacuum more regularly for insert
only tables since we currently don't do regularly enough which a) increases
the amount of work needed at once and b) prevents index only scans from
working there.
Yes. I'm not sure how we would set this though; I think it's another
example of how autovacuum's parameters for when to vaccuum etc. are too
simple-minded for the real world. Doing an all-visible scan on an
insert-only table, for example, should be based on XID age and not on %
inserted, no?
Speaking of which, I need to get on revamping the math for autoanalyze.
Mind you, in the real-world insert-only table case, this does create
extra IO -- real insert-only tables often have a few rows ( < 5% ) which
are updated/deleted. Vacuum would see these and want to clean the pages
up, which would create much more substantial IO. It might still be a
good tradeoff, but we should be aware of it.
Unless we want a special VACUUM ALL VISIBLE mode. I vote no, unless we
demonstrate some really convincing case for it.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Import Notes
Reply to msg id not found: WM27c171ea12045d9ea7282967fdc5cd7e0dbbb250673365653d5aaa9f3f3a7f8fe7615bd102ce427ad2c73cdcf1f674a8@asav-2.01.com
On 05/25/2013 01:14 PM, Simon Riggs wrote:
On 24 May 2013 17:00, Robert Haas <robertmhaas@gmail.com> wrote:
On Fri, May 24, 2013 at 11:29 AM, Robert Haas <robertmhaas@gmail.com> wrote:
On Fri, May 24, 2013 at 10:53 AM, Andres Freund <andres@2ndquadrant.com> wrote:
[all-visible cannot restore hint bits without FPI because of torn pages]
I haven't yet thought about this sufficiently yet. I think we might have
a chance of working around this, let me ponder a bit.Yeah. I too feel like there might be a solution. But I don't know
have something specific in mind, yet anyway.One thought I had is that it might be beneficial to freeze when a page
ceases to be all-visible, rather than when it becomes all-visible.
Any operation that makes the page not-all-visible is going to emit an
FPI anyway, so we don't have to worry about torn pages in that case.
Under such a scheme, we'd have to enforce the rule that xmin and xmax
are ignored for any page that is all-visible; and when a page ceases
to be all-visible, we have to go back and really freeze the
pre-existing tuples. I think we might be able to use the existing
all_visible_cleared/new_all_visible_cleared flags to trigger this
behavior, without adding anything new to WAL at all.I like the idea but it would mean we'd have to freeze in the
foreground path rather in a background path.Have we given up on the double buffering idea to remove FPIs
completely? If we did that, then this wouldn't work.Anyway, I take it the direction of this idea is that "we don't need a
separate freezemap, just use the vismap". That seems to be forcing
ideas down a particular route we may regret. I'd rather just keep
those things separate, even if we manage to merge the WAL actions for
most of the time.Some other related thoughts:
ISTM that if we really care about keeping xids for debug purposes that
it could be a parameter. For the mainline, we just freeze blocks at
the same time we do page pruning.I think the right way is actually to rethink and simplify all this
complexity of Freezing/Pruning/Hinting/Visibility
I think that tis xmin, xmax business is mainly leftovers from the time when
PostgreSQL was a full history database. If we are happy to descide that we
do not want to resurrect this feature, at least not the same way, then
freezing
at the earliest or most convenient possibility seems the way to go .
The "forensic" part has always been just a nice side effect of this
design and
not the main design considerataion.
--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic O�
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Andres,
I was talking this over with Jeff on the plane, and we wanted to be
clear on your goals here: are you looking to eliminate the *write* cost
of freezing, or just the *read* cost of re-reading already frozen pages?
If just the latter, what about just adding a bit to the visibility map
to indicate that the page is frozen? That seems simpler than what
you're proposing.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Import Notes
Reply to msg id not found: WMc410b0b6a045a9045f3a8e5d1d1c30d864f864e6ce8d572e277344f913adce45ec5e686ccb8bfce6755bd90396ed45a0@asav-3.01.com
On Sat, May 25, 2013 at 6:14 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
One thought I had is that it might be beneficial to freeze when a page
ceases to be all-visible, rather than when it becomes all-visible.
Any operation that makes the page not-all-visible is going to emit an
FPI anyway, so we don't have to worry about torn pages in that case.
Under such a scheme, we'd have to enforce the rule that xmin and xmax
are ignored for any page that is all-visible; and when a page ceases
to be all-visible, we have to go back and really freeze the
pre-existing tuples. I think we might be able to use the existing
all_visible_cleared/new_all_visible_cleared flags to trigger this
behavior, without adding anything new to WAL at all.I like the idea but it would mean we'd have to freeze in the
foreground path rather in a background path.
That's true, but I think with this approach it would be really cheap.
The overhead of setting a few bits in a page is very small compared to
the overhead of emitting a WAL record. We'd have to test it, but I
wouldn't be surprised to find the cost is too small to measure.
Have we given up on the double buffering idea to remove FPIs
completely? If we did that, then this wouldn't work.
I don't see why those things are mutually exclusive. What is the relationship?
Anyway, I take it the direction of this idea is that "we don't need a
separate freezemap, just use the vismap". That seems to be forcing
ideas down a particular route we may regret. I'd rather just keep
those things separate, even if we manage to merge the WAL actions for
most of the time.
Hmm. To me it seems highly desirable to merge those things, because
they're basically the same thing. The earliest time at which we can
freeze a tuple is when it's all-visible, and the only argument I've
ever heard for waiting longer is to preserve the original xmin for
forensic purposes, which I think we can do anyway. I have posted a
patch for that on another thread. I don't like having two separate
concepts where one will do; I think the fact that it is structured
that way today is mostly an artifact of one setting being page-level
and the other tuple-level, which is a thin excuse for so much
complexity.
I think the right way is actually to rethink and simplify all this
complexity of Freezing/Pruning/Hinting/Visibility
I agree, but I think that's likely to have to wait until we get a
pluggable storage API, and then a few years beyond that for someone to
develop the technology to enable the new and better way. In the
meantime, if we can eliminate or even reduce the impact of freezing in
the near term, I think that's worth doing.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2013-05-26 16:58:58 -0700, Josh Berkus wrote:
I was talking this over with Jeff on the plane, and we wanted to be
clear on your goals here: are you looking to eliminate the *write* cost
of freezing, or just the *read* cost of re-reading already frozen pages?
Both. The latter is what I have seen causing more hurt, but the former
alone is painful enough.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
