Re: [HACKERS] Frequent Update Project: Design Overview of HOTUpdates

On Friday 10 November 2006 08:53, Simon Riggs wrote: > On Fri, 2006-11-10 at 12:32 +0100, Zeugswetter Andreas ADI SD wrote: > > 4. although at first it might seem so I see no advantage for vacuum with > > overflow > > No need to VACUUM the indexes, which is the most expensive part. The > more index

Re: [HACKERS] Frequent Update Project: Design Overview of HOTUpdates

On Fri, 2006-11-10 at 16:46 +0100, Zeugswetter Andreas ADI SD wrote: > > I'm not sure this really solves that problem because there > > are still DELETEs to consider but it does remove one factor > > that exacerbates it unnecessarily. > > Yea, so you still need to vaccum the large table regular

Re: [HACKERS] Frequent Update Project: Design Overview of HOTUpdates

Hi, On 11/10/06, Zeugswetter Andreas ADI SD <[EMAIL PROTECTED]> wrote: > > True, but Nikhil has run tests that clearly show HOT outperforming> > current situation in the case of long running transactions. The need > > to optimise HeapTupleSatisfiesVacuum() and avoid long chains does> > still remain

Re: [HACKERS] Frequent Update Project: Design Overview of HOT Updates

Hi, On 11/10/06, Pavan Deolasee <[EMAIL PROTECTED]> wrote: On 11/10/06, Tom Lane <[EMAIL PROTECTED]> wrote: "Pavan Deolasee" < [EMAIL PROTECTED]> writes:> On 11/10/06, Tom Lane <[EMAIL PROTECTED]> wrote:> (2) Isn't this full of race conditions? > I agree, there  could be race  conditions. But

Re: [HACKERS] Frequent Update Project: Design Overview of

Ühel kenal päeval, R, 2006-11-10 kell 12:19, kirjutas Simon Riggs: > On Thu, 2006-11-09 at 18:28 -0500, Tom Lane wrote: > > > HOT can only work in cases where a tuple does not modify one of the > > > columns defined in an index on the table, and when we do not alter the > > > row length of the tup

Re: [HACKERS] Frequent Update Project: Design Overview of HOTUpdates

> > True, but Nikhil has run tests that clearly show HOT outperforming > > current situation in the case of long running transactions. The need > > to optimise HeapTupleSatisfiesVacuum() and avoid long chains does > > still remain a difficulty for both HOT and the current situation. > > > Yes

Re: [HACKERS] Frequent Update Project: Design Overview of HOTUpdates

> > 2. locking should be easier if only the original heap page is involved. > > Yes, but multi-page update already happens now, so HOT is not > different on that point. I was thinking about the case when you "pull back" a tuple, which seems to be more difficult than what we have now. Andreas

Re: [HACKERS] Frequent Update Project: Design Overview of HOT Updates

> > 1. It doubles the IO (original page + hot page), if the new row would > > have fit into the original page. > > That's an awfully big IF there. Even if you use a fillfactor > of 50% in which case you're paying a 100% performance penalty I don't see where the 50% come from ? That's only

Re: [HACKERS] Frequent Update Project: Design Overview of HOT Updates

On 11/10/06, Tom Lane <[EMAIL PROTECTED]> wrote: "Pavan Deolasee" <[EMAIL PROTECTED]> writes:> On 11/10/06, Tom Lane <[EMAIL PROTECTED]> wrote:> (2) Isn't this full of race conditions? > I agree, there  could be race  conditions. But IMO we can handle those.Doubtless you can prevent races by introd

Re: [HACKERS] Frequent Update Project: Design Overview of HOTUpdates

Hi, > True, but Nikhil has run tests that clearly show HOT outperforming > current situation in the case of long running transactions. The need to > optimise HeapTupleSatisfiesVacuum() and avoid long chains does still > remain a difficulty for both HOT and the current situation. > Yes, I carried

Re: [HACKERS] Frequent Update Project: Design Overview of HOT Updates

> > I think the vision is that the overflow table would never be very > > large because it can be vacuumed very aggressively. It has only tuples > > that are busy and will need vacuuming as soon as a transaction ends. > > Unlike the main table which is mostly tuples that don't need > > vacuumi

Re: [HACKERS] Frequent Update Project: Design Overview of HOTUpdates

On 11/10/06, Simon Riggs <[EMAIL PROTECTED]> wrote: On Fri, 2006-11-10 at 12:32 +0100, Zeugswetter Andreas ADI SD wrote:> e.g. a different header seems no easier in overflow than in heap True. The idea there is that we can turn frequent update on/off fairlyeasily for normal tables since there are n

Re: [HACKERS] Frequent Update Project: Design Overview of HOTUpdates

On Fri, 2006-11-10 at 12:32 +0100, Zeugswetter Andreas ADI SD wrote: > > > As more UPDATEs take place these tuple chains would grow, making > > > locating the latest tuple take progressively longer. > > > More generally, do we need an overflow table at all, rather > > than having these overflow

Re: [HACKERS] Frequent Update Project: Design Overview of HOT Updates

Hi,I think the vision is that the overflow table would never be very large because it can be vacuumed very aggressively. It has only tuples that are busyand will need vacuuming as soon as a transaction ends. Unlike the main tablewhich is mostly tuples that don't need vacuuming. Thats right. vacuum

Re: [HACKERS] Frequent Update Project: Design Overview of HOTUpdates

Hi,   > > This allows the length of a typical tuple chain to be extremely short in> > practice. For a single connection issuing a stream of UPDATEs the chain> > length will no more than 1 at any time. >> Only if there are no other transactions being held open, which makes> this claim a lot weaker.T

Re: [HACKERS] Frequent Update Project: Design Overview of HOTUpdates

"Simon Riggs" <[EMAIL PROTECTED]> writes: >> Seems like "altering the row length" isn't the issue, it's just "is >> there room on the page for the new version". Again, a generous >> fillfactor would give you more flexibility. > > The copy-back operation can only work if the tuple fits in the same

Re: [HACKERS] Frequent Update Project: Design Overview of HOT Updates

"Zeugswetter Andreas ADI SD" <[EMAIL PROTECTED]> writes: > 1. It doubles the IO (original page + hot page), if the new row would > have fit into the original page. That's an awfully big IF there. Even if you use a fillfactor of 50% in which case you're paying a 100% performance penalty *al

Re: [HACKERS] Frequent Update Project: Design Overview of HOTUpdates

Oops, pressed send too early. Ignore the one-line reply I just sent... Simon Riggs wrote: On Thu, 2006-11-09 at 18:28 -0500, Tom Lane wrote: "Simon Riggs" <[EMAIL PROTECTED]> writes: HOT can only work in cases where a tuple does not modify one of the columns defined in an index on the table, a

Re: [HACKERS] Frequent Update Project: Design Overview of HOTUpdates

Simon Riggs wrote: On Thu, 2006-11-09 at 18:28 -0500, Tom Lane wrote: "Simon Riggs" <[EMAIL PROTECTED]> writes: HOT can only work in cases where a tuple does not modify one of the columns defined in an index on the table, and when we do not alter the row length of the tuple. Seems like "alteri

Re: [HACKERS] Frequent Update Project: Design Overview of HOTUpdates

On Thu, 2006-11-09 at 18:28 -0500, Tom Lane wrote: > "Simon Riggs" <[EMAIL PROTECTED]> writes: > > As more UPDATEs take place these tuple chains would grow, making > > locating the latest tuple take progressively longer. > > This is the part that bothers me --- particularly the random-access > nat

Re: [HACKERS] Frequent Update Project: Design Overview of HOT Updates

On 11/10/06, Heikki Linnakangas <[EMAIL PROTECTED]> wrote: Tom Lane wrote:> (Actually, the assumption that you can throw an additional back-pointer> into overflow tuple headers is the worst feature of this proposal in> that regard --- it's really not that easy to support multiple header > formats.)

Re: [HACKERS] Frequent Update Project: Design Overview of HOT Updates

> > As more UPDATEs take place these tuple chains would grow, making > > locating the latest tuple take progressively longer. > More generally, do we need an overflow table at all, rather > than having these overflow tuples living in the same file as > the root tuples? As long as there's a b

Re: [HACKERS] Frequent Update Project: Design Overview of HOT Updates

Tom Lane wrote: "Pavan Deolasee" <[EMAIL PROTECTED]> writes: Yes. The last bit in the t_infomask is used up to mark presence of overflow tuple header. But I believe there are few more bits that can be reused. There are three bits available in the t_ctid field as well (since ip_posid needs maximu

Re: [HACKERS] Frequent Update Project: Design Overview of HOT Updates

On 11/10/06, Tom Lane <[EMAIL PROTECTED] > wrote: "Pavan Deolasee" <[EMAIL PROTECTED]> writes:> Yes. The last bit in the t_infomask is used up to mark presence of overflow > tuple header. But I believe there are few more bits that can be reused. > There are three bits available in the t_ctid field

Re: [HACKERS] Frequent Update Project: Design Overview of HOT

On Fri, 2006-11-10 at 09:51 +0200, Hannu Krosing wrote: > > What are the advantages of HOT over SITC (other than cool name) ? > > still wondering this, is it just the abilty to span multiple pages ? Multiple page spanning, copy back/VACUUM support, separate overflow relation to prevent heap grow

Re: [HACKERS] Frequent Update Project: Design Overview of HOT

Ühel kenal päeval, R, 2006-11-10 kell 09:06, kirjutas Hannu Krosing: > Ühel kenal päeval, N, 2006-11-09 kell 18:28, kirjutas Tom Lane: > > "Simon Riggs" <[EMAIL PROTECTED]> writes: > > > As more UPDATEs take place these tuple chains would grow, making > > > locating the latest tuple take progressiv

Re: [HACKERS] Frequent Update Project: Design Overview of HOT Updates

"Pavan Deolasee" <[EMAIL PROTECTED]> writes: > On 11/10/06, Tom Lane <[EMAIL PROTECTED]> wrote: > (2) Isn't this full of race conditions? > I agree, there could be race conditions. But IMO we can handle those. Doubtless you can prevent races by introducing a bunch of additional locking. The qu

Re: [HACKERS] Frequent Update Project: Design Overview of HOT

Ühel kenal päeval, N, 2006-11-09 kell 18:28, kirjutas Tom Lane: > "Simon Riggs" <[EMAIL PROTECTED]> writes: > > As more UPDATEs take place these tuple chains would grow, making > > locating the latest tuple take progressively longer. > > This is the part that bothers me --- particularly the random

Re: [HACKERS] Frequent Update Project: Design Overview of HOT Updates

On 11/10/06, Tom Lane <[EMAIL PROTECTED]> wrote: "Pavan Deolasee" <[EMAIL PROTECTED]> writes:> On 11/10/06, Josh Berkus < josh@agliodbs.com> wrote: >> I believe that's the "unsolved technical issue" in the prototype, unless>> Pavan has solved it in the last two weeks.   Pavan?>>> When an overflow

Re: [HACKERS] Frequent Update Project: Design Overview of HOT Updates

"Pavan Deolasee" <[EMAIL PROTECTED]> writes: > On 11/10/06, Josh Berkus wrote: >> I believe that's the "unsolved technical issue" in the prototype, unless >> Pavan has solved it in the last two weeks. Pavan? >> > When an overflow tuple is copied back to the main heap, the overflow tuple > is >

Re: [HACKERS] Frequent Update Project: Design Overview of HOT Updates

On 11/10/06, Josh Berkus wrote: Tom,> Actually, you omitted to mention the locking aspects of moving tuples> around --- exactly how are you going to make that work without breaking> concurrent scans?I believe that's the "unsolved technical issue" in the prototype, unless Pavan h

Re: [HACKERS] Frequent Update Project: Design Overview of HOT Updates

Tom, > Actually, you omitted to mention the locking aspects of moving tuples > around --- exactly how are you going to make that work without breaking > concurrent scans? I believe that's the "unsolved technical issue" in the prototype, unless Pavan has solved it in the last two weeks. Pavan?

Re: [HACKERS] Frequent Update Project: Design Overview of HOT Updates

"Simon Riggs" <[EMAIL PROTECTED]> writes: > As more UPDATEs take place these tuple chains would grow, making > locating the latest tuple take progressively longer. This is the part that bothers me --- particularly the random-access nature of the search. I wonder whether you couldn't do something

Re: [HACKERS] Frequent Update Project: Design Overview of HOTUpdates

On Thu, 2006-11-09 at 13:21 -0800, Josh Berkus wrote: > Simon, > > > If we perform an update that meets the HOT criteria then we put the > > new version into the overflow relation; we describe this as a HOT > > UPDATE. If we perform an update that does not meet the criteria, then we > > carry on w

Re: [HACKERS] Frequent Update Project: Design Overview of HOT Updates

Simon, > If we perform an update that meets the HOT criteria then we put the > new version into the overflow relation; we describe this as a HOT > UPDATE. If we perform an update that does not meet the criteria, then we > carry on with the existing/old MVCC behaviour; we describe this as a > non-H

Re: [HACKERS] Frequent Update Project: Design Overview of HOTUpdates

On Thu, 2006-11-09 at 18:49 +0100, Martijn van Oosterhout wrote: > Nice idea, just one question: > It seems to me that bitmap index scans will get these same > characteristics also, right? The bitmap scan will have to follow the > chain of any possibly matching tuple in any of the blocks that are

Re: [HACKERS] Frequent Update Project: Design Overview of HOT Updates

Nice idea, just one question: On Thu, Nov 09, 2006 at 05:13:16PM +, Simon Riggs wrote: > Behavioural Characteristics > --- > > With HOT, it is easily possible that the chain of prior versions spans > many blocks. The chain always starts with the block of the root tuple

[HACKERS] Frequent Update Project: Design Overview of HOT Updates

Design Overview of HOT Updates -- The objective is to increase the speed of the UPDATE case, while minimizing the overall negative effects of the UPDATE. We refer to the general requirement as *Frequent Update Optimization*, though this design proposal is for Heap Overf