Summary of plans to avoid the annoyance of Freezing
Freezing is painful for VLDBs and high transaction rate systems. We have a
number of proposals to improve things...
1. Allow parallel cores to be used with vacuumdb (Dilip)
Idea is if we have to get the job done, lets do it as fast as we can using
brute force. Reasonable thinking, but there are more efficient approaches.
2. Freeze Map (Sawada)
This works and we have a mostly-ready patch. I'm down to do final review on
this, which is why I'm producing this summary and working out what's the
best next action for Postgres.
3. Speed up autovacuums when they are triggered to avoid wraparounds (Simon)
Idea is to do a VACUUM scan which only freezes tuples. If we dirty a page
from freezing we then also prune it, but don't attempt to scan indexes to
remove the now-truncated dead tuples.
This looks very straightforward, no technical issues. Might even be able to
backpatch it.
[patch investigated but not finished yet]
4. 64-bit Xids (Alexander)
Proposal rejected
5. 64-bit Xid Visibility, with Xid Epoch in page header (Heikki)
* Epoch is stored on new pages, using a new page format. Epoch of existing
pages is stored in control file - code changes to bufpage.c look isolated
* All transaction age tests are made using both xid and epoch - the code
changes to tqual.c look isolated
* We won't need to issue anti-wraparound vacuums again, ever
* We will still need to issue clog-trimming vacuums, so we'll need a new
parameter to control when to start scanning to avoid clog growing too large
* relfrozenxid will be set to InvalidTransactionId once all existing pages
have been made visible
* relhintxid and relhintepoch will be required so we have a backstop to
indicate where to truncate clog (and same for multixacts)
* There is no need to FREEZE except when UPDATEing/DELETEing pages from
previous epochs
* VACUUM FREEZE will only freeze old pages; on a new cluster it will work
same as VACUUM
* VACUUMs that touch every non-all-visible page will be able to set
relhintxid to keep clog trimmed, so never need to scan all blocks in table
* Code changes seem fairly isolated
* No action required at pg_upgrade
* Additional benefit: we can move to 32-bit CRC checksums on data pages at
same time as doing this (seamlessly).
* 8 bytes additional space required per page (~0.1% growth in database size)
* (Any other changes to page headers can be made or reserved at this time)
To me 3) would have been useful if we'd done it earlier. Now that we have
2) and 5), I don't see much point pursuing 3).
The main options are 2) or 5)
Freeze Map (2) makes Freezing more efficient for larger tables, but doesn't
avoid the need altogether. 5) is a deeper treatment of the underlying
problem and is a better architecture for the future of Postgres, IMHO.
I was previously a proponent of (2) as a practical way forwards, but my
proposal here today is that we don't do anything further on 2) yet, and
seek to make progress on 5) instead.
If 5) fails to bring a workable solution by the Jan 2016 CF then we commit
2) instead.
If Heikki wishes to work on (5), that's good. Otherwise, I think its
something I can understand and deliver by 1 Jan, though likely for 1 Nov CF.
--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Sun, Aug 9, 2015 at 11:03 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
If 5) fails to bring a workable solution by the Jan 2016 CF then we commit
2) instead.
Is there actually a conflict there? I didn't think so.
--
Peter Geoghegan
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 10 August 2015 at 07:14, Peter Geoghegan <pg@heroku.com> wrote:
On Sun, Aug 9, 2015 at 11:03 PM, Simon Riggs <simon@2ndquadrant.com>
wrote:If 5) fails to bring a workable solution by the Jan 2016 CF then we
commit
2) instead.
Is there actually a conflict there? I didn't think so.
I didn't explain myself fully, thank you for asking.
Having a freeze map would be wholly unnecessary if we don't ever need to
freeze whole tables again. Freezing would still be needed on individual
blocks where an old row has been updated or deleted; a freeze map would not
help there either.
So there is no conflict, but options 2) and 3) are completely redundant if
we go for 5). After investigation, I now think 5) is achievable in 9.6, but
if I am wrong for whatever reason, we have 2) as a backstop.
--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Having a freeze map would be wholly unnecessary if we don't ever need to
freeze whole tables again. Freezing would still be needed on individual
blocks where an old row has been updated or deleted; a freeze map would not
help there either.
So there is no conflict, but options 2) and 3) are completely redundant if
we go for 5). After investigation, I now think 5) is achievable in 9.6, but
if I am wrong for whatever reason, we have 2) as a backstop for more go to h
ttp://www.pillenpalast.com/ <http://www.pillenpalast.com/>
-----
Kamagra http://www.pillenpalast.com/
--
View this message in context: http://postgresql.nabble.com/Summary-of-plans-to-avoid-the-annoyance-of-Freezing-tp5861530p5861534.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2015-08-10 07:26:29 +0100, Simon Riggs wrote:
On 10 August 2015 at 07:14, Peter Geoghegan <pg@heroku.com> wrote:
On Sun, Aug 9, 2015 at 11:03 PM, Simon Riggs <simon@2ndquadrant.com>
wrote:If 5) fails to bring a workable solution by the Jan 2016 CF then we
commit
2) instead.
Is there actually a conflict there? I didn't think so.
I didn't explain myself fully, thank you for asking.
Having a freeze map would be wholly unnecessary if we don't ever need to
freeze whole tables again. Freezing would still be needed on individual
blocks where an old row has been updated or deleted; a freeze map would not
help there either.So there is no conflict, but options 2) and 3) are completely redundant if
we go for 5). After investigation, I now think 5) is achievable in 9.6, but
if I am wrong for whatever reason, we have 2) as a backstop.
I don't think that's true. You can't ever delete the clog without
freezing. There's no need for anti-wraparound scans anymore, but you
still need to freeze once.
Andres
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 08/10/2015 11:17 AM, Andres Freund wrote:
On 2015-08-10 07:26:29 +0100, Simon Riggs wrote:
On 10 August 2015 at 07:14, Peter Geoghegan <pg@heroku.com> wrote:
On Sun, Aug 9, 2015 at 11:03 PM, Simon Riggs <simon@2ndquadrant.com>
wrote:If 5) fails to bring a workable solution by the Jan 2016 CF then we
commit
2) instead.
Is there actually a conflict there? I didn't think so.
I didn't explain myself fully, thank you for asking.
Having a freeze map would be wholly unnecessary if we don't ever need to
freeze whole tables again. Freezing would still be needed on individual
blocks where an old row has been updated or deleted; a freeze map would not
help there either.So there is no conflict, but options 2) and 3) are completely redundant if
we go for 5). After investigation, I now think 5) is achievable in 9.6, but
if I am wrong for whatever reason, we have 2) as a backstop.I don't think that's true. You can't ever delete the clog without
freezing. There's no need for anti-wraparound scans anymore, but you
still need to freeze once.
What's your definition of freezing? As long as you remove all dead
tuples, you can just leave the rest in place with their original
XID+epoch in place, and assume that everything old enough is committed.
- Heikki
--
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: 22648_1439194662_55C85E26_22648_5172_1_20150810081714.GA16192@awork2.anarazel.de
On 2015-08-10 11:25:37 +0300, Heikki Linnakangas wrote:
On 08/10/2015 11:17 AM, Andres Freund wrote:
On 2015-08-10 07:26:29 +0100, Simon Riggs wrote:
So there is no conflict, but options 2) and 3) are completely redundant if
we go for 5). After investigation, I now think 5) is achievable in 9.6, but
if I am wrong for whatever reason, we have 2) as a backstop.I don't think that's true. You can't ever delete the clog without
freezing. There's no need for anti-wraparound scans anymore, but you
still need to freeze once.What's your definition of freezing? As long as you remove all dead tuples,
you can just leave the rest in place with their original XID+epoch in place,
and assume that everything old enough is committed.
Hm. Right. -ENCOFFEE (I really ran out of beans). Sorry for that.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Simon,
Thank you for this summary! I was losing track, myself.
On 08/09/2015 11:03 PM, Simon Riggs wrote:
Freezing is painful for VLDBs and high transaction rate systems. We have
a number of proposals to improve things...
3. Speed up autovacuums when they are triggered to avoid wraparounds (Simon)
Idea is to do a VACUUM scan which only freezes tuples. If we dirty a
page from freezing we then also prune it, but don't attempt to scan
indexes to remove the now-truncated dead tuples.
This looks very straightforward, no technical issues. Might even be able
to backpatch it.
[patch investigated but not finished yet]
There's a lesser version of this item which remains relevant unless we
implement (5). That is, currently the same autovacuum_vaccuum_delay
(AVVD) applies to regular autovacuums as does to anti-wraparound
autovacuums. If the user has set AV with a high delay, this means that
anti-wraparound AV may never complete. For that reason, we ought to
have a separate parameter for AVVD, which defaults to a lower number
(like 5ms), or even to zero.
Of course, if we implement (5), that's not necessary, since AV will
never trigger an anti-wraparound freeze.
Having a freeze map would be wholly unnecessary if we don't ever need to
freeze whole tables again. Freezing would still be needed on individual
blocks where an old row has been updated or deleted; a freeze map would
not help there either.So there is no conflict, but options 2) and 3) are completely redundant
if we go for 5). After investigation, I now think 5) is achievable in
9.6, but if I am wrong for whatever reason, we have 2) as a backstop.
It's not redundant. Users may still want to freeze for two reasons:
1. to shrink the clog and multixact logs
2. to support INDEX-ONLY SCAN
In both of those cases, having a freeze map would speed up the manual
vacuum freeze considerably. Otherwise, we're just punting on the
problem, and making it worse for users who wait too long.
Now, it might still be the case that the *overhead* of a freeze map is a
bad tradeoff if we don't have to worry about forced wraparound. But
that's a different argument.
BTW, has it occured to anyone that implementing XID epoch headers is
going to mean messing with multixact logs again? Just thought I'd open
a papercut and pour some lemon juice on 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: WM906188f6b8231f54e239ea65290436f86136b784c23f7cc795f785f1fc667003ffcbaff052f0f12cef38d5ae24963c58@asav-2.01.com
On 10 August 2015 at 18:02, Josh Berkus <josh@agliodbs.com> wrote:
There's a lesser version of this item which remains relevant unless we
implement (5). That is, currently the same autovacuum_vaccuum_delay
(AVVD) applies to regular autovacuums as does to anti-wraparound
autovacuums. If the user has set AV with a high delay, this means that
anti-wraparound AV may never complete. For that reason, we ought to
have a separate parameter for AVVD, which defaults to a lower number
(like 5ms), or even to zero.Of course, if we implement (5), that's not necessary, since AV will
never trigger an anti-wraparound freeze.
Good idea.
Having a freeze map would be wholly unnecessary if we don't ever need to
freeze whole tables again. Freezing would still be needed on individual
blocks where an old row has been updated or deleted; a freeze map would
not help there either.So there is no conflict, but options 2) and 3) are completely redundant
if we go for 5). After investigation, I now think 5) is achievable in
9.6, but if I am wrong for whatever reason, we have 2) as a backstop.It's not redundant. Users may still want to freeze for two reasons:
1. to shrink the clog and multixact logs
2. to support INDEX-ONLY SCAN
Freezing is not a necessary pre-condition for either of those things, I am
happy to say. There is confusion here because for ( 1 ) the shrink was
performed after freezing, but when you have access to the epoch there is no
need for exhaustive freezing - only in special cases, as noted. If we are
lucky those special cases will mean a massive reduction in I/O. For ( 2 ) a
normal VACUUM is sufficient and as Robert observes, maybe just HOT is
enough.
In the new world, the clog can be shrunk when everything has been hinted.
Given that is possible with just a normal VACUUM, I think the new
anti-freeze design (hey, we need a name, right?) will mean the clog
actually stays smaller in most cases than it does now.
In both of those cases, having a freeze map would speed up the manual
vacuum freeze considerably. Otherwise, we're just punting on the
problem, and making it worse for users who wait too long.
There would be no further need for the VACUUM FREEZE command. It would do
nothing desirable.
Now, it might still be the case that the *overhead* of a freeze map is a
bad tradeoff if we don't have to worry about forced wraparound. But
that's a different argument.
I myself was in favour of the freeze map solution for some time, but I'm
not anymore. Thank discussions at Pgcon slowly working their way into my
brain.
BTW, has it occured to anyone that implementing XID epoch headers is
going to mean messing with multixact logs again? Just thought I'd open
a papercut and pour some lemon juice on it.
I doubt we have seen the last of that pain, but its not my fingers on the
chopping board, so squeeze all you want. ;-)
--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 08/10/2015 10:31 AM, Simon Riggs wrote:
Freezing is not a necessary pre-condition for either of those things, I
am happy to say. There is confusion here because for ( 1 ) the shrink
was performed after freezing, but when you have access to the epoch
there is no need for exhaustive freezing - only in special cases, as
noted. If we are lucky those special cases will mean a massive reduction
in I/O. For ( 2 ) a normal VACUUM is sufficient and as Robert observes,
maybe just HOT is enough.
Yeah, saw your explanation on this on the other thread. Good point.
Question: does regular vacuum update the visibility map in the same way
vacuum freeze does?
--
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: WM26b5fbda52a29a5e32a4602ca8ff8e7d459a2444dcfaec2412bc9eb1ff7e461bcb79254c0b7b1587799cf15da9318043@asav-3.01.com
On 10 August 2015 at 19:21, Josh Berkus <josh@agliodbs.com> wrote:
Question: does regular vacuum update the visibility map in the same way
vacuum freeze does?
Yes
--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2015-08-10 07:03:02 +0100, Simon Riggs wrote:
I was previously a proponent of (2) as a practical way forwards, but my
proposal here today is that we don't do anything further on 2) yet, and
seek to make progress on 5) instead.If 5) fails to bring a workable solution by the Jan 2016 CF then we commit
2) instead.If Heikki wishes to work on (5), that's good. Otherwise, I think its
something I can understand and deliver by 1 Jan, though likely for 1 Nov CF.
I highly doubt that we can get either variant into 9.6 if we only start
to seriously review them by then. Heikki's lsn ranges patch essentially
was a variant of 5) and it ended up being a rather complicated patch. I
don't think using an explicit epoch is going to be that much simpler.
So I think we need to decide now.
My vote is that we should try to get freeze maps into 9.6 - that seems
more realistic given that we have a patch right now. Yes, it might end
up being superflous churn, but it's rather localized. I think around
we've put off significant incremental improvements off with the promise
of more radical stuff too often.
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 9/6/15 7:25 AM, Andres Freund wrote:
On 2015-08-10 07:03:02 +0100, Simon Riggs wrote:
I was previously a proponent of (2) as a practical way forwards, but my
proposal here today is that we don't do anything further on 2) yet, and
seek to make progress on 5) instead.If 5) fails to bring a workable solution by the Jan 2016 CF then we commit
2) instead.If Heikki wishes to work on (5), that's good. Otherwise, I think its
something I can understand and deliver by 1 Jan, though likely for 1 Nov CF.I highly doubt that we can get either variant into 9.6 if we only start
to seriously review them by then. Heikki's lsn ranges patch essentially
was a variant of 5) and it ended up being a rather complicated patch. I
don't think using an explicit epoch is going to be that much simpler.So I think we need to decide now.
My vote is that we should try to get freeze maps into 9.6 - that seems
more realistic given that we have a patch right now. Yes, it might end
up being superflous churn, but it's rather localized. I think around
we've put off significant incremental improvements off with the promise
of more radical stuff too often.
I'm concerned with how to test this. Right now it's rather difficult to
test things like epoch rollover, especially in a way that would expose
race conditions and other corner cases. We obviously got burned by that
on the MultiXact changes, and a lot of our best developers had to spend
a huge amount of time fixing that. ISTM that a way to unit test things
like CLOG/MXID truncation and visibility logic should be created before
attempting a change like this. Would having this kind of test
infrastructure have helped with the LSN patch development? More
importantly, would it have reduced the odds of the MXID bugs, or made it
easier to diagnose them?
In any case, thanks Simon for the summary. I really like the idea and
will help with it if I can.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Sun, Sep 6, 2015 at 8:25 AM, Andres Freund <andres@anarazel.de> wrote:
On 2015-08-10 07:03:02 +0100, Simon Riggs wrote:
I was previously a proponent of (2) as a practical way forwards, but my
proposal here today is that we don't do anything further on 2) yet, and
seek to make progress on 5) instead.If 5) fails to bring a workable solution by the Jan 2016 CF then we commit
2) instead.If Heikki wishes to work on (5), that's good. Otherwise, I think its
something I can understand and deliver by 1 Jan, though likely for 1 Nov CF.I highly doubt that we can get either variant into 9.6 if we only start
to seriously review them by then. Heikki's lsn ranges patch essentially
was a variant of 5) and it ended up being a rather complicated patch. I
don't think using an explicit epoch is going to be that much simpler.So I think we need to decide now.
My vote is that we should try to get freeze maps into 9.6 - that seems
more realistic given that we have a patch right now. Yes, it might end
up being superflous churn, but it's rather localized. I think around
we've put off significant incremental improvements off with the promise
of more radical stuff too often.
I strongly support that plan. I think it's unlikely we're going to
have something better ready in time for 9.6, and freeze maps by
themselves would bring enormous relief to many people. And the worst
thing that happens if we rip this back out again because it isn't
needed, which isn't really going to be a lot of work.
--
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 Sun, Sep 6, 2015 at 1:25 PM, Andres Freund <andres@anarazel.de> wrote:
My vote is that we should try to get freeze maps into 9.6 - that seems
more realistic given that we have a patch right now. Yes, it might end
up being superflous churn, but it's rather localized. I think around
we've put off significant incremental improvements off with the promise
of more radical stuff too often.
Superfluous churn in the code isn't too bad. But superfluous churn in
data formats might be a bit more scary. Would we be able to handle
pg_upgrade from a database with or without a freezemap? Would you have
to upgrade once to add the freezemap then again to remove it?
--
greg
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Sep 9, 2015 at 10:03 PM, Greg Stark <stark@mit.edu> wrote:
On Sun, Sep 6, 2015 at 1:25 PM, Andres Freund <andres@anarazel.de> wrote:
My vote is that we should try to get freeze maps into 9.6 - that seems
more realistic given that we have a patch right now. Yes, it might end
up being superflous churn, but it's rather localized. I think around
we've put off significant incremental improvements off with the promise
of more radical stuff too often.Superfluous churn in the code isn't too bad. But superfluous churn in
data formats might be a bit more scary. Would we be able to handle
pg_upgrade from a database with or without a freezemap? Would you have
to upgrade once to add the freezemap then again to remove it?
Currently freeze map patch adds frozen bit to visibility map when
upgrading to 9.6.
The visibility map is not critical information, and is generated by VACUUM.
So we can drop it and create new visibility map by doing VACUUM, if
table size is not large.
Regards,
--
Masahiko Sawada
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Sep 9, 2015 at 6:03 AM, Greg Stark <stark@mit.edu> wrote:
On Sun, Sep 6, 2015 at 1:25 PM, Andres Freund <andres@anarazel.de> wrote:
My vote is that we should try to get freeze maps into 9.6 - that seems
more realistic given that we have a patch right now. Yes, it might end
up being superflous churn, but it's rather localized. I think around
we've put off significant incremental improvements off with the promise
of more radical stuff too often.Superfluous churn in the code isn't too bad. But superfluous churn in
data formats might be a bit more scary. Would we be able to handle
pg_upgrade from a database with or without a freezemap? Would you have
to upgrade once to add the freezemap then again to remove it?
Surely we wouldn't introduce and remove freeze-maps between minor
versions. So either it is a new major version, in which case you would be
doing the upgrade anyway, or they would be added and then removed again all
within one development cycle; and running unreleased code always has
on-disk incompatibility churn. Or am I missing your point here?
Cheers,
Jeff
* My vote is that we should try to get freeze maps into 9.6 - that seems
* More realistic given that we have a patch right now. Yes, it might end
* Up being superfluous churn, but it's rather localized. I think around
*We’ve put off significant incremental improvements off with the promise
*Of more radical stuff too often.
Superfluous churn in the code isn't too bad. But superfluous churn in
Data formats might be a bit scarier. Would we be able to handle
pg_upgrade from a database with or without a freeze map? Would you have?
To upgrade once to add the freeze map then again to remove it?
Surely we wouldn't introduce and remove freeze-maps between minor versions.
So either it is a new major version, in which case you would be doing the
upgrade anyway, or they would be added and then removed again all within one
development cycle; and running unreleased code always has on-disk
incompatibility churn. Or am I missing your point here?
Cheers,
Martin
-----
Kamagra
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html