Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-12 Thread Bruce Momjian
Greg Stark wrote: On Thu, Jun 3, 2010 at 11:41 AM, Greg Stark gsst...@mit.edu wrote: I think to make it work you need to store a whole 64-bit reference transaction id consisting of both a cycle counter and a transaction id. The invariant for the page is that every xid on the page can be

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-12 Thread Tom Lane
Bruce Momjian br...@momjian.us writes: I think we might need two bits, one commited and all visible, and another aborted and all vislble. Huh? The latter means vacuumable. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-10 Thread Jan Wieck
Seems I underestimated the importance of forensic breadcrumbs. On 6/9/2010 12:09 PM, Tom Lane wrote: I do like the idea of using a status bit rather than FrozenXid to mark a frozen tuple, because that eliminates the conflict between wanting to freeze aggressively for performance reasons and

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-10 Thread Greg Stark
On Thu, Jun 3, 2010 at 11:41 AM, Greg Stark gsst...@mit.edu wrote: I think to make it work you need to store a whole 64-bit reference transaction id consisting of both a cycle counter and a transaction id. The invariant for the page is that every xid on the page can be compared to that

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-10 Thread Tom Lane
Jan Wieck janwi...@yahoo.com writes: Depends. Specifically on transaction profiles and how long the blocks linger around before being written. If you can set the all visible bit by the time, the page is written the first time, what bit including the is-frozen one cannot be set at that time

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-09 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Tue, Jun 8, 2010 at 6:35 PM, Tom Lane t...@sss.pgh.pa.us wrote: But none of this accomplishes a damn thing towards the original goal, which was to avoid an extra disk write associated with freezing (not to mention an extra write for setting the

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-09 Thread marcin mank
On Wed, Jun 9, 2010 at 12:35 AM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: On Tue, 2010-06-08 at 18:03 -0400, Robert Haas wrote: OK, yes, I see what you're getting at now.  There are two possible ways to do freeze the tuples and keep the xmin: we can either

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-09 Thread Simon Riggs
On Tue, 2010-06-08 at 18:35 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: On Tue, 2010-06-08 at 18:03 -0400, Robert Haas wrote: OK, yes, I see what you're getting at now. There are two possible ways to do freeze the tuples and keep the xmin: we can either rely on the

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-09 Thread Tom Lane
marcin mank marcin.m...@gmail.com writes: Could a tuple wih the bit set be considered frozen already? Would we actually ever need to rewrite the xmin, even for anti-wraparound reasons? That's exactly what Simon is suggesting: if we had a tuple status flag with the semantics of this xmin is

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-09 Thread Robert Haas
On Wed, Jun 9, 2010 at 12:09 PM, Tom Lane t...@sss.pgh.pa.us wrote: If you freeze all tuples by the time the pages are marked all-visible, perhaps via the xmin-preserving mechanism Simon suggested, then you can use the visibility map to skip anti-wraparound vacuum as well as regular vacuum.  

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-08 Thread Greg Stark
On Tue, Jun 8, 2010 at 2:26 AM, Stephen Frost sfr...@snowman.net wrote: Just an off-the-wall thought, but, would it be possible to have a tool which read WAL backwards and compared entries in the WAL against entries on disk?  I realize that you'd only see one version of a particular block and

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-08 Thread Stephen Frost
* Greg Stark (gsst...@mit.edu) wrote: I assume you mean back out the changes incrementally until you find a full_page_write and see if it matches? To be honest, you're already assuming I know more about how this all works than I do. :) The gist of my thought was simply- we write out block

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-08 Thread Jan Wieck
On 6/8/2010 8:27 AM, Greg Stark wrote: On Tue, Jun 8, 2010 at 2:26 AM, Stephen Frost sfr...@snowman.net wrote: Just an off-the-wall thought, but, would it be possible to have a tool which read WAL backwards and compared entries in the WAL against entries on disk? I realize that you'd only see

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-08 Thread Simon Riggs
On Fri, 2010-06-04 at 10:18 -0400, Tom Lane wrote: Jan Wieck janwi...@yahoo.com writes: On 6/2/2010 3:10 PM, Alvaro Herrera wrote: I'd prefer a setting that would tell the system to freeze all tuples that fall within a safety range whenever any tuple in the page is frozen -- weren't you

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-08 Thread Robert Haas
On Tue, Jun 8, 2010 at 4:55 PM, Simon Riggs si...@2ndquadrant.com wrote: On Fri, 2010-06-04 at 10:18 -0400, Tom Lane wrote: Jan Wieck janwi...@yahoo.com writes: On 6/2/2010 3:10 PM, Alvaro Herrera wrote: I'd prefer a setting that would tell the system to freeze all tuples that fall within

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-08 Thread Simon Riggs
On Tue, 2010-06-08 at 16:58 -0400, Robert Haas wrote: On Tue, Jun 8, 2010 at 4:55 PM, Simon Riggs si...@2ndquadrant.com wrote: On Fri, 2010-06-04 at 10:18 -0400, Tom Lane wrote: Jan Wieck janwi...@yahoo.com writes: On 6/2/2010 3:10 PM, Alvaro Herrera wrote: I'd prefer a setting that

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-08 Thread Robert Haas
On Tue, Jun 8, 2010 at 5:08 PM, Simon Riggs si...@2ndquadrant.com wrote: Why not just have a separate flag for HEAP_XMIN_FROZEN, that way we can keep the xmin but also can see it is frozen? We could do that, but I think the point of this exercise is to reduce I/O - specifically, I/O caused

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-08 Thread Simon Riggs
On Tue, 2010-06-08 at 18:03 -0400, Robert Haas wrote: OK, yes, I see what you're getting at now. There are two possible ways to do freeze the tuples and keep the xmin: we can either rely on the PD_ALL_VISIBLE page-level bit (as I previously proposed) or we can additionally have a

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-08 Thread Tom Lane
Simon Riggs si...@2ndquadrant.com writes: On Tue, 2010-06-08 at 18:03 -0400, Robert Haas wrote: OK, yes, I see what you're getting at now. There are two possible ways to do freeze the tuples and keep the xmin: we can either rely on the PD_ALL_VISIBLE page-level bit (as I previously proposed)

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-08 Thread Alvaro Herrera
Excerpts from Tom Lane's message of mar jun 08 18:35:00 -0400 2010: But none of this accomplishes a damn thing towards the original goal, which was to avoid an extra disk write associated with freezing (not to mention an extra write for setting the transaction-committed hint bit). Setting a

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-08 Thread Robert Haas
On Tue, Jun 8, 2010 at 6:35 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: On Tue, 2010-06-08 at 18:03 -0400, Robert Haas wrote: OK, yes, I see what you're getting at now.  There are two possible ways to do freeze the tuples and keep the xmin: we can either

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-07 Thread Tom Lane
Kevin Grittner kevin.gritt...@wicourts.gov writes: ... my perspective is that it would be A Good Thing if it could just be turned on when needed. If you have recurring bug that can be arranged, but in those cases you have other options; so I'm assuming you want this kept because it is

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-07 Thread Kevin Grittner
Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: The best thought I've had so far is that if someone kept WAL files long enough the evidence might be in there somewhere Hm, that is an excellent point. The WAL trace would actually be a lot superior

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-07 Thread Kevin Grittner
Tom Lane t...@sss.pgh.pa.us wrote: If we were actually going in this direction we'd want to write a much better WAL-text-dump tool than we have, and then in principle somebody could sanitize the text output before shipping it off. I wouldn't think this would be practical unless there was a

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-07 Thread Tom Lane
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: Hm, that is an excellent point. The WAL trace would actually be a lot superior in terms of being able to figure out what went wrong. But I don't quite see how we tell people either keep xmin or keep your

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-07 Thread Greg Smith
Tom Lane wrote: If we were actually going in this direction we'd want to write a much better WAL-text-dump tool than we have, and then in principle somebody could sanitize the text output before shipping it off. But going through a large volume of data that way could be pretty impractical.

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-07 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: If we were actually going in this direction we'd want to write a much better WAL-text-dump tool than we have, and then in principle somebody could sanitize the text output before shipping it off. But going through a large volume of data that way could be

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-04 Thread Jan Wieck
On 6/2/2010 2:16 PM, Robert Haas wrote: On Wed, Jun 2, 2010 at 2:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@commandprompt.com writes: The problem is that vacuum doesn't know that a certain part of the table is already frozen. It needs to scan it completely anyways. If

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-04 Thread Jan Wieck
On 6/2/2010 3:10 PM, Alvaro Herrera wrote: Excerpts from Robert Haas's message of mié jun 02 14:16:33 -0400 2010: We could, but I think we'd be better off just freezing at the time we mark the page PD_ALL_VISIBLE and then using the visibility map for both purposes. Keeping around the old xmin

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-04 Thread Tom Lane
Jan Wieck janwi...@yahoo.com writes: On 6/2/2010 3:10 PM, Alvaro Herrera wrote: I'd prefer a setting that would tell the system to freeze all tuples that fall within a safety range whenever any tuple in the page is frozen -- weren't you working on a patch to do this? (was it Jeff Davis?) I

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-04 Thread Robert Haas
On Fri, Jun 4, 2010 at 10:18 AM, Tom Lane t...@sss.pgh.pa.us wrote: Jan Wieck janwi...@yahoo.com writes: On 6/2/2010 3:10 PM, Alvaro Herrera wrote: I'd prefer a setting that would tell the system to freeze all tuples that fall within a safety range whenever any tuple in the page is frozen --

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-04 Thread Kevin Grittner
Tom Lane t...@sss.pgh.pa.us wrote: Jan Wieck janwi...@yahoo.com writes: I just see a lot of cost caused by this safety range. I yet have to see its real value, other than feel good. Jan, you don't know what you're talking about. I have repeatedly had cases where being able to look at

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-04 Thread Tom Lane
Kevin Grittner kevin.gritt...@wicourts.gov writes: In my experience with my own environment, I can honestly say that it's clear that not freezing tuples quickly adds more cost than running with cassert on. If we had to run in production with one or the other, I would definitely choose cassert

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-04 Thread Robert Haas
On Fri, Jun 4, 2010 at 11:45 AM, Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: In my experience with my own environment, I can honestly say that it's clear that not freezing tuples quickly adds more cost than running with cassert on.  If we had to run in

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-04 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Fri, Jun 4, 2010 at 11:45 AM, Tom Lane t...@sss.pgh.pa.us wrote: The reason for not recommending cassert in production builds is not cost but stability. We routinely castigate people for benchmarking done with cassert turned on, and tell them

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-04 Thread Kevin Grittner
Tom Lane t...@sss.pgh.pa.us wrote: But Kevin's question seemed to be based on the assumption that runtime cost was the only negative. It wouldn't be terribly hard to make a variant of cassert that skips two or three of the most expensive things (particularly memory context checking and

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-04 Thread Bruce Momjian
Kevin Grittner wrote: Fair enough. I was thinking of them both as debugging features, which had various ideas roiling around in my head. Having run hundreds of databases 24/7 for years without ever needing this information, but paying the cost for it one way or another every day, my

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-04 Thread Tom Lane
Bruce Momjian br...@momjian.us writes: The idea that thousands of Postgres installations are slower just so we can occasionally debug xmin/xmax issues seems way off balance to me. There's no evidence whatsoever that the scope of the problem is that large. If people want debugging, let them

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-04 Thread Kevin Grittner
Tom Lane t...@sss.pgh.pa.us wrote: Bruce Momjian br...@momjian.us writes: The idea that thousands of Postgres installations are slower just so we can occasionally debug xmin/xmax issues seems way off balance to me. There's no evidence whatsoever that the scope of the problem is that large.

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-04 Thread Bruce Momjian
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: The idea that thousands of Postgres installations are slower just so we can occasionally debug xmin/xmax issues seems way off balance to me. There's no evidence whatsoever that the scope of the problem is that large. If people want

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-04 Thread Tom Lane
Bruce Momjian br...@momjian.us writes: With in-place VACUUM FULL gone in 9.0, will there be as much need for xmin/xmax forensics? You know perfectly well that no one could answer that question. (Or at least not answer it on the basis of facts available today.) regards,

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-04 Thread Bruce Momjian
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: With in-place VACUUM FULL gone in 9.0, will there be as much need for xmin/xmax forensics? You know perfectly well that no one could answer that question. (Or at least not answer it on the basis of facts available today.) Well, guess

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-04 Thread Tom Lane
Bruce Momjian br...@momjian.us writes: Tom Lane wrote: Bruce Momjian br...@momjian.us writes: With in-place VACUUM FULL gone in 9.0, will there be as much need for xmin/xmax forensics? You know perfectly well that no one could answer that question. (Or at least not answer it on the basis

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-04 Thread Alvaro Herrera
Excerpts from Bruce Momjian's message of vie jun 04 15:39:07 -0400 2010: Tom Lane wrote: Bruce Momjian br...@momjian.us writes: With in-place VACUUM FULL gone in 9.0, will there be as much need for xmin/xmax forensics? You know perfectly well that no one could answer that question.

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-04 Thread Bruce Momjian
Alvaro Herrera wrote: Excerpts from Bruce Momjian's message of vie jun 04 15:39:07 -0400 2010: Tom Lane wrote: Bruce Momjian br...@momjian.us writes: With in-place VACUUM FULL gone in 9.0, will there be as much need for xmin/xmax forensics? You know perfectly well that no one

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-03 Thread Greg Stark
So I think the scheme in the original post of this thread is workable. Not as described but could be made to work. In which case I think it's preferable to a freeze map -- which I had previously assumed we would need eventually. The problem with the scheme originally described is that it assumed

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-02 Thread Russell Smith
On 28/05/10 04:00, Josh Berkus wrote: 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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-02 Thread Alvaro Herrera
Excerpts from Russell Smith's message of mié jun 02 06:38:35 -0400 2010: Don't you not get a positive enough effect by adjusting the table's autovacuum_min_freeze_age and autovacuum_max_freeze_age. If you set those numbers small, it appears to me that you would get very quickly to a state

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-02 Thread Tom Lane
Alvaro Herrera alvhe...@commandprompt.com writes: The problem is that vacuum doesn't know that a certain part of the table is already frozen. It needs to scan it completely anyways. If we had a frozen map, we could mark pages that are completely frozen and thus do not need any vacuuming; but

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-02 Thread Robert Haas
On Wed, Jun 2, 2010 at 2:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@commandprompt.com writes: The problem is that vacuum doesn't know that a certain part of the table is already frozen.  It needs to scan it completely anyways.  If we had a frozen map, we could mark pages

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-02 Thread Alvaro Herrera
Excerpts from Robert Haas's message of mié jun 02 14:16:33 -0400 2010: We could, but I think we'd be better off just freezing at the time we mark the page PD_ALL_VISIBLE and then using the visibility map for both purposes. Keeping around the old xmin values after every tuple on the page is

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-02 Thread Bruce Momjian
Alvaro Herrera wrote: Excerpts from Robert Haas's message of mi?? jun 02 14:16:33 -0400 2010: We could, but I think we'd be better off just freezing at the time we mark the page PD_ALL_VISIBLE and then using the visibility map for both purposes. Keeping around the old xmin values after

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-06-02 Thread Robert Haas
On Wed, Jun 2, 2010 at 3:10 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Robert Haas's message of mié jun 02 14:16:33 -0400 2010: We could, but I think we'd be better off just freezing at the time we mark the page PD_ALL_VISIBLE and then using the visibility map for both

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-28 Thread Heikki Linnakangas
On 27/05/10 22:56, Robert Haas wrote: On Thu, May 27, 2010 at 3:52 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Robert Haasrobertmh...@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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-27 Thread Heikki Linnakangas
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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-27 Thread Josh Berkus
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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-27 Thread Josh Berkus
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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-27 Thread Robert Haas
On Thu, May 27, 2010 at 2:17 PM, Josh Berkus j...@agliodbs.com wrote: 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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-27 Thread Robert Haas
On Thu, May 27, 2010 at 2:00 PM, Josh Berkus j...@agliodbs.com wrote: 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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-27 Thread Jesper Krogh
On 27/05/2010, at 20.00, Josh Berkus j...@agliodbs.com wrote: 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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-27 Thread Kevin Grittner
Jesper Krogh jes...@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)

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-27 Thread Robert Haas
On Thu, May 27, 2010 at 3:15 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Jesper Krogh jes...@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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-27 Thread Kevin Grittner
Robert Haas robertmh...@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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-27 Thread Robert Haas
On Thu, May 27, 2010 at 3:52 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Robert Haas robertmh...@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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-27 Thread Kevin Grittner
Robert Haas robertmh...@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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-27 Thread Joe Conway
On 05/27/2010 12:39 PM, Robert Haas wrote: On Thu, May 27, 2010 at 3:15 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Jesper Krogh jes...@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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-27 Thread Kevin Grittner
Joe Conway m...@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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-26 Thread Josh Berkus
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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-26 Thread Heikki Linnakangas
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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-26 Thread Josh Berkus
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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-26 Thread Robert Haas
On Wed, May 26, 2010 at 2:44 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: 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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-26 Thread Josh Berkus
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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-26 Thread Tom Lane
Josh Berkus j...@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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-26 Thread Robert Haas
On Wed, May 26, 2010 at 8:06 PM, Tom Lane t...@sss.pgh.pa.us wrote: Josh Berkus j...@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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-26 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Wed, May 26, 2010 at 8:06 PM, Tom Lane t...@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,

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-26 Thread Robert Haas
On Wed, May 26, 2010 at 8:59 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Wed, May 26, 2010 at 8:06 PM, Tom Lane t...@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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-26 Thread Jesper Krogh
On 27/05/2010, at 02.48, Robert Haas robertmh...@gmail.com wrote: On Wed, May 26, 2010 at 8:06 PM, Tom Lane t...@sss.pgh.pa.us wrote: Josh Berkus j...@agliodbs.com writes: How does that get us out of reading and writing old pages, though? Yeah. Neither PD_ALL_VISIBLE nor the visibility

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-25 Thread Heikki Linnakangas
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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-25 Thread Alvaro Herrera
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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-25 Thread Tom Lane
Alvaro Herrera alvhe...@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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-25 Thread Josh Berkus
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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-25 Thread Jan Wieck
On 5/24/2010 9:30 AM, Heikki Linnakangas wrote: On 22/05/10 16:35, Tom Lane wrote: Josh Berkusj...@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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-25 Thread Josh Berkus
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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-25 Thread Heikki Linnakangas
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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-24 Thread Heikki Linnakangas
On 22/05/10 16:35, Tom Lane wrote: Josh Berkusj...@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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-24 Thread Tom Lane
Heikki Linnakangas heikki.linnakan...@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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-24 Thread Josh Berkus
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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-24 Thread Alvaro Herrera
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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-22 Thread Tom Lane
Josh Berkus j...@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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-22 Thread Josh Berkus
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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-22 Thread Tom Lane
Josh Berkus j...@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

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-22 Thread Jan Wieck
On 5/22/2010 9:16 PM, Tom Lane wrote: Josh Berkus j...@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

[HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-21 Thread Josh Berkus
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.

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

2010-05-21 Thread Florian Pflug
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