From: Simon Riggs <simon@2ndquadrant.com>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Hint Bits and Write I/O
Date: Tue, 27 May 2008 20:35:51 +0100
Message-ID: <1211916951.4489.232.camel@ebony.site> (raw)
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
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, pgsql-hackers@postgresql.org
Subject: Re: Hint Bits and Write I/O
In-Reply-To: <1211916951.4489.232.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