agora inbox for [email protected]  

help / color / mirror / Atom feed

From: Jim Nasby <[email protected]>
To: Robert Haas <[email protected]>
To: Simon Riggs <[email protected]>
Cc: Amit Kapila <[email protected]>
Cc: Petr Jelinek <[email protected]>
Cc: Andres Freund <[email protected]>
Cc: Heikki Linnakangas <[email protected]>
Cc: Bruce Momjian <[email protected]>
Cc: Sawada Masahiko <[email protected]>
Cc: Greg Stark <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Cc: Jeff Janes <[email protected]>
Subject: Re: Reducing tuple overhead
Date: Fri, 1 May 2015 17:55:48 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <CA+Tgmob4jJZna8ajxRpUd_d_QCs9X+0Unncp7ZZQ8HrWFDTJCQ@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<CAA4eK1+R2f+woEsu_Woj6AgoZE6LRxYCVg5kXiC3snKSkxHmFQ@mail.gmail.com>
	<[email protected]>
	<CAA4eK1KbqMicXYFo6MCp7nnOfjkuqyx071c+sAm0c+ftAKSCBw@mail.gmail.com>
	<CANP8+jK=rZw6XrweSjZmsmkU30yef+HCw2KOnEf1cS3PhWVerQ@mail.gmail.com>
	<CA+Tgmob4jJZna8ajxRpUd_d_QCs9X+0Unncp7ZZQ8HrWFDTJCQ@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-hackers>

On 4/30/15 7:37 AM, Robert Haas wrote:
> On Thu, Apr 30, 2015 at 8:05 AM, Simon Riggs <[email protected]> wrote:
>> A much better idea is to work out how to avoid index bloat at cause. If we
>> are running an UPDATE and we cannot get a cleanup lock, we give up and do a
>> non-HOT update, causing the index to bloat. It seems better to wait for a
>> short period to see if we can get the cleanup lock. The short period is
>> currently 0, so lets start there and vary the duration of wait upwards
>> proportionally as the index gets more bloated.

That only happens if there already wasn't enough space on the page so we 
need to Defrag, yes? If there is enough space we can HOT update without 
the cleanup lock.

What would be useful to know is how often we abort a HOT update because 
of lack of free space; that would indicate to a DBA that a lower fill 
factor may be in oredr. What would be useful to -hackers would be stats 
on how often an update would have been HOT if only the page had been pruned.

> What I'd be worried about there is that it would be very hard to tune
> the wait time, and that the operating system scheduling granularity
> (10ms?) would be way too long.

[1] indicates between 0.75 and 6ms by default on Linux. I think FBSD 
still uses a 1000Hz scheduler (1ms), but it's not as clear.

What might be more promising is ways to avoid holding a pin for a long 
time (like the outer side of a nested loop), or being more aggressive 
about attempting the lock (IE: lower the threshold to trigger cleaning).

There's also a (in hindsight) questionable bit of logic in 
heap_page_prune_opt(); once we get the cleanup lock we check the page 
free space a second time. If we managed to actually get the lock, we 
should probably just clean it anyway.

> But I'm in vigorous agreement with you on one point: the solution to
> index bloat (and probably heap bloat, too) is not to clean it up
> faster but to create less of it in the first place.  Making more
> updates HOT is one way to do that.

+1.


1: 
http://stackoverflow.com/questions/16401294/how-to-know-linux-scheduler-time-slice
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com


-- 
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

view thread (27+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Reducing tuple overhead
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox