Message-ID: <55440474.4080507@BlueTreble.com>
Date: Fri, 1 May 2015 17:55:48 -0500
From: Jim Nasby <Jim.Nasby@BlueTreble.com>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9;
 rv:31.0) Gecko/20100101 Thunderbird/31.4.0
MIME-Version: 1.0
To: Robert Haas <robertmhaas@gmail.com>, Simon Riggs <simon@2ndquadrant.com>
CC: Amit Kapila <amit.kapila16@gmail.com>, Petr Jelinek
 <petr@2ndquadrant.com>, Andres Freund <andres@anarazel.de>, Heikki
 Linnakangas <hlinnaka@iki.fi>, Bruce Momjian <bruce@momjian.us>, Sawada
 Masahiko <sawada.mshk@gmail.com>, Greg Stark <stark@mit.edu>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>, Jeff Janes
 <jeff.janes@gmail.com>
Subject: Re: Reducing tuple overhead
References: <20150423162429.GG3055@alap3.anarazel.de>
	<553921B1.7080204@2ndquadrant.com>	<5539491C.6040405@BlueTreble.com>
	<CAA4eK1+R2f+woEsu_Woj6AgoZE6LRxYCVg5kXiC3snKSkxHmFQ@mail.gmail.com>
	<553AA753.9010103@BlueTreble.com>
	<CAA4eK1KbqMicXYFo6MCp7nnOfjkuqyx071c+sAm0c+ftAKSCBw@mail.gmail.com>
	<CANP8+jK=rZw6XrweSjZmsmkU30yef+HCw2KOnEf1cS3PhWVerQ@mail.gmail.com>
 <CA+Tgmob4jJZna8ajxRpUd_d_QCs9X+0Unncp7ZZQ8HrWFDTJCQ@mail.gmail.com>
In-Reply-To: 
 <CA+Tgmob4jJZna8ajxRpUd_d_QCs9X+0Unncp7ZZQ8HrWFDTJCQ@mail.gmail.com>
Content-Type: text/plain; charset="utf-8"; format=flowed
Content-Transfer-Encoding: 7bit
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 01 May 2015 22:55:57.2187 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-Transport-CrossTenantHeadersStamped: BLUPR11MB0674
X-Pg-Spam-Score: -2.1 (--)
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On 4/30/15 7:37 AM, Robert Haas wrote:
> On Thu, Apr 30, 2015 at 8:05 AM, Simon Riggs <simon@2ndquadrant.com> 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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers