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: Jesper Krogh on 27 May 2010 01:56 On 27/05/2010, at 02.48, Robert Haas <robertmhaas(a)gmail.com> wrote: > 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. Just a thought. Wouldn't a All-visible bit also enable index only scans to some degree? Jesper -- 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: Heikki Linnakangas on 27 May 2010 03:17 On 27/05/10 08:56, Jesper Krogh wrote: > Just a thought. Wouldn't a All-visible bit also enable index only scans > to some degree? Yes. In fact, that's one reason I implemented the visibility map in the first place. I started working on index-only scans based on that last year, if you search the archives for index-only scans you'll find those discussions. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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: Robert Haas on 27 May 2010 15:39 On Thu, May 27, 2010 at 3:15 PM, Kevin Grittner <Kevin.Grittner(a)wicourts.gov> wrote: > Jesper Krogh <jesper(a)krogh.cc> wrote: > >> Couldn't pages that are totally filled by the same transaction, be > >> frozen on the initial write? > > As far as I'm aware, that can only be done if: > > (a) The tuples were written within the same transaction which > created or truncated the table. > > *or* > > (b) The writing transaction and all transactions concurrent to it > have completed by the time the page is about to be written. Actually, I think this is true only in case (b). In case (a), you mess up visibility with respect to other command-IDs within the transaction. -- 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: "Kevin Grittner" on 27 May 2010 15:52 Robert Haas <robertmhaas(a)gmail.com> wrote: > On Thu, May 27, 2010 at 3:15 PM, Kevin Grittner >> (a) The tuples were written within the same transaction which >> created or truncated the table. > In case (a), you mess up visibility with respect to other > command-IDs within the transaction. Surely that problem is surmountable? -Kevin -- 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 27 May 2010 15:56
On Thu, May 27, 2010 at 3:52 PM, Kevin Grittner <Kevin.Grittner(a)wicourts.gov> wrote: > Robert Haas <robertmhaas(a)gmail.com> wrote: >> On Thu, May 27, 2010 at 3:15 PM, Kevin Grittner > >>> (a) The tuples were written within the same transaction which >>> created or truncated the table. > >> In case (a), you mess up visibility with respect to other >> command-IDs within the transaction. > > Surely that problem is surmountable? I proposed an idea at PGCon, but I believe Tom and Heikki thought it was far too grotty to consider. -- 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 |