Re: [HACKERS] Do we need so many hint bits?

2012-11-27 Thread Robert Haas
On Mon, Nov 19, 2012 at 2:46 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jeff Davis pg...@j-davis.com writes: As I said elsewhere in the thread, I'm not planning to introduce any additional locking. There is already precedent in IndexOnlyNext. Of course, that just begs the question of whether the

Re: [HACKERS] Do we need so many hint bits?

2012-11-21 Thread Jeff Davis
On Mon, 2012-11-19 at 14:46 -0500, Tom Lane wrote: Jeff Davis pg...@j-davis.com writes: As I said elsewhere in the thread, I'm not planning to introduce any additional locking. There is already precedent in IndexOnlyNext. Of course, that just begs the question of whether the code in

Re: [HACKERS] Do we need so many hint bits?

2012-11-20 Thread Merlin Moncure
On Thu, Nov 15, 2012 at 10:37 PM, Simon Riggs si...@2ndquadrant.com wrote: On 15 November 2012 22:21, Tom Lane t...@sss.pgh.pa.us wrote: Removing those 3 hints would give us 3 more flag bits (eventually, after we are sure they aren't just leftover), and it would also reduce the chance that a

Re: [HACKERS] Do we need so many hint bits?

2012-11-20 Thread Tom Lane
Merlin Moncure mmonc...@gmail.com writes: Hm, I wonder if you could squeeze two bits out. ISTM here are the interesting cases enumerated: 0: xmin unknown 1: xmin invalid 2: xmin valid, xmax unknown 3: xmin valid, xmax invalid 4: xmin valid, xmax valid Did I miss any? Yes. xmin unknown,

Re: [HACKERS] Do we need so many hint bits?

2012-11-19 Thread Atri Sharma
On Sun, Nov 18, 2012 at 10:43 PM, Jeff Davis pg...@j-davis.com wrote: On Sun, 2012-11-18 at 15:19 +0100, Andres Freund wrote: On Sunday, November 18, 2012 03:07:01 AM Jeff Davis wrote: Process A (process that clears a VM bit for a data page): 1. Acquires exclusive lock on data buffer

Re: [HACKERS] Do we need so many hint bits?

2012-11-19 Thread Atri Sharma
It's quite common to load a lot of data, and then do some reads for a while (setting hint bits and flushing them to disk), and then do a VACUUM a while later, setting PD_ALL_VISIBLE and writing all of the pages again. Also, if I remember correctly, Robert went to significant effort when

Re: [HACKERS] Do we need so many hint bits?

2012-11-19 Thread Atri Sharma
On Tue, Nov 20, 2012 at 12:08 AM, Jeff Davis pg...@j-davis.com wrote: On Mon, 2012-11-19 at 23:50 +0530, Atri Sharma wrote: Sorry If I am being a bit naive, but shouldnt a simple mutex work in the case when a process wants to change the VM bit in cache? Mutex would be cheaper than

Re: [HACKERS] Do we need so many hint bits?

2012-11-19 Thread Jeff Davis
On Mon, 2012-11-19 at 23:50 +0530, Atri Sharma wrote: Sorry If I am being a bit naive, but shouldnt a simple mutex work in the case when a process wants to change the VM bit in cache? Mutex would be cheaper than locks. I thought mutexes are locks? The point is to avoid taking new locks

Re: [HACKERS] Do we need so many hint bits?

2012-11-19 Thread Jeff Davis
On Tue, 2012-11-20 at 00:13 +0530, Atri Sharma wrote: My mistake...I thought we were more concerned about the cost of locking. I agree, locking many data pages simultaneously could be hazardous. This requires more thought.Maybe removing PD_ALL_VISIBLE isnt such a great idea after all...

Re: [HACKERS] Do we need so many hint bits?

2012-11-19 Thread Andres Freund
On 2012-11-19 10:46:37 -0800, Jeff Davis wrote: On Tue, 2012-11-20 at 00:13 +0530, Atri Sharma wrote: My mistake...I thought we were more concerned about the cost of locking. I agree, locking many data pages simultaneously could be hazardous. This requires more thought.Maybe

Re: [HACKERS] Do we need so many hint bits?

2012-11-19 Thread Tom Lane
Jeff Davis pg...@j-davis.com writes: As I said elsewhere in the thread, I'm not planning to introduce any additional locking. There is already precedent in IndexOnlyNext. Of course, that just begs the question of whether the code in IndexOnlyNext is correct. And after looking at it, it seems

Re: [HACKERS] Do we need so many hint bits?

2012-11-18 Thread Simon Riggs
On 17 November 2012 21:20, Jeff Davis pg...@j-davis.com wrote: ISTM that we should tune that specifically by performing a VM lookup for next 32 pages (or more), so we reduce the lookups well below 1 per page. That way the overhead of using the VM will be similar to using the PD_ALL_VISIBLE.

Re: [HACKERS] Do we need so many hint bits?

2012-11-18 Thread Andres Freund
On Sunday, November 18, 2012 03:07:01 AM Jeff Davis wrote: Process A (process that clears a VM bit for a data page): 1. Acquires exclusive lock on data buffer 2. Acquires exclusive lock on VM buffer 3. clears VM bit 4. Releases VM buffer lock 5. Releases data buffer lock Well, but

Re: [HACKERS] Do we need so many hint bits?

2012-11-18 Thread Simon Riggs
On 18 November 2012 08:52, Simon Riggs si...@2ndquadrant.com wrote: The difference here is that we still need to check visibility of each tuple, but that can be a very cheap check and never involves clog, nor does it dirty the page. Tuple access is reasonably expensive in comparison with a

Re: [HACKERS] Do we need so many hint bits?

2012-11-18 Thread Jeff Davis
On Sun, 2012-11-18 at 15:19 +0100, Andres Freund wrote: On Sunday, November 18, 2012 03:07:01 AM Jeff Davis wrote: Process A (process that clears a VM bit for a data page): 1. Acquires exclusive lock on data buffer 2. Acquires exclusive lock on VM buffer 3. clears VM bit 4.

Re: [HACKERS] Do we need so many hint bits?

2012-11-17 Thread Andres Freund
On 2012-11-16 17:19:23 -0800, Jeff Davis wrote: On Fri, 2012-11-16 at 16:09 +0100, Andres Freund wrote: As far as I understand the code the crash-safety aspects of the visibilitymap currently rely on on having the knowledge that ALL_VISIBLE has been cleared during a

Re: [HACKERS] Do we need so many hint bits?

2012-11-17 Thread Jeff Davis
On Sat, 2012-11-17 at 14:24 +0100, Andres Freund wrote: I think the point is that to check whether the visibilitymap bit needs to be unset What's the problem with that? If you already have the VM buffer pinned (which should be possible if we keep the VM buffer in a longer-lived structure), then

Re: [HACKERS] Do we need so many hint bits?

2012-11-17 Thread Tom Lane
Jeff Davis pg...@j-davis.com writes: What's the problem with that? If you already have the VM buffer pinned (which should be possible if we keep the VM buffer in a longer-lived structure), then doing the test is almost as cheap as checking PD_ALL_VISIBLE, because you don't need any locks.

Re: [HACKERS] Do we need so many hint bits?

2012-11-17 Thread Simon Riggs
On 16 November 2012 19:58, Jeff Davis pg...@j-davis.com wrote: On Fri, 2012-11-16 at 11:58 -0500, Robert Haas wrote: Also, I am wondering about PD_ALL_VISIBLE. It was originally introduced in the visibility map patch, apparently as a way to know when to clear the VM bit when doing an

Re: [HACKERS] Do we need so many hint bits?

2012-11-17 Thread Jeff Davis
On Sat, 2012-11-17 at 16:53 -0500, Tom Lane wrote: Jeff Davis pg...@j-davis.com writes: What's the problem with that? If you already have the VM buffer pinned (which should be possible if we keep the VM buffer in a longer-lived structure), then doing the test is almost as cheap as checking

Re: [HACKERS] Do we need so many hint bits?

2012-11-17 Thread Jeff Davis
On Sat, 2012-11-17 at 19:35 -0500, Simon Riggs wrote: The biggest problem with hint bits is SeqScans on a table that ends up dirtying many pages. Repeated checks against clog and hint bit setting are massive overheads for the user that hits that, plus it generates an unexpected surge of

Re: [HACKERS] Do we need so many hint bits?

2012-11-17 Thread Jeff Janes
On Fri, Nov 16, 2012 at 5:35 PM, Jeff Davis pg...@j-davis.com wrote: On Fri, 2012-11-16 at 17:04 -0800, Jeff Janes wrote: Your question prompts me to post something I had been wondering. Might it be worthwhile to break the PD_ALL_VISIBLE / vm equivalence? Should the vm bit get cleared by a

Re: [HACKERS] Do we need so many hint bits?

2012-11-16 Thread Simon Riggs
On 15 November 2012 23:12, Simon Riggs si...@2ndquadrant.com wrote: We need detailed stats that allow many people to make their own tests and to report on what they find. To work out how to proceed we need * wait event times on clog accesses at table-level * numbers of blocks dirtied by hint

Re: [HACKERS] Do we need so many hint bits?

2012-11-16 Thread Merlin Moncure
On Thu, Nov 15, 2012 at 9:21 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jeff Davis pg...@j-davis.com writes: It occurred to me recently that many of the hint bits aren't terribly important (at least it's not obvious to me). HEAP_XMIN_COMMITTED clearly has a purpose, and we'd expect it to be used

Re: [HACKERS] Do we need so many hint bits?

2012-11-16 Thread Andres Freund
On 2012-11-16 08:43:12 -0600, Merlin Moncure wrote: On Thu, Nov 15, 2012 at 9:21 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jeff Davis pg...@j-davis.com writes: It occurred to me recently that many of the hint bits aren't terribly important (at least it's not obvious to me). HEAP_XMIN_COMMITTED

Re: [HACKERS] Do we need so many hint bits?

2012-11-16 Thread Merlin Moncure
On Fri, Nov 16, 2012 at 8:58 AM, Andres Freund and...@2ndquadrant.com wrote: On 2012-11-16 08:43:12 -0600, Merlin Moncure wrote: On Thu, Nov 15, 2012 at 9:21 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jeff Davis pg...@j-davis.com writes: It occurred to me recently that many of the hint bits

Re: [HACKERS] Do we need so many hint bits?

2012-11-16 Thread Merlin Moncure
On Thu, Nov 15, 2012 at 10:14 PM, Pavan Deolasee pavan.deola...@gmail.com wrote: Another approach could be to set those additional bits, but don't dirty the page. So if the page is already dirty or gets dirty later on before its eviction, those hint bits will get recorded. We can also try some

Re: [HACKERS] Do we need so many hint bits?

