Skip site navigation (1) Skip section navigation (2)

Re: Hint Bits and Write I/O

From: "Florian G(dot) Pflug" <fgp(at)phlo(dot)org>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Hint Bits and Write I/O
Date: 2008-05-27 21:28:08
Message-ID: 483C7CE8.2090404@phlo.org (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
Simon Riggs wrote:
> After some discussions at PGCon, I'd like to make some proposals for
> hint bit setting with the aim to reduce write overhead.
> 
> Currently, when we see an un-hinted row we set the bit, if possible and
> then dirty the block.
> 
> If we were to set the bit but *not* dirty the block we may be able to
> find a reduction in I/O. In many cases this would make no difference at
> all, since we often set hints on an already dirty block. In other cases,
> particularly random INSERTs, UPDATEs and DELETEs against large tables
> this would reduce I/O, though possibly increase accesses to clog.

Hm, but the io overhead of hit-bit setting occurs only once, while the
pressure on the clog is increased until we set the hint-bit. This looks 
like not writing the hit-bit update to disk results in worse throughput 
unless there are many updated, and only very few selects. But not too 
many updates either, because if a page gets hit by tuple updates faster 
than the bgwriter writes it out, you won't waste any io on hit-bit-only 
writes either. That might turn out to be a pretty slim window which 
actually shows substantial IO savings...

> My proposal is to have this as a two-stage process. When we set the hint
> on a tuple in a clean buffer we mark it BM_DIRTY_HINTONLY, if not
> already dirty. If we set a hint on a buffer that is BM_DIRTY_HINTONLY
> then we mark it BM_DIRTY.
> 
> The objective of this is to remove effects of single index accesses.
So effectively, only the first hit-bit update hitting a previously clean 
buffer gets treated specially - the second hit-bit update flags the 
buffer as dirty, just as it does now? That sounds a bit strange - why is 
it exactly the *second* write that triggers the dirtying? Or did I 
missunderstand what you wrote?

regards, Florian Pflug

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2008-05-27 21:31:52
Subject: Re: ERRORDATA_STACK_SIZE panic crashes on Windows
Previous:From: Tom LaneDate: 2008-05-27 21:20:17
Subject: Re: ERRORDATA_STACK_SIZE panic crashes on Windows

pgsql-patches by date

Next:From: Simon RiggsDate: 2008-05-27 22:10:42
Subject: Re: Hint Bits and Write I/O
Previous:From: Tom LaneDate: 2008-05-27 20:24:05
Subject: Re: BUG #4203: perform dblink() in begin/exception returns wrong SQLSTATE code

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group