|From:||Bruce Momjian <bruce(at)momjian(dot)us>|
|To:||Simon Riggs <simon(at)2ndquadrant(dot)com>|
|Subject:||Re: Hint Bits and Write I/O|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
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 www.2ndQuadrant.com
> 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> http://momjian.us
+ If your life is a hard drive, Christ can be your backup. +
|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?|
|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|