Idea for getting rid of VACUUM FREEZE on cold pages
From a discussion at dinner at pgcon, I wanted to send this to the list
for people to poke holes in it:
Problem: currently, if your database has a large amount of "cold" data,
such as 350GB of 3-year-old sales transactions, in 8.4 vacuum no longer
needs to touch it thanks to the visibility map. However, every
freeze_age transactions, very old pages need to be sucked into memory
and rewritten just in order to freeze those pages. This can have a huge
impact on system performance, and seems unjustified because the pages
are not actually being used.
Suggested resolution: we would add a 4-byte field to the *page* header
which would track the XID wraparound count. Any page whose wraparound
count was not equal to the current one would be considered to have all
frozen tuples. This would remove the necessity to read and write old
pages just to freeze them, a humongous gain for databases with long data
retention horizons, let alone data warehouses.
All xids on the page would, necessarily, need to belong to the same
wraparound; if a page gets updated and its wraparound count (hereafter
WCID) is lower than current, all tuples on the page would be frozen
before any data is written to it. XIDs which were before the max_freeze
horizon on a page which was being written anyway would be frozen as they
are now.
Obvious issues:
(1) In a case of rows written close to the wraparound point, this would
cause a set of tuples to be frozen sooner than they would be in the
current system.
(2) It's not clear what to do with a page where there are XIDs which are
just before wraparound (like XID # 2.4b) which are still visible and
receives a write with a new cycle xid (#1).
(3) This will require changing the page structure, with all that
entails. So it should probably be done when we're making another change
(like adding CRCs).
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
On May 21, 2010, at 23:57 , Josh Berkus wrote:
From a discussion at dinner at pgcon, I wanted to send this to the list for people to poke holes in it:
Problem: currently, if your database has a large amount of "cold" data, such as 350GB of 3-year-old sales transactions, in 8.4 vacuum no longer needs to touch it thanks to the visibility map. However, every freeze_age transactions, very old pages need to be sucked into memory and rewritten just in order to freeze those pages. This can have a huge impact on system performance, and seems unjustified because the pages are not actually being used.
Suggested resolution: we would add a 4-byte field to the *page* header which would track the XID wraparound count. Any page whose wraparound count was not equal to the current one would be considered to have all frozen tuples. This would remove the necessity to read and write old pages just to freeze them, a humongous gain for databases with long data retention horizons, let alone data warehouses.
If I understand this correctly, VACUUM usually only frees old tuples, but never increases the oldest xid in the pg_class record. Once that value becomes older than freeze_age, VACUUM needs to scan the whole relation to freeze old tuples. That results in most of the pages being marked dirty and subsequently being written out, causing an IO storm. If, OTOH, the wraparound count was stored in the page header, VACUUM would still need to read those pages, but wouldn't need to write them out.
Alternatively, VACUUM could freeze a few pages on each run, even if the xids are below freeze_age. It could pick those pages randomly, or maybe even prefer pages whose tuples have older xmin/xmas values. That would spread the load out more evenly, much like we try to spread checkpoints out over the whole checkpoint interval.
best regards,
Florian Pflugi
Josh Berkus <josh@agliodbs.com> writes:
From a discussion at dinner at pgcon, I wanted to send this to the list
for people to poke holes in it:
Somebody (I think Joe or Heikki) poked a big hole in this last night at
the Royal Oak. Although the scheme would get rid of the need to replace
old XIDs with FrozenXid, it does not get rid of the need to set hint
bits before you can truncate CLOG. So in your example of an insert-only
table that's probably never read again, there's still a minimum of one
update visit required on every old page. Now that's still better than
two update visits ... but we could manage that already, just by tweaking
vacuum's heuristics about when to freeze vs when to set hint bits.
regards, tom lane
Somebody (I think Joe or Heikki) poked a big hole in this last night at
the Royal Oak. Although the scheme would get rid of the need to replace
old XIDs with FrozenXid, it does not get rid of the need to set hint
bits before you can truncate CLOG. So in your example of an insert-only
table that's probably never read again, there's still a minimum of one
update visit required on every old page. Now that's still better than
two update visits ... but we could manage that already, just by tweaking
vacuum's heuristics about when to freeze vs when to set hint bits.
Yeah, someone pointed that out to me too and suggested that a freeze map
was the better solution. I still think there's something we can do with
pages on the visibility map but I'll have to think about it some more.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
On 5/22/2010 9:16 PM, Tom Lane wrote:
Josh Berkus <josh@agliodbs.com> writes:
Somebody (I think Joe or Heikki) poked a big hole in this last night at
the Royal Oak. Although the scheme would get rid of the need to replace
old XIDs with FrozenXid, it does not get rid of the need to set hint
bits before you can truncate CLOG. So in your example of an insert-only
table that's probably never read again, there's still a minimum of one
update visit required on every old page. Now that's still better than
two update visits ... but we could manage that already, just by tweaking
vacuum's heuristics about when to freeze vs when to set hint bits.Yeah, someone pointed that out to me too and suggested that a freeze map
was the better solution. I still think there's something we can do with
pages on the visibility map but I'll have to think about it some more.It occurred to me on the flight home that maybe we could salvage
something from this if there were some mechanism that caused hint bits
to get set before the page got written out from shared buffers the first
time. This assumes that you have enough slack in shared-buffer space
that the transactions that touched a particular page all commit or abort
before the page first gets flushed to disk.
At least the background writer should have a few spare cycles to look
over a "to be flushed" page before writing it.
Jan
--
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin
Import Notes
Reply to msg id not found: 7319.1274577417@sss.pgh.pa.us
Josh Berkus <josh@agliodbs.com> writes:
Somebody (I think Joe or Heikki) poked a big hole in this last night at
the Royal Oak. Although the scheme would get rid of the need to replace
old XIDs with FrozenXid, it does not get rid of the need to set hint
bits before you can truncate CLOG. So in your example of an insert-only
table that's probably never read again, there's still a minimum of one
update visit required on every old page. Now that's still better than
two update visits ... but we could manage that already, just by tweaking
vacuum's heuristics about when to freeze vs when to set hint bits.
Yeah, someone pointed that out to me too and suggested that a freeze map
was the better solution. I still think there's something we can do with
pages on the visibility map but I'll have to think about it some more.
It occurred to me on the flight home that maybe we could salvage
something from this if there were some mechanism that caused hint bits
to get set before the page got written out from shared buffers the first
time. This assumes that you have enough slack in shared-buffer space
that the transactions that touched a particular page all commit or abort
before the page first gets flushed to disk.
regards, tom lane
On 22/05/10 16:35, Tom Lane wrote:
Josh Berkus<josh@agliodbs.com> writes:
From a discussion at dinner at pgcon, I wanted to send this to the list
for people to poke holes in it:Somebody (I think Joe or Heikki) poked a big hole in this last night at
the Royal Oak.
Me.
Although the scheme would get rid of the need to replace
old XIDs with FrozenXid, it does not get rid of the need to set hint
bits before you can truncate CLOG.
Hmm, we don't rely on setting hint bits to truncate CLOG anymore
(http://archives.postgresql.org/pgsql-committers/2006-11/msg00026.php).
It's the replacement of xids with FrozenXid that matters, the hint bits
are really just hints.
Doesn't change the conclusion, though: you still need to replace XIDs
with FrozenXids to truncate the clog. Conceivably we could keep around
more than 2^32 transactions in clog with this scheme, but then you need
a lot more space for the clog. But perhaps it would be better to do that
than to launch anti-wraparound vacuums, or to refuse more updates in the
extreme cases.
So in your example of an insert-only
table that's probably never read again, there's still a minimum of one
update visit required on every old page. Now that's still better than
two update visits ... but we could manage that already, just by tweaking
vacuum's heuristics about when to freeze vs when to set hint bits.
(As also discussed in the Royal Oak) I think we should simply not dirty
a page when a hint bit is updated. Reading a page from disk is
expensive, setting hint bits on the access is generally cheap compared
to that. But that is orthogonal to the idea of a per-page XID epoch.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
(As also discussed in the Royal Oak) I think we should simply not dirty
a page when a hint bit is updated. Reading a page from disk is
expensive, setting hint bits on the access is generally cheap compared
to that. But that is orthogonal to the idea of a per-page XID epoch.
I'm not sure it's cheap. What you suggest would result in a substantial
increase in clog accesses, which means (1) more I/O and (2) more
contention. Certainly it's worth experimenting with, but it's no
guaranteed win.
regards, tom lane
I'm not sure it's cheap. What you suggest would result in a substantial
increase in clog accesses, which means (1) more I/O and (2) more
contention. Certainly it's worth experimenting with, but it's no
guaranteed win.
It seems like there's a number of issues we could fix by making the CLOG
more efficient somehow -- from the elimination of hint bits to the
ability to freeze pages without writing them.
Not, of course, that I have any idea how to do that.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
Excerpts from Josh Berkus's message of vie may 21 17:57:35 -0400 2010:
Problem: currently, if your database has a large amount of "cold" data,
such as 350GB of 3-year-old sales transactions, in 8.4 vacuum no longer
needs to touch it thanks to the visibility map. However, every
freeze_age transactions, very old pages need to be sucked into memory
and rewritten just in order to freeze those pages. This can have a huge
impact on system performance, and seems unjustified because the pages
are not actually being used.
I think this is nonsense. If you have 3-years-old sales transactions,
and your database has any interesting churn, tuples those pages have
been frozen for a very long time *already*. The problem is vacuum
reading them in so that it can verify there's nothing to do. If we want
to avoid *reading* those pages, this solution is useless:
Suggested resolution: we would add a 4-byte field to the *page* header
which would track the XID wraparound count.
because you still have to read the page.
I think what you're looking for is for this Xid wraparound count to be
stored elsewhere, not inside the page. That way vacuum can read it and
skip the page without reading it altogether. I think a "freeze map" has
been mentioned downthread.
I remember mentioning some time ago that we could declare some tables as
frozen, i.e. "not needing vacuum". This strikes me as similar, except
at the page level rather than table level.
--
Álvaro Herrera <alvherre@alvh.no-ip.org>
On 24/05/10 22:49, Alvaro Herrera wrote:
Excerpts from Josh Berkus's message of vie may 21 17:57:35 -0400 2010:
Problem: currently, if your database has a large amount of "cold" data,
such as 350GB of 3-year-old sales transactions, in 8.4 vacuum no longer
needs to touch it thanks to the visibility map. However, every
freeze_age transactions, very old pages need to be sucked into memory
and rewritten just in order to freeze those pages. This can have a huge
impact on system performance, and seems unjustified because the pages
are not actually being used.I think this is nonsense. If you have 3-years-old sales transactions,
and your database has any interesting churn, tuples those pages have
been frozen for a very long time *already*. The problem is vacuum
reading them in so that it can verify there's nothing to do. If we want
to avoid *reading* those pages, this solution is useless:Suggested resolution: we would add a 4-byte field to the *page* header
which would track the XID wraparound count.because you still have to read the page.
What's missing from the suggestion is that relfrozenxid and datfrozenxid
also need to be expanded to 8-bytes. That way you effectively have
8-byte XIDs, which means that you never need to vacuum to avoid XID
wraparound.
You still need to freeze to truncate clog, though, but if you have the
disk space, you can now do that every 100 billion transactions for
example if you wish.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Excerpts from Heikki Linnakangas's message of mar may 25 04:41:30 -0400 2010:
On 24/05/10 22:49, Alvaro Herrera wrote:
I think this is nonsense. If you have 3-years-old sales transactions,
and your database has any interesting churn, tuples those pages have
been frozen for a very long time *already*.
What's missing from the suggestion is that relfrozenxid and datfrozenxid
also need to be expanded to 8-bytes. That way you effectively have
8-byte XIDs, which means that you never need to vacuum to avoid XID
wraparound.
Hmm, so are we going to use the "xid epoch" more officially? That's
entirely a new line of development, perhaps it opens new possibilities.
This sounds like extending Xid to 64 bits, without having to store the
high bits everywhere. Was this discussed in the PGCon devs meeting?
--
Álvaro Herrera <alvherre@alvh.no-ip.org>
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
This sounds like extending Xid to 64 bits, without having to store the
high bits everywhere. Was this discussed in the PGCon devs meeting?
Yeah, that's what it would amount to. It was not discussed at the dev
meeting --- it was an idea that came up one evening at PGCon.
I'm not sure whether this would imply having to widen xid to 64 bits
internally. That could be a bit unpleasant as far as CPU and shared
memory space go, although every year that goes by makes 32-bit machines
less interesting as DB servers.
regards, tom lane
Alvaro,
This sounds like extending Xid to 64 bits, without having to store the
high bits everywhere. Was this discussed in the PGCon devs meeting?
Essentially, yes.
One of the main objections to raising XID to 64-bit has been the per-row
overhead. But adding 4 bytes per page wouldn't be much of an impact.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
On 5/24/2010 9:30 AM, Heikki Linnakangas wrote:
On 22/05/10 16:35, Tom Lane wrote:
Josh Berkus<josh@agliodbs.com> writes:
From a discussion at dinner at pgcon, I wanted to send this to the list
for people to poke holes in it:Somebody (I think Joe or Heikki) poked a big hole in this last night at
the Royal Oak.Me.
Although the scheme would get rid of the need to replace
old XIDs with FrozenXid, it does not get rid of the need to set hint
bits before you can truncate CLOG.Hmm, we don't rely on setting hint bits to truncate CLOG anymore
(http://archives.postgresql.org/pgsql-committers/2006-11/msg00026.php).
It's the replacement of xids with FrozenXid that matters, the hint bits
are really just hints.Doesn't change the conclusion, though: you still need to replace XIDs
with FrozenXids to truncate the clog. Conceivably we could keep around
more than 2^32 transactions in clog with this scheme, but then you need
a lot more space for the clog. But perhaps it would be better to do that
than to launch anti-wraparound vacuums, or to refuse more updates in the
extreme cases.
Correct. The problem actually are aborted transactions. Just because an
XID is really old doesn't mean it was committed.
Jan
--
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin
Correct. The problem actually are aborted transactions. Just because an
XID is really old doesn't mean it was committed.
Yes, that's the main issue with my idea; XIDs which fell off the CLOG
would become visible even if they'd aborted.
Do we get a bit in the visibility map for a page which has aborted
transaction rows on it?
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
On 25/05/10 23:56, Josh Berkus wrote:
Do we get a bit in the visibility map for a page which has aborted
transaction rows on it?
If there's a tuple with an aborted xmin on a page, the bit in the
visibility map is not set. A tuple with aborted xmax doesn't matter.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
On 5/25/10 10:04 PM, Heikki Linnakangas wrote:
On 25/05/10 23:56, Josh Berkus wrote:
Do we get a bit in the visibility map for a page which has aborted
transaction rows on it?If there's a tuple with an aborted xmin on a page, the bit in the
visibility map is not set. A tuple with aborted xmax doesn't matter.
Then it seems like pages in the visibility map, at least, would not need
to be vacuumed or frozen. Do pages persist in the visibility map
indefinitely?
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
On 26/05/10 21:35, Josh Berkus wrote:
On 5/25/10 10:04 PM, Heikki Linnakangas wrote:
On 25/05/10 23:56, Josh Berkus wrote:
Do we get a bit in the visibility map for a page which has aborted
transaction rows on it?If there's a tuple with an aborted xmin on a page, the bit in the
visibility map is not set. A tuple with aborted xmax doesn't matter.Then it seems like pages in the visibility map, at least, would not need
to be vacuumed or frozen. Do pages persist in the visibility map
indefinitely?
In theory, until any tuple on the page is inserted/updated/deleted
again. However, we've been operating on the assumption that it's always
safe to clear any bit in the visibility map, without affecting
correctness. I would not like to give up that assumption, it makes life
easier.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
In theory, until any tuple on the page is inserted/updated/deleted
again. However, we've been operating on the assumption that it's always
safe to clear any bit in the visibility map, without affecting
correctness. I would not like to give up that assumption, it makes life
easier.
It wouldn't affect correctness, it would just force that page to be
vacuumed-and-frozen. I think I can make this work, let me just hammer
it out.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
