Subject: Hint Bits and Write I/O
From: Simon Riggs <simon@2ndquadrant.com>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: text/plain
Date: Tue, 27 May 2008 20:35:51 +0100
Message-Id: <1211916951.4489.232.camel@ebony.site>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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