Hint Bits and Write I/O

From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Hint Bits and Write I/O
Date: 2008-05-27 19:35:51
Message-ID: 1211916951.4489.232.camel@ebony.site
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers pgsql-patches

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


Browse pgsql-hackers by date

  From Date Subject
Next Message Simon Riggs 2008-05-27 19:59:18 Outer joins and equivalence
Previous Message Dave Page 2008-05-27 19:18:29 Re: ERRORDATA_STACK_SIZE panic crashes on Windows

Browse pgsql-patches by date

  From Date Subject
Next Message Joe Conway 2008-05-27 19:39:37 Re: BUG #4203: perform dblink() in begin/exception returns wrong SQLSTATE code
Previous Message Alex Hunsaker 2008-05-27 18:29:51 guc config_enum_entry add hidden field