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: "Kevin Grittner" on 27 May 2010 16:01 Robert Haas <robertmhaas(a)gmail.com> wrote: > I proposed an idea at PGCon, but I believe Tom and Heikki thought > it was far too grotty to consider. Well, as an alternative -- don't we have some information about the relation pinned which could hold the xid of its creator? If the tuple is frozen check to see if your transaction is the creator and behave like you created the tuple (which, in fact, you did)? -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: Joe Conway on 27 May 2010 16:21 On 05/27/2010 12:39 PM, Robert Haas wrote: > 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. > (a) can work if it is all in one command, CREATE TABLE AS SELECT... Additionally we were discussing COPY in the FROM clause, which means you could CREATE TABLE AS SELECT ... FROM (COPY ...). That would allow bulk loading with hint bits already set (and tuples frozen?). Joe
From: "Kevin Grittner" on 27 May 2010 16:33 >>>Joe Conway <mail(a)joeconway.com> wrote: > (a) can work if it is all in one command, CREATE TABLE AS > SELECT... > Additionally we were discussing COPY in the FROM clause, which > means you could CREATE TABLE AS SELECT ... FROM (COPY ...). That > would allow bulk loading with hint bits already set (and tuples > frozen?). As long as it's hinted and frozen after a pg_dump -1 | psql I'll be happy. -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: Josh Berkus on 27 May 2010 14:00 > 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. That is the use case, though. What I've encountered so far at 3 client sites is tables which are largely append-only, with a few selects and very few updates (< 2%) on recent data. In general, once data gets flushed out of memory, it goes to disk and never gets recalled, and certainly not written. Thinks are hunky-dory until we reach max_freeze_age, at which point the server has to chew through hundreds of gigabytes of old data just to freeze them, sometimes bringing the application to a halt in the process. The user's perspective on this is quite reasonable: if I haven't selected these pages, and I haven't written to them, why does autovacuum need to visit them and screw up my server performance? > 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, That only works if those pages were going to be autovacuumed anyway. In the case outlined above (which I've seen at 3 different production sites this year), they wouldn't be; a table with less than 2% updates and deletes does not get vacuumed until max_freeze_age for any reason. For that matter, pages which are getting autovacuumed are not a problem, period; they're being read and written and freezing them is not an issue. I'm not seeing a way of fixing this common issue short of overhauling CLOG, or of creating a freeze_map. Darn. -- -- 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: Josh Berkus on 27 May 2010 14:17
On 5/26/10 6:32 PM, Robert Haas wrote: > 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. It would be an improvement, and easier than the various ways of never having to visit the pages, which are all fairly intensive. Given the destruction of rollback information, though, we'd probably want a way to switch this behaviour on and off as an autovacuum setting. Does this send us down the wrong path, though? I thought we wanted to think about removing hint bits so that we could implement things like CRCs. No? -- -- 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 |