2012-11-16 Thread Andres Freund
On 2012-11-15 16:42:57 -0800, Jeff Davis wrote: Related to discussion here: http://archives.postgresql.org/message-id/cahyxu0zn5emepledozugraqif92f-yjvfr-p5vuh6n0wpkz...@mail.gmail.com It occurred to me recently that many of the hint bits aren't terribly important (at least it's not obvious

Re: [HACKERS] Do we need so many hint bits?

2012-11-16 Thread Robert Haas
On Thu, Nov 15, 2012 at 7:42 PM, Jeff Davis pg...@j-davis.com wrote: But the other tuple hint bits seem to be there just for symmetry, because they shouldn't last long. If HEAP_XMIN_INVALID or HEAP_XMAX_COMMITTED is set, then it's (hopefully) going to be vacuumed soon, and gone completely. And

Re: [HACKERS] Do we need so many hint bits?

2012-11-16 Thread Jeff Davis
On Thu, 2012-11-15 at 22:21 -0500, Tom Lane wrote: We aren't pressed for flag bits particularly. I think the main attraction of this idea is precisely to reduce unnecessary page dirties, and so that leads me to suggest a variant: keep the four bits defined as now, but do not attempt to set

Re: [HACKERS] Do we need so many hint bits?

2012-11-16 Thread Jeff Davis
On Fri, 2012-11-16 at 11:58 -0500, Robert Haas wrote: Also, I am wondering about PD_ALL_VISIBLE. It was originally introduced in the visibility map patch, apparently as a way to know when to clear the VM bit when doing an update. It was then also used for scans, which showed a significant

Re: [HACKERS] Do we need so many hint bits?

2012-11-16 Thread Jeff Janes
On Thu, Nov 15, 2012 at 4:42 PM, Jeff Davis pg...@j-davis.com wrote: Also, I am wondering about PD_ALL_VISIBLE. It was originally introduced in the visibility map patch, apparently as a way to know when to clear the VM bit when doing an update. It was then also used for scans, which showed a

Re: [HACKERS] Do we need so many hint bits?

2012-11-16 Thread Jeff Davis
On Fri, 2012-11-16 at 16:09 +0100, Andres Freund wrote: As far as I understand the code the crash-safety aspects of the visibilitymap currently rely on on having the knowledge that ALL_VISIBLE has been cleared during a heap_(insert|update|delete). That allows management of the visibilitymap

Re: [HACKERS] Do we need so many hint bits?

2012-11-16 Thread Jeff Davis
On Fri, 2012-11-16 at 17:04 -0800, Jeff Janes wrote: On Thu, Nov 15, 2012 at 4:42 PM, Jeff Davis pg...@j-davis.com wrote: Also, I am wondering about PD_ALL_VISIBLE. It was originally introduced in the visibility map patch, apparently as a way to know when to clear the VM bit when doing an

[HACKERS] Do we need so many hint bits?

2012-11-15 Thread Jeff Davis
Related to discussion here: http://archives.postgresql.org/message-id/cahyxu0zn5emepledozugraqif92f-yjvfr-p5vuh6n0wpkz...@mail.gmail.com It occurred to me recently that many of the hint bits aren't terribly important (at least it's not obvious to me). HEAP_XMIN_COMMITTED clearly has a purpose,

Re: [HACKERS] Do we need so many hint bits?

2012-11-15 Thread Simon Riggs
On 15 November 2012 19:42, Jeff Davis pg...@j-davis.com wrote: many of the hint bits aren't terribly important The truth is that nobody knows because there is no way of knowing. A good way forwards would be to collect detailed stats by table. We can then run various loads and see if any of

Re: [HACKERS] Do we need so many hint bits?

2012-11-15 Thread Tom Lane
Simon Riggs si...@2ndquadrant.com writes: On 15 November 2012 19:42, Jeff Davis pg...@j-davis.com wrote: many of the hint bits aren't terribly important The truth is that nobody knows because there is no way of knowing. We had a discussion awhile back in which the idea of *no* hint bits was

Re: [HACKERS] Do we need so many hint bits?

2012-11-15 Thread Tom Lane
Jeff Davis pg...@j-davis.com writes: It occurred to me recently that many of the hint bits aren't terribly important (at least it's not obvious to me). HEAP_XMIN_COMMITTED clearly has a purpose, and we'd expect it to be used many times following the initial CLOG lookup. Right. But the other

Re: [HACKERS] Do we need so many hint bits?

2012-11-15 Thread Simon Riggs
On 15 November 2012 22:08, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: On 15 November 2012 19:42, Jeff Davis pg...@j-davis.com wrote: many of the hint bits aren't terribly important The truth is that nobody knows because there is no way of knowing. We had a

Re: [HACKERS] Do we need so many hint bits?

2012-11-15 Thread Pavan Deolasee
On Fri, Nov 16, 2012 at 8:51 AM, Tom Lane t...@sss.pgh.pa.us wrote: Jeff Davis pg...@j-davis.com writes: Removing those 3 hints would give us 3 more flag bits (eventually, after we are sure they aren't just leftover), and it would also reduce the chance that a page is dirtied for no other