Re: Hint Bits and Write I/O

Added to TODO:

* Consider decreasing the I/O caused by updating tuple hint bits
 http://archives.postgresql.org/pgsql-hackers/2008-05/msg00847.php


---------------------------------------------------------------------------

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.
> 
> 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.
> 
> If the bgwriter has time, it will write out BM_DIRTY_HINTONLY buffers,
> though on a consistently busy server this should not occur.
> 
> This new behaviour should reduce the effects of random hint bit setting
> on tables with a low cache hit ratio. This can occur when a table is
> written/read fairly randomly and is much larger than shared_buffers.
> 
> This won't change the behaviour of first-read-after-copy. To improve
> that behaviour, I suggest that we only move from BM_DIRTY_HINTONLY to
> BM_DIRTY when we are setting the hint for a new xid. If we are just
> setting the same xid over-and-over again then we should avoid setting
> the page dirty. So when data has been loaded via COPY, we will just
> check the status of the xid once, then scan the whole page using the
> single-item transaction cache.
> 
> Let's discuss.
> 
> -- 
>  Simon Riggs           www.2ndQuadrant.com
>  PostgreSQL Training, Services and Support
> 
> 
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +