Sorry for a long pause on this thread. A new arrival at home kept me
occupied all the time.
This thread saw a lot of ideas and suggestions from different people. I
don't think we had an agreement one way or the other on any of them, but
let me summarize the discussion for archival and taking furth
On Thursday, December 20, 2012, Robert Haas wrote:
>
> IMHO, it's probably fairly hopeless to make a pure pgbench workload
> show a benefit from index-only scans. A large table under a very
> heavy, completely random write workload is just about the worst
> possible case for index-only scans. In
On Wednesday, December 19, 2012, Robert Haas wrote:
> On Wed, Dec 19, 2012 at 12:26 PM, Pavan Deolasee
> > wrote:
>
> > I would like to run some pgbench tests where we get the system in a
> > steady state such as all/most updates are HOT updates (not entirely
> > unlikely scenario for many real li
On Thu, Dec 20, 2012 at 1:30 PM, Tom Lane wrote:
> Pavan Deolasee writes:
>> On Thu, Dec 20, 2012 at 10:55 PM, Tom Lane wrote:
>>> Seems unlikely to be a win. We only care about freezing tuples in the
>>> context of being able to advance a relation-wide relfrozenxid horizon.
>>> Freezing pages
Pavan Deolasee writes:
> On Thu, Dec 20, 2012 at 10:55 PM, Tom Lane wrote:
>> Seems unlikely to be a win. We only care about freezing tuples in the
>> context of being able to advance a relation-wide relfrozenxid horizon.
>> Freezing pages retail accomplishes nothing whatsoever towards that goal
On Thu, Dec 20, 2012 at 10:59 PM, Robert Haas wrote:
> On Thu, Dec 20, 2012 at 11:49 AM, Pavan Deolasee
> wrote:
>> I wonder if we should add a flag to heap_page_prune and try to do some
>> additional work if its being called from lazy vacuum such as setting
>> the VM bit and the tuple freeze. II
On Thu, Dec 20, 2012 at 10:55 PM, Tom Lane wrote:
> Pavan Deolasee writes:
>> Ok. Will try to read archives to see what was actually suggested and
>> why it was put on back burner. BTW at the risk of being shot down
>> again, I wonder if can we push down the freeze operation to HOT prune
>> also.
On Thu, Dec 20, 2012 at 11:49 AM, Pavan Deolasee
wrote:
> I wonder if we should add a flag to heap_page_prune and try to do some
> additional work if its being called from lazy vacuum such as setting
> the VM bit and the tuple freeze. IIRC I had put something like that in
> the early patches, but
Pavan Deolasee writes:
> Ok. Will try to read archives to see what was actually suggested and
> why it was put on back burner. BTW at the risk of being shot down
> again, I wonder if can we push down the freeze operation to HOT prune
> also.
Seems unlikely to be a win. We only care about freezin
On Thu, Dec 20, 2012 at 9:23 PM, Robert Haas wrote:
> On Wed, Dec 19, 2012 at 11:12 PM, Pavan Deolasee
>
>
>> I'm very reluctant to suggest that we can solve
>> this my setting aside another page-level bit to track visibility of
>> tuples for heapscans. Or even have a bit in the tuple header itsel
On Thu, Dec 20, 2012 at 4:58 AM, Simon Riggs wrote:
> ISTM that if someone spots a block that could use cleanup, they mark
> the block as BM_PIN_COUNT_WAITER, but don't set pid. Then when they
> unpin the block they send a signal/queue work for a special cleaning
> process to come in and do the wo
On Wed, Dec 19, 2012 at 11:12 PM, Pavan Deolasee
wrote:
> Yeah, VM buffer contention can become prominent if we break the
> invariant that page level bit status implies the vm bit status, at
> least when its clear.OTOH IMHO we need some mechanism to address the
> issue of aggressive clearing of th
On 20 December 2012 00:10, Pavan Deolasee wrote:
> I just thought that we can fairly easily limit the damage if we are
> really worried about SELECTs being penalised. What if we set a
> configurable limit on *extra* things that a query may do which is
> otherwise not very useful for the query its
On 20 December 2012 00:43, Robert Haas wrote:
> On Wed, Dec 19, 2012 at 12:39 PM, Simon Riggs wrote:
>> The benefit of saying that only UPDATEs clean the block is that this
>> penalises only the workload making the mess, rather than everybody
>> cleaning up repeatedly over one messy guy.
>
> Righ
On Thursday, December 20, 2012 6:14 AM Robert Haas wrote:
> On Wed, Dec 19, 2012 at 12:39 PM, Simon Riggs
> wrote:
> > The benefit of saying that only UPDATEs clean the block is that this
> > penalises only the workload making the mess, rather than everybody
> > cleaning up repeatedly over one mes
On Thu, Dec 20, 2012 at 6:12 AM, Robert Haas wrote:
> On Wed, Dec 19, 2012 at 12:26 PM, Pavan Deolasee
> wrote:
>> This can be handled by breaking 1-to-1 mapping on VM bit and
>> PD_ALL_VISIBLE bit. So seq scans will only look at PD_ALL_VISIBLE. It
>> was proposed by Andres up thread, but shot do
On Wed, Dec 19, 2012 at 12:39 PM, Simon Riggs wrote:
> The benefit of saying that only UPDATEs clean the block is that this
> penalises only the workload making the mess, rather than everybody
> cleaning up repeatedly over one messy guy.
Right, but there are plenty of situations where having ever
On Wed, Dec 19, 2012 at 12:26 PM, Pavan Deolasee
wrote:
> This can be handled by breaking 1-to-1 mapping on VM bit and
> PD_ALL_VISIBLE bit. So seq scans will only look at PD_ALL_VISIBLE. It
> was proposed by Andres up thread, but shot down by Tom and Simon. But
> I still feel that was over reacti
On Thu, Dec 20, 2012 at 5:05 AM, Simon Riggs wrote:
>
>
> And those last things are being debated hotly. We definitely need to
> ask whether the way things are now can be tweaked to be better. The
> major mechanics need not be reviewed, but the tradeoffs and balances?
> Definitely.
>
I have zero
On Thu, Dec 20, 2012 at 12:22 AM, Tom Lane wrote:
> Pavan Deolasee writes:
>
>
>> What if we wrap that into the WAL generated by HOT prune itself ?
>
> What WAL? The case we're worried about here is that there's nothing
> else for HOT prune to do.
>
Does such a case exist ? Or at least, is it t
On 19 December 2012 17:26, Pavan Deolasee wrote:
> We definitely
> made great progress by having HOT
Yes, definitely. Great work. That is not for debate.
> But I think generally HOT made great difference to the
> system as a whole, may be at a cost of slowdown for some read-only,
> select queri
Pavan Deolasee writes:
> On Wed, Dec 19, 2012 at 9:51 PM, Tom Lane wrote:
>> If we start generating a lot of useless WAL activity and I/O as
>> a result of thrashing the all-visible bit, it won't be so tolerable
>> anymore.
> What if we wrap that into the WAL generated by HOT prune itself ?
Wha
On 19 December 2012 16:21, Tom Lane wrote:
> Robert Haas writes:
>> On Sat, Dec 15, 2012 at 4:48 PM, Tom Lane wrote:
>>> Maybe it's time to look at what it'd take for the low-level scan
>>> operations to know whether they're scanning the target relation of
>>> an UPDATE query, so that we could s
On Wed, Dec 19, 2012 at 10:40 PM, Robert Haas wrote:
> On Wed, Dec 19, 2012 at 11:49 AM, Pavan Deolasee
> wrote:
>> Also, if extra WAL-logging is really worrisome, may be we should again
>> seriously reconsider my idea of *not* clearing the bit at HOT update.
>> My apologies for repeating myself.
On Wed, Dec 19, 2012 at 11:49 AM, Pavan Deolasee
wrote:
> Also, if extra WAL-logging is really worrisome, may be we should again
> seriously reconsider my idea of *not* clearing the bit at HOT update.
> My apologies for repeating myself. But that seems very important in a
> steady system when almo
On Wed, Dec 19, 2012 at 9:51 PM, Tom Lane wrote:
> If we start generating a lot of useless WAL activity and I/O as
> a result of thrashing the all-visible bit, it won't be so tolerable
> anymore.
What if we wrap that into the WAL generated by HOT prune itself ?
Would that address your concerns f
Robert Haas writes:
> On Sat, Dec 15, 2012 at 4:48 PM, Tom Lane wrote:
>> Maybe it's time to look at what it'd take for the low-level scan
>> operations to know whether they're scanning the target relation of
>> an UPDATE query, so that we could skip pruning altogether except
>> when a HOT update
On Wed, Dec 19, 2012 at 8:32 PM, Robert Haas wrote:
> On Sat, Dec 15, 2012 at 4:48 PM, Tom Lane wrote:
>> On the other hand, the HOT prune operation itself is worthless when
>> we're running a SELECT. The only reason we do it that way is that we
>> have to prune before the query starts to use th
On Sat, Dec 15, 2012 at 4:48 PM, Tom Lane wrote:
> On the other hand, the HOT prune operation itself is worthless when
> we're running a SELECT. The only reason we do it that way is that we
> have to prune before the query starts to use the page, else pruning
> might invalidate pointers-to-tuples
On Sunday, December 16, 2012 11:14 PM Tom Lane wrote:
> Pavan Deolasee writes:
> > On Sun, Dec 16, 2012 at 3:10 PM, Simon Riggs
> wrote:
> >> As explained above, I disagree that it looks like a good idea, and
> >> you've shown no evidence it would be or is true.
>
> > Lets separate out these two
On Sun, Dec 16, 2012 at 11:14 PM, Tom Lane wrote:
> And I agree with him that your proposed
> redefinition of the bit's meaning to avoid that is pretty horrid;
> it's ugly, complicates the invariant quite a lot, and breaks some
> existing usages of the bit.
(slammed.. feels the pain) You defini
Pavan Deolasee writes:
> On Sun, Dec 16, 2012 at 3:10 PM, Simon Riggs wrote:
>> As explained above, I disagree that it looks like a good idea, and
>> you've shown no evidence it would be or is true.
> Lets separate out these two issues. What you are suggesting as a
> follow up to Tom's idea, I'v
On 2012-12-16 16:25:03 +, Simon Riggs wrote:
> On 16 December 2012 14:41, Andres Freund wrote:
> > On 2012-12-16 13:23:56 +0530, Pavan Deolasee wrote:
> >> Another idea could have been to NOT clear the visibility bit when a
> >> HOT update happens. Such tuple can get pruned by HOT prune, so we
On 16 December 2012 14:41, Andres Freund wrote:
> On 2012-12-16 13:23:56 +0530, Pavan Deolasee wrote:
>> Another idea could have been to NOT clear the visibility bit when a
>> HOT update happens. Such tuple can get pruned by HOT prune, so we
>> don't need vacuum per se, and the index-only scans ar
On Sun, Dec 16, 2012 at 3:10 PM, Simon Riggs wrote:
>
>
> As explained above, I disagree that it looks like a good idea, and
> you've shown no evidence it would be or is true.
>
Lets separate out these two issues. What you are suggesting as a
follow up to Tom's idea, I've no objection to that and
On 2012-12-15 16:48:08 -0500, Tom Lane wrote:
> Simon Riggs writes:
> > Doing that only makes sense when we're running a SELECT. Setting the
> > all visible bit immediately prior to an UPDATE that clears it again is
> > pointless effort, generating extra work for no reason.
>
> On the other hand,
On 2012-12-16 13:23:56 +0530, Pavan Deolasee wrote:
> Another idea could have been to NOT clear the visibility bit when a
> HOT update happens. Such tuple can get pruned by HOT prune, so we
> don't need vacuum per se, and the index-only scans are not affected
> because the update was a HOT update,
On 16 December 2012 07:53, Pavan Deolasee wrote:
> On Sun, Dec 16, 2012 at 3:18 AM, Tom Lane wrote:
>> Simon Riggs writes:
>>> Doing that only makes sense when we're running a SELECT. Setting the
>>> all visible bit immediately prior to an UPDATE that clears it again is
>>> pointless effort, gen
On Sun, Dec 16, 2012 at 3:18 AM, Tom Lane wrote:
> Simon Riggs writes:
>> Doing that only makes sense when we're running a SELECT. Setting the
>> all visible bit immediately prior to an UPDATE that clears it again is
>> pointless effort, generating extra work for no reason.
>
> On the other hand,
Simon Riggs writes:
> Doing that only makes sense when we're running a SELECT. Setting the
> all visible bit immediately prior to an UPDATE that clears it again is
> pointless effort, generating extra work for no reason.
On the other hand, the HOT prune operation itself is worthless when
we're ru
On 15 December 2012 12:42, Pavan Deolasee wrote:
> We discussed this idea in the past [1] and Robert recently again
> mentioned this in another thread [2]. Please see a rebased/revised
> patch attached with the mail. This is mostly similar to what I's
> submitted in [1] except some additions to al
We discussed this idea in the past [1] and Robert recently again
mentioned this in another thread [2]. Please see a rebased/revised
patch attached with the mail. This is mostly similar to what I's
submitted in [1] except some additions to also compute visibility
cut-off XID. I also removed a warnin
42 matches
Mail list logo
