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 |
Message-ID: | 200807151355.m6FDtMj22014@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-patches |
Added to TODO:
* Consider decreasing the I/O caused by updating tuple hint bits
http://archives.postgresql.org/pgsql-hackers/2008-05/msg00847.php
---------------------------------------------------------------------------
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:
> http://www.postgresql.org/mailpref/pgsql-hackers
--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
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? |
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 |