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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers