Re: Hint Bits and Write I/O

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Hint Bits and Write I/O
Date: 2008-07-15 13:55:22
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers pgsql-patches

Added to TODO:

* Consider decreasing the I/O caused by updating tuple hint bits


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
> PostgreSQL Training, Services and Support
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:

Bruce Momjian <bruce(at)momjian(dot)us>

+ If your life is a hard drive, Christ can be your backup. +

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2008-07-15 14:09:08 Re: PATCH: CITEXT 2.0 v3
Previous Message Xiao Meng 2008-07-15 12:18:45 Re: Is there anyway to create a TupleDesc with uint32 attribute easily?

Browse pgsql-patches by date

  From Date Subject
Next Message Tom Lane 2008-07-15 14:56:55 Re: [PATCHES] WIP: executor_hook for pg_stat_statements
Previous Message Guillaume Lelarge 2008-07-15 09:10:37 Re: \d+ should display the storage options for columns