Idea for getting rid of VACUUM FREEZE on cold pages
in [PgSql]
|
Prev: [HACKERS] beta testing - planner bug - ERROR: XX000: failed to build any 2-way joins
Next: [HACKERS] beta testing - pg_upgrade bug fix - double free
From: Josh Berkus on 26 May 2010 17:01 > What if we drove it off of the PD_ALL_VISIBLE bit on the page itself, > rather than the visibility map bit? It would be safe to clear the > visibility map bit without touching the page, but if you clear the > PD_ALL_VISIBLE bit on the page itself then you set all the hint bits > and freeze all the tuples. In the case where the visibility map bit > gets cleared but the page-level bit is still set, a future vacuum can > notice and reset the visibility map bit. But whenever the visibility > map bit is set, you know that the page-level bit MUST be set, so you > needn't vacuum those pages, even for anti-wraparound: you know they'll > be frozen when and if they ever get written again. How does that get us out of reading and writing old pages, though? If we're going to set a bit on them, we might as well freeze them. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 26 May 2010 20:06 Josh Berkus <josh(a)agliodbs.com> writes: > How does that get us out of reading and writing old pages, though? Yeah. Neither PD_ALL_VISIBLE nor the visibility map are going to solve your problem, because they cannot become set without having visited the page. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 26 May 2010 20:48 On Wed, May 26, 2010 at 8:06 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > Josh Berkus <josh(a)agliodbs.com> writes: >> How does that get us out of reading and writing old pages, though? > > Yeah. Neither PD_ALL_VISIBLE nor the visibility map are going to solve > your problem, because they cannot become set without having visited the > page. Well, maybe I'm confused here, but arranging things so that we NEVER have to visit the page after initially writing it seems like it's setting the bar almost impossibly high. Consider a table that is regularly written but append-only. Every time autovacuum kicks in, we'll go and remove any dead tuples and then mark the pages PD_ALL_VISIBLE and set the visibility map bits, which will cause subsequent vacuums to ignore the all-visible portions of the table... until anti-wraparound kicks in, at which point we'll vacuum the entire table and freeze everything. If, however, we decree that you can't write a new tuple into a PD_ALL_VISIBLE page without freezing the existing tuples, then you'll still have the small, incremental vacuums but those are pretty cheap, and in any event, I don't see any way to get rid of them unless someone can devise a scheme to do away with vacuum entirely. But you won't need the full-table vacuum to freeze tuples, because you can freeze them opportunistically the next time those pages are written (at which point freezing will be very cheap because the page has to be written to disk at that point no matter what). Maybe I'm confused. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 26 May 2010 20:59 Robert Haas <robertmhaas(a)gmail.com> writes: > On Wed, May 26, 2010 at 8:06 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: >> Yeah. �Neither PD_ALL_VISIBLE nor the visibility map are going to solve >> your problem, because they cannot become set without having visited the >> page. > Well, maybe I'm confused here, but arranging things so that we NEVER > have to visit the page after initially writing it seems like it's > setting the bar almost impossibly high. Well, that was the use-case that Josh was on about when this idea came up: high-volume append-only log tables that in most cases will never be read, so his client wants to get rid of the extra I/O for maintenance visits to once-written pages. If you're willing to allow one visit and rewrite of each page, then we can do that today with maybe a bit of rejiggering of vacuum's when-to-freeze heuristics. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 26 May 2010 21:32
On Wed, May 26, 2010 at 8:59 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas(a)gmail.com> writes: >> On Wed, May 26, 2010 at 8:06 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: >>> Yeah. Neither PD_ALL_VISIBLE nor the visibility map are going to solve >>> your problem, because they cannot become set without having visited the >>> page. > >> Well, maybe I'm confused here, but arranging things so that we NEVER >> have to visit the page after initially writing it seems like it's >> setting the bar almost impossibly high. > > Well, that was the use-case that Josh was on about when this idea came > up: high-volume append-only log tables that in most cases will never be > read, so his client wants to get rid of the extra I/O for maintenance > visits to once-written pages. Well, I'll just note that using PD_ALL_VISIBLE as I'm proposing is basically equivalent to Josh's original proposal of using an XID epoch except that it addresses all three of the "obvious issues" which he noted in his original email; plus it doesn't prevent truncating CLOG (on the assumption that we rejigger things not to consult clog when the page is marked PD_ALL_VISIBLE). > If you're willing to allow one visit and rewrite of each page, then > we can do that today with maybe a bit of rejiggering of vacuum's > when-to-freeze heuristics. Hmm, yeah. Maybe we should freeze when we set PD_ALL_VISIBLE; that might be just as good, and simpler. Assuming the visibility map is sufficiently crash-safe/non-buggy, we could then teach VACUUM that it's OK to advance relfrozenxid even when doing just a partial vacuum - because any pages that were skipped must contain only frozen tuples. Previously you've objected to proposals in this direction because they might destroy forensic information, but maybe we should do it anyway. Either way, I think if we do this it *basically* gets rid of anti-wraparound vacuum. Yeah, we'll still do routine partial vacuums, but what you won't have is... write the table, vacuum, vacuum, vacuum, vacuum, OK, everything's visible to everyone, don't need to vacuum any more... months pass... boom, unexpected full-table vacuum. The beginning part is the same, but you get rid of the boom at the end. The only way I see to cut down vacuum activity even further is to freeze them (and set the visibility map bit) before evicting them from shared_buffers. That's really the only way to get "write once and only once", but it's pretty hit or miss, because the xmin horizon might not advance fast enough to make it actually work. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |