On 9/14/07, Tom Lane <[EMAIL PROTECTED]> wrote:
>
>
> HeapTupleSatisfiesAbort is bogus because it has failed to track recent
> changes in tqual.c.
Oh. I should have been aware.
Rather than fix it, though, I question why we need it at all. The only
> use of it is in heap_prune_tuplechain() and
Okay, something else (a real problem this time ;-)):
HeapTupleSatisfiesAbort is bogus because it has failed to track recent
changes in tqual.c.
Rather than fix it, though, I question why we need it at all. The only
use of it is in heap_prune_tuplechain() and ISTM that code is redundant,
wrong, or
On 9/13/07, Tom Lane <[EMAIL PROTECTED]> wrote:
>
>
>
> Never mind ... though my
> suspicions would probably not have been aroused if anyone had bothered
> to fix the comments.
>
>
Yeah, my fault. I should have fixed that. Sorry about that.
Thanks,
Pavan
--
Pavan Deolasee
EnterpriseDB htt
"Pavan Deolasee" <[EMAIL PROTECTED]> writes:
> On 9/13/07, Tom Lane <[EMAIL PROTECTED]> wrote:
>> You have apparently
>> decided to redefine the WAL record format as using one-based rather than
>> zero-based item offsets, which would be fine if any of the rest of the
>> code had been changed to agr
On 9/13/07, Heikki Linnakangas <[EMAIL PROTECTED]> wrote:
>
>
>
> Yeah, I just checked the work-in-progress patch I sent you back in July.
> I refactored it to use one-based offsets for consistency, since I
> modified log_heap_clean quite heavily anyway.
>
> It's possible that I broke it in the pro
Pavan Deolasee wrote:
> On 9/13/07, Tom Lane <[EMAIL PROTECTED]> wrote:
> You have apparently
>> decided to redefine the WAL record format as using one-based rather than
>> zero-based item offsets, which would be fine if any of the rest of the
>> code had been changed to agree ...
>>
>>
> I know He
On 9/13/07, Tom Lane <[EMAIL PROTECTED]> wrote:
>
>
> I'm curious how much the WAL-recovery aspects of this patch have been
> tested, because heap_xlog_clean seems quite broken.
There are quite a few crash recovery tests that one of our QA persons
(Dharmendra Goyal) has written. I can post them
"Pavan Deolasee" <[EMAIL PROTECTED]> writes:
> Please see the revised patches attached.
I'm curious how much the WAL-recovery aspects of this patch have been
tested, because heap_xlog_clean seems quite broken. You have apparently
decided to redefine the WAL record format as using one-based rather
On 9/11/07, Heikki Linnakangas <[EMAIL PROTECTED]> wrote:
>
>
>
> The crucial design
> decision for HOT is when to prune and when to defragment the page, so
> that when we're doing the UPDATE, there's room in the page.
>
>
Right.
For defragmentation, I am still inclined towards doing it when we se
On Tue, 2007-09-11 at 16:10 -0400, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > On Tue, 2007-09-11 at 09:21 +0530, Pavan Deolasee wrote:
> >> IMHO we are making full circles here. We have already tried LP_DELETE
> >> techniques and moved away to simplify things.
>
> >> We can sav
Simon Riggs <[EMAIL PROTECTED]> writes:
> On Tue, 2007-09-11 at 09:21 +0530, Pavan Deolasee wrote:
>> IMHO we are making full circles here. We have already tried LP_DELETE
>> techniques and moved away to simplify things.
>> We can save rest of the techniques for beta testing period or 8.4.
> Agr
Tom Lane wrote:
> "Heikki Linnakangas" <[EMAIL PROTECTED]> writes:
>> Tom Lane wrote:
>>> Then you don't remember far enough --- either the HeapTupleHeader change
>>> or the varvarlena change would be enough to prevent that. For that
>>> matter, the pd_flags header field that the patch is already
"Heikki Linnakangas" <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Then you don't remember far enough --- either the HeapTupleHeader change
>> or the varvarlena change would be enough to prevent that. For that
>> matter, the pd_flags header field that the patch is already relying on
>> is new f
Tom Lane wrote:
> "Pavan Deolasee" <[EMAIL PROTECTED]> writes:
>> I would actually think twice before even doing this because this would
>> lead to complete change in heap page structure and stop people from
>> upgrading to 8.3 without a complete dump/restore. I don't remember 8.3
>> introduces any
On 9/11/07, Tom Lane <[EMAIL PROTECTED]> wrote:
>
> "Pavan Deolasee" <[EMAIL PROTECTED]> writes:
> > I would actually think twice before even doing this because this would
> > lead to complete change in heap page structure and stop people from
> > upgrading to 8.3 without a complete dump/restore. I
"Pavan Deolasee" <[EMAIL PROTECTED]> writes:
> I would actually think twice before even doing this because this would
> lead to complete change in heap page structure and stop people from
> upgrading to 8.3 without a complete dump/restore. I don't remember 8.3
> introduces any other significant cha
On 9/11/07, Pavan Deolasee <[EMAIL PROTECTED]> wrote:
>
>
> - Track the minimum xmin in the page header to avoid repeated
> (wasted) attempts to prune a Prunable page in the presence of long running
> transactions.
>
>
I would actually think twice before even doing this because this would lead
to
c
On 9/11/07, Tom Lane <[EMAIL PROTECTED]> wrote:
>
> "Pavan Deolasee" <[EMAIL PROTECTED]> writes:
> > Pruning removes intermediate dead tuples by marking their line pointers
> > ~LP_USED and redirecting the root line pointer to the first
> > live/recently_dead tuple in the chain.
>
> It seems utterl
Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
>> On Tue, 2007-09-11 at 01:22 +0530, Pavan Deolasee wrote:
>>> I think the only difference is that the quick pruning does not mark
>>> intermediate tuples ~LP_USED and hence we may avoid WAL logging.
>
>> Sounds great.
>
> What it sounds
On 9/11/07, Heikki Linnakangas <[EMAIL PROTECTED]> wrote:
>
>
>
> We're only changing the offsetnumber part of it, which is 2 bytes. That
> shouldn't cross a hardware sector boundary on any reasonable hardware.
>
>
Not entirely true if we consider line pointer redirection, which involves
changing 4
"Tom Lane" <[EMAIL PROTECTED]> writes:
> What it sounds is utterly unsafe. You can get away with not WAL-logging
> individual bit flips (that is, hint-bit-setting) because either state of
> the page is valid. If I read this proposal correctly it is to change
> t_ctid without WAL-logging, which
On Tue, 2007-09-11 at 09:21 +0530, Pavan Deolasee wrote:
> IMHO we are making full circles here. We have already tried LP_DELETE
> techniques and moved away to simplify things.
> We can save rest of the techniques for beta testing period or 8.4.
Agreed, we're just fine tuning by reinventing old
On Mon, 2007-09-10 at 22:24 -0400, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > On Tue, 2007-09-11 at 01:22 +0530, Pavan Deolasee wrote:
> >> I think the only difference is that the quick pruning does not mark
> >> intermediate tuples ~LP_USED and hence we may avoid WAL logging.
>
Pavan Deolasee wrote:
> IMHO we are making full circles here. We have already tried LP_DELETE
> techniques
> and moved away to simplify things. We also tried reusing dead space without
> running PageRepairFragmentation. Each of these techniques worked just fine
> with slightly different performance
On 9/11/07, Gregory Stark <[EMAIL PROTECTED]> wrote:
>
>
>
>
> You could mark such tuples with LP_DELETE. That would also let other
> transactions quickly tot up how much space would be available if they were
> to
> run PageRepairFragmentation.
>
>
>
>
IMHO we are making full circles here. We have
Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > On Tue, 2007-09-11 at 01:22 +0530, Pavan Deolasee wrote:
> >> I think the only difference is that the quick pruning does not mark
> >> intermediate tuples ~LP_USED and hence we may avoid WAL logging.
>
> > Sounds great.
>
> What it sou
Simon Riggs <[EMAIL PROTECTED]> writes:
> On Tue, 2007-09-11 at 01:22 +0530, Pavan Deolasee wrote:
>> I think the only difference is that the quick pruning does not mark
>> intermediate tuples ~LP_USED and hence we may avoid WAL logging.
> Sounds great.
What it sounds is utterly unsafe. You can
"Pavan Deolasee" <[EMAIL PROTECTED]> writes:
> Pruning removes intermediate dead tuples by marking their line pointers
> ~LP_USED and redirecting the root line pointer to the first
> live/recently_dead tuple in the chain.
It seems utterly unsafe to do this without a vacuum-grade lock on the
page.
"Pavan Deolasee" <[EMAIL PROTECTED]> writes:
> On 9/11/07, Bruce Momjian <[EMAIL PROTECTED]> wrote:
>
>> Right. My point is that pruning only modifies item pointers. It does
>> not change the actual heap tuples. In the quote above, how is Heikki's
>> "quick pruning" different from the pruning
On Tue, 2007-09-11 at 01:22 +0530, Pavan Deolasee wrote:
> I think the only difference is that the quick pruning does not mark
> intermediate tuples ~LP_USED and hence we may avoid WAL logging.
Sounds great.
> Btw, I am not too enthusiastic about quick pruning because it leaves
> behind dead he
Gregory Stark wrote:
> "Bruce Momjian" <[EMAIL PROTECTED]> writes:
>
> > Looking at the patch I see:
> >
> > + /*
> > +* Mark the page as clear of prunable tuples. If we find a tuple
> > which
> > +* may become prunable, we shall set the hint again.
> > +*/
> > +
On Mon, 2007-09-10 at 20:46 +0100, Heikki Linnakangas wrote:
> Bruce Momjian wrote:
> > Pavan Deolasee wrote:
> >> At the end of pruning:
> >>
> >> - line pointer of 1 is redirected to 4
> >> - line pointers of 2 and 3 are marked ~LP_USED
> >> - the offset of 4 and 5 is unchanged
> >>
> > In the q
On 9/11/07, Bruce Momjian <[EMAIL PROTECTED]> wrote:
>
>
>
> Right. My point is that pruning only modifies item pointers. It does
> not change the actual heap tuples. In the quote above, how is Heikki's
> "quick pruning" different from the pruning you are describing?
>
>
>
I think the only diffe
Bruce Momjian wrote:
> Pavan Deolasee wrote:
Pruning does generate a WAL record at the moment. Maybe you could do
some kind of a quick pruning without a WAL record. Like just modify the
ctid of the oldest dead tuple in the chain, or the redirecting line
pointer if there is one,
Pavan Deolasee wrote:
> > > Pruning does generate a WAL record at the moment. Maybe you could do
> > > some kind of a quick pruning without a WAL record. Like just modify the
> > > ctid of the oldest dead tuple in the chain, or the redirecting line
> > > pointer if there is one, to point to the lat
Heikki Linnakangas wrote:
> Bruce Momjian wrote:
> > My guess is that once you prune a tuple
> > you no longer have to check its visibility, and that is where the win is
> > coming from.
>
> Yes, I think that's right.
>
> Oh, one more thing occured to me. Without HOT, we not only mark index
> t
On 9/11/07, Bruce Momjian <[EMAIL PROTECTED]> wrote:
>
> Heikki Linnakangas wrote:
>
> >
> > Pruning does generate a WAL record at the moment. Maybe you could do
> > some kind of a quick pruning without a WAL record. Like just modify the
> > ctid of the oldest dead tuple in the chain, or the redire
On 9/10/07, Heikki Linnakangas <[EMAIL PROTECTED]> wrote:
>
>
> Oh, one more thing occured to me. Without HOT, we not only mark index
> tuples pointing to dead tuples as killed, we remove them altogether if
> the index page gets full. If you modify the test case so that after
> doing the updates, y
On 9/10/07, Florian Pflug <[EMAIL PROTECTED]> wrote:
>
>
> Maybe that risk could be lowered if instead of a flag, we stored the
> minimal global xmin needed to prune at least one tuple.
>
>
I like the idea. The question is whether the chances of a Prunable
page being looked up again and again in p
On Mon, 2007-09-10 at 18:23 +0100, Heikki Linnakangas wrote:
> > If we check a tuple in a chain and the tuple is dead is it possible the
> > pruning operation is cheaper than having to check the tuple again for
> > visibility the next time we see it? If so, we can just always prune
> > when we se
Bruce Momjian wrote:
Looking at the patch I see:
+ /*
+* Mark the page as clear of prunable tuples. If we find a tuple which
+* may become prunable, we shall set the hint again.
+*/
+ PageClearPrunable(page);
I like the idea of the page hint bit, but my quest
"Bruce Momjian" <[EMAIL PROTECTED]> writes:
> Looking at the patch I see:
>
> + /*
> +* Mark the page as clear of prunable tuples. If we find a tuple which
> +* may become prunable, we shall set the hint again.
> +*/
> + PageClearPrunable(page);
>
> I like the i
Pavan Deolasee wrote:
> Until patch version 14, defragmentation and pruning were separate
> operations, though we used to invoke both at the same time. The only
> difference was that pruning can work with a simple exclusive lock
> whereas defragmentation needs vacuum-strength lock. Tom argued
> tha
Bruce Momjian wrote:
> My guess is that once you prune a tuple
> you no longer have to check its visibility, and that is where the win is
> coming from.
Yes, I think that's right.
Oh, one more thing occured to me. Without HOT, we not only mark index
tuples pointing to dead tuples as killed, we
Heikki Linnakangas wrote:
> HEADHOT HOT-opt HOT-pruned
> seqscan 19.921.120.111.5
> idxscan 27.831.430.413.7
>
> Explanations of the columns:
>
> HEAD: CVS HEAD
> HOT-pruned: CVS HEAD + HOT patch v15
> HOT:
On 9/10/07, Simon Riggs <[EMAIL PROTECTED]> wrote:
>
> On Mon, 2007-09-10 at 12:17 +0100, Heikki Linnakangas wrote:
> > Bruce Momjian wrote:
> > > (Can someone time the access time for following a chain that fills an
> > > entire page (the worst case) vs. having a single tuple on the page?)
> >
> >
On 9/8/07, Bruce Momjian <[EMAIL PROTECTED]> wrote:
>
>
>
> Would someone tell us exactly when pruning and defragmenting happens in
> the current version of the patch? If we don't nail this issue down soon
> PostgreSQL 8.3 is going to sail without this patch.
>
>
>
I guess you already have the an
On Mon, 2007-09-10 at 12:17 +0100, Heikki Linnakangas wrote:
> Bruce Momjian wrote:
> > (Can someone time the access time for following a chain that fills an
> > entire page (the worst case) vs. having a single tuple on the page?)
>
> Here is some results, on my laptop.
> HEADH
On 9/6/07, Tom Lane <[EMAIL PROTECTED]> wrote:
>
> "Heikki Linnakangas" <[EMAIL PROTECTED]> writes:
> > When I suggested that we get rid of the LP_DELETE flag for heap tuples,
> > the tuple-level fragmentation and all that, and just take the vacuum
> > lock and call PageRepairFragmentation, I was t
Heikki Linnakangas wrote:
> Explanations of the columns:
>
> HEAD: CVS HEAD
> HOT-pruned: CVS HEAD + HOT patch v15
> HOT: CVS HEAD + HOT patch v15, but with heap_page_prune_defrag short
> circuited to do nothing
> HOT-opt: CVS HEAD + HOT patch v15, but with static XidInMVCC
Bruce Momjian wrote:
> (Can someone time the access time for following a chain that fills an
> entire page (the worst case) vs. having a single tuple on the page?)
Here is some results, on my laptop. The test case is a table with a
single integer column, and a single row in the table. The row is u
On 9/10/07, Heikki Linnakangas <[EMAIL PROTECTED]> wrote:
>
>
>
> I'm repeating myself, but I would split that second operation into two
> parts while we think about this: pruning the entire page, and
> defragmenting (PageRepairFragmentation). Tom wondered why they're
> separated in the patch. As t
Bruce Momjian wrote:
> Tom Lane wrote:
>> The we-already-pinned-the-page problem is a bit nasty but may not be
>> insurmountable.
If you find a way, that would be great.
> As I understand it, there are two HOT features:
>
> Single-chain pruning, which trims HOT chains but doesn't reuse
>
On Mon, 2007-09-10 at 09:03 +0100, Heikki Linnakangas wrote:
> > As I understand it, there are two HOT features:
> >
> > Single-chain pruning, which trims HOT chains but doesn't reuse
> > the space
> >
> > Defragementation, which prunes the entire page and reuses space
> >
On Sun, 2007-09-09 at 07:55 -0400, Bruce Momjian wrote:
> Simon Riggs wrote:
> > On Sat, 2007-09-08 at 23:13 -0400, Bruce Momjian wrote:
> > > In summary, I feel we have the HOT mechanics down well, but the open
> > > issue is _when_ to activate each operation.
> >
> > That has been the only open
Simon Riggs wrote:
> On Sat, 2007-09-08 at 23:13 -0400, Bruce Momjian wrote:
> > In summary, I feel we have the HOT mechanics down well, but the open
> > issue is _when_ to activate each operation.
>
> That has been the only open issue for months. The interplay of behaviour
> is where HOT succeeds
On Sat, 2007-09-08 at 23:13 -0400, Bruce Momjian wrote:
> Defragementation is the HOT feature we really want. Single-chain
> pruning is kind of nice because it speeds things up, but isn't
> necessary. The fact that the entire chain is on the same page makes me
> think that we could just leave si
Tom Lane wrote:
> "Heikki Linnakangas" <[EMAIL PROTECTED]> writes:
> > Tom argued that following the tuple chain is cheap enough, and might
> > even be cheaper than what we have now, that we don't need to prune just
> > for the purpose of keeping the chains short. To which I pointed out that
> > cu
Bruce Momjian <[EMAIL PROTECTED]> writes:
> Simon Riggs wrote:
>> The speed/cost is the same. The issue is *when* we do this. Normal
>> SELECTs will follow the chain each time we do an index lookup.
> But a sequential scan still follows the chain, and that isn't going to
> prune. Why are we more
Florian Pflug <[EMAIL PROTECTED]> writes:
> I was under the impression that pruning *does* free the space occupied
> by DEAD HOT-Tuples (minus the size of a redirection line pointer). It
> just doesn't defragment, so how much of that freed space you can actually
> use to store new tuples is another
Bruce Momjian wrote:
Heikki Linnakangas wrote:
Florian Pflug wrote:
Heikki Linnakangas wrote:
Tom Lane wrote:
Compared to what it currently takes to check the same tuple (a separate
index entry fetch and traversal to the heap page), this is already an
enormous performance improvement.
Though
Simon Riggs wrote:
> > > If we defer pruning until the page is full, worst case we may could end
> > > up with a chain ~240 tuples long, which might need to be scanned
> > > repeatedly. That won't happen often, but it would be better to prune
> > > whenever we hit one of these conditions
> > > - wh
Heikki Linnakangas wrote:
> Florian Pflug wrote:
> > Heikki Linnakangas wrote:
> >> Tom Lane wrote:
> >>> Compared to what it currently takes to check the same tuple (a separate
> >>> index entry fetch and traversal to the heap page), this is already an
> >>> enormous performance improvement.
> >>
Heikki Linnakangas wrote:
Florian Pflug wrote:
Heikki Linnakangas wrote:
Tom Lane wrote:
Compared to what it currently takes to check the same tuple (a separate
index entry fetch and traversal to the heap page), this is already an
enormous performance improvement.
Though keep in mind that we
"Heikki Linnakangas" <[EMAIL PROTECTED]> writes:
> Tom argued that following the tuple chain is cheap enough, and might
> even be cheaper than what we have now, that we don't need to prune just
> for the purpose of keeping the chains short. To which I pointed out that
> currently, without HOT, we m
On Sat, 2007-09-08 at 17:46 +0100, Heikki Linnakangas wrote:
> Tom Lane wrote:
> > Compared to what it currently takes to check the same tuple (a separate
> > index entry fetch and traversal to the heap page), this is already an
> > enormous performance improvement.
>
> Though keep in mind that we
Florian Pflug wrote:
> Heikki Linnakangas wrote:
>> Tom Lane wrote:
>>> Compared to what it currently takes to check the same tuple (a separate
>>> index entry fetch and traversal to the heap page), this is already an
>>> enormous performance improvement.
>>
>> Though keep in mind that we kill inde
Heikki Linnakangas wrote:
Tom Lane wrote:
Compared to what it currently takes to check the same tuple (a separate
index entry fetch and traversal to the heap page), this is already an
enormous performance improvement.
Though keep in mind that we kill index tuples as soon as they're deemed
to b
Tom Lane wrote:
> Compared to what it currently takes to check the same tuple (a separate
> index entry fetch and traversal to the heap page), this is already an
> enormous performance improvement.
Though keep in mind that we kill index tuples as soon as they're deemed
to be dead. Nevertheless, I'
Simon Riggs <[EMAIL PROTECTED]> writes:
> On Fri, 2007-09-07 at 22:08 -0400, Bruce Momjian wrote:
>> I don't see how following a HOT chain is any slower than following an
>> UPDATE chain like we do now.
> The speed/cost is the same. The issue is *when* we do this. Normal
> SELECTs will follow th
On Fri, 2007-09-07 at 22:08 -0400, Bruce Momjian wrote:
> Simon Riggs wrote:
> > > We could begin pruning only when the chain is N long. Currently N=2, but
> > > we could set N=3+ easily enough. There's no code yet to actually count
> > > that, but we can do that easily as we do each lookup. We sho
Simon Riggs wrote:
> > We could begin pruning only when the chain is N long. Currently N=2, but
> > we could set N=3+ easily enough. There's no code yet to actually count
> > that, but we can do that easily as we do each lookup. We should also be
> > able to remember the visibility result for each
Heikki Linnakangas wrote:
> Tom Lane wrote:
> > I wrote:
> >> Hmm ... also check commit status (pg_clog access). I've always thought
> >> that those things couldn't be done in bgwriter, because it wasn't
> >> running real transactions, but right at the moment I can't see that
> >> there is any obs
On Thu, 6 Sep 2007, Heikki Linnakangas wrote:
I wonder if pruning in bgwriter only is enough to make HOT work
efficiently. On a very frequently updated page, bgwriter will have to
work hard to keep up.
One of the goals for how I rebuilt the just-in-time BGW was to try and
make smaller values
Tom Lane wrote:
> I wrote:
>> Hmm ... also check commit status (pg_clog access). I've always thought
>> that those things couldn't be done in bgwriter, because it wasn't
>> running real transactions, but right at the moment I can't see that
>> there is any obstacle. Perhaps that meme dates from a
I wrote:
> Hmm ... also check commit status (pg_clog access). I've always thought
> that those things couldn't be done in bgwriter, because it wasn't
> running real transactions, but right at the moment I can't see that
> there is any obstacle. Perhaps that meme dates from a time when
> GetOldest
On Thu, 2007-09-06 at 19:05 +0100, Heikki Linnakangas wrote:
> Pruning is expensive, you need to scan all tuples on
> the page and write WAL record. And defragment the page if you consider
> that part of pruning. You don't want to do it too aggressively.
OK, so my understanding or the meaning of
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Heikki Linnakangas escribió:
>> and the removed tuples need to be WAL-logged.
> Is this a problem for bgwriter?
bgwriter does checkpoints, which emit WAL records; and they also call
GetOldestXmin. So we know a-priori that those two things work. There
"Heikki Linnakangas" <[EMAIL PROTECTED]> writes:
> Alvaro Herrera wrote:
>> That would be best, if at all possible. You can prune without accessing
>> anything outside the page itself, right?
> Yes, though you do need to have an oldest xmin to determine which tuples
> are dead, and the removed tu
Heikki Linnakangas escribió:
> Alvaro Herrera wrote:
> > Heikki Linnakangas escribió:
> >
> >> Hmm. I wonder if we could prune/defragment in bgwriter?
> >
> > That would be best, if at all possible. You can prune without accessing
> > anything outside the page itself, right?
>
> Yes, though you
"Heikki Linnakangas" <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> I don't follow. HOT chains can only get longer by updates.
> Yes. We don't seem to be on the same wavelength...
> Imagine a page with just one tuple on it:
> 1
> After a bunch of updates, it looks like this
> 1 -> 2 -> 3 ->
Simon Riggs wrote:
> Yes, the main point is that an UPDATE doesn't always allow you to prune.
You can always remove dead HOT tuples in heap_update. But you can never
defragment the page at that point.
> If it did, that would be the right place. Since it doesn't the best
> place to prune is surely
On Thu, 2007-09-06 at 16:15 +0100, Heikki Linnakangas wrote:
> Tom Lane wrote:
> > "Heikki Linnakangas" <[EMAIL PROTECTED]> writes:
> >> Imagine a page with just one tuple on it:
> >
> >> 1
> >
> >> After a bunch of updates, it looks like this
> >
> >> 1 -> 2 -> 3 -> 4 -> 5
> >
> >> 1 is the tu
Heikki Linnakangas escribió:
> Every time you do an index lookup, you have to follow that chain from 1
> to 5. Which gets expensive. That's why we want to prune the chain to
> make it shorter, even if there's still plenty of room on the page for
> updates, and even if we're never going to update i
Florian Pflug wrote:
> Heikki Linnakangas wrote:
>> That's a pretty sensitive tradeoff, we want to prune often to cut the
>> long HOT chains, but not too often because it's pretty expensive to
>> acquire the vacuum lock and move tuples around. I don't think we've
>> found the optimal solution yet.
Alvaro Herrera wrote:
> Heikki Linnakangas escribió:
>
>> Hmm. I wonder if we could prune/defragment in bgwriter?
>
> That would be best, if at all possible. You can prune without accessing
> anything outside the page itself, right?
Yes, though you do need to have an oldest xmin to determine wh
Heikki Linnakangas escribió:
> Hmm. I wonder if we could prune/defragment in bgwriter?
That would be best, if at all possible. You can prune without accessing
anything outside the page itself, right?
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL
On Wed, 2007-09-05 at 18:15 -0400, Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Tom Lane wrote:
> >> Uh, why would any of this code at all execute during a pure lookup?
>
> > No idea. It seems an index lookup tries to prune a heap chain, and he
> > was asking if it should look
Florian Pflug wrote:
> Tom Lane wrote:
> A rather wild idea: Could we maybe pin individual tuples, instead
> of the whole page? Then we'd just have to be careful not to move
> those when pruning during the update.
Uh, that's a huge change. We might be able to keep track of tuples that
we have refe
Tom Lane wrote:
> "Heikki Linnakangas" <[EMAIL PROTECTED]> writes:
>> Imagine a page with just one tuple on it:
>
>> 1
>
>> After a bunch of updates, it looks like this
>
>> 1 -> 2 -> 3 -> 4 -> 5
>
>> 1 is the tuple the indexes point to, others are heap only.
>
> But if we were attempting prun
Me & Greg just had a little chat, and came up with this scheme:
1. on heap_update, if the page is full, you prune the page (but don't
defragment it, because you can't get the vacuum lock). That hopefully
leaves behind a large enough gap to put the new tuple in. Insert the new
tuple in the gap, and
Tom Lane wrote:
"Heikki Linnakangas" <[EMAIL PROTECTED]> writes:
Tom Lane wrote:
Another real problem with doing pruning only in UPDATE path is that
we may end up with long HOT chains if the page does not receive a
UPDATE, after many consecutive HOT updates.
How is that, if the same number of
Alvaro Herrera wrote:
> Pruning is going to take place on next vacuum anyway, isn't it?
>
> AFAIUI the point is that you can't do cleanup selectively only on UPDATE
> because of pointers that your process may already have to the page,
> which is why you wanted to do it on every heap_fetch. I sugg
Alvaro Herrera wrote:
> Pruning is going to take place on next vacuum anyway, isn't it?
Yes. If HOT is working well, you're not going to run vacuum very often,
though.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)-
"Heikki Linnakangas" <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>>> Another real problem with doing pruning only in UPDATE path is that
>>> we may end up with long HOT chains if the page does not receive a
>>> UPDATE, after many consecutive HOT updates.
>>
>> How is that, if the same number of
Tom Lane wrote:
> "Heikki Linnakangas" <[EMAIL PROTECTED]> writes:
>> Tom Lane wrote:
Another real problem with doing pruning only in UPDATE path is that
we may end up with long HOT chains if the page does not receive a
UPDATE, after many consecutive HOT updates.
>>> How is that, if
Heikki Linnakangas wrote:
That's a pretty sensitive tradeoff, we want to prune often to cut the
long HOT chains, but not too often because it's pretty expensive to
acquire the vacuum lock and move tuples around. I don't think we've
found the optimal solution yet. Separating the pruning and defrag
Tom Lane wrote:
> "Heikki Linnakangas" <[EMAIL PROTECTED]> writes:
>> When I suggested that we get rid of the LP_DELETE flag for heap tuples,
>> the tuple-level fragmentation and all that, and just take the vacuum
>> lock and call PageRepairFragmentation, I was thinking that we'd do it in
>> heap_u
"Heikki Linnakangas" <[EMAIL PROTECTED]> writes:
> When I suggested that we get rid of the LP_DELETE flag for heap tuples,
> the tuple-level fragmentation and all that, and just take the vacuum
> lock and call PageRepairFragmentation, I was thinking that we'd do it in
> heap_update and only when we
Pavan Deolasee wrote:
> On 9/6/07, Gregory Stark <[EMAIL PROTECTED]> wrote:
>> "Tom Lane" <[EMAIL PROTECTED]> writes:
>>
>>> ISTM the only time we should be doing HOT cleanup work is when we are
>>> trying to insert a new tuple on that page --- and maybe even only when
>>> we try and it doesn't fit
1 - 100 of 108 matches
Mail list logo
