From: Simon Riggs <simon@2ndquadrant.com>
To: Florian G. Pflug <fgp@phlo.org>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: Hint Bits and Write I/O
Date: Tue, 27 May 2008 23:10:42 +0100
Message-ID: <1211926242.4489.301.camel@ebony.site> (raw)
In-Reply-To: <483C7CE8.2090404@phlo.org>
References: <1211916951.4489.232.camel@ebony.site>
<483C7CE8.2090404@phlo.org>
On Tue, 2008-05-27 at 23:28 +0200, Florian G. Pflug wrote:
> 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?
Hmm, I think the question is: How many hint bits need to be set before
we mark the buffer dirty? (N)
Should it be 1, as it is now? Should it be never? Never is a long time.
As N increases, clog accesses increase. So it would seem there is likely
to be an optimal value for N.
Each buffer read into shared_buffers will stay there for a certain
period of time. During that time, how many hint bits will be set on
otherwise clean blocks? We can draw that as a frequency distribution of
the number of hint bit set operations before the block leaves
shared_buffers. In a small database, the % of blocks with #hint bits
sets = 1 is very low, since we expect the blocks to stay in cache for
long periods. In a large database, the % of blocks with #hint bit sets =
1 increases dramatically, since the cache churns more quickly and the
frequency of access to each block *may* be lower. If we dirty only when
#hint bit sets >= 2 then we will remove a large proportion of I/O from
random selects/updates.
Remember that we are setting the hint bit on the tuples in buffers, just
not setting BM_DIRTY quickly. So if we have just a single bit set, but
many buffer accesses we perform no additional I/O, nor additional clog
access.
So, based on all of the above:
* For large databases, values of N=2 seem appropriate.
* For small databases, values of N=1 seem appropriate.
Perhaps we can vary this according to the size of database/table?
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
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: pgsql-patches@postgresql.org
Cc: simon@2ndquadrant.com, fgp@phlo.org, pgsql-hackers@postgresql.org
Subject: Re: Hint Bits and Write I/O
In-Reply-To: <1211926242.4489.301.camel@ebony.site>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox