Subject: Re: [HACKERS] Hint Bits and Write I/O
From: Simon Riggs <simon@2ndquadrant.com>
To: Gregory Stark <stark@enterprisedb.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
 pgsql-hackers <pgsql-hackers@postgresql.org>,
	List pgsql-patches <pgsql-patches@postgresql.org>
In-Reply-To: <87hcbfq71p.fsf@oxford.xeocode.com>
References: <1211916951.4489.232.camel@ebony.site>
	<15901.1211931165@sss.pgh.pa.us> <1213797209.9468.126.camel@ebony.site>
	<87hcbfq71p.fsf@oxford.xeocode.com>
Content-Type: text/plain
Date: Fri, 27 Jun 2008 16:02:18 +0100
Message-Id: <1214578938.3845.423.camel@ebony.site>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit


On Fri, 2008-06-27 at 15:36 +0100, Gregory Stark wrote:
> "Simon Riggs" <simon@2ndquadrant.com> writes:
> 
> > The default and minimum value for this parameter is 1, so very similar to
> > existing behaviour. Expected settings would be 2-5, possibly as high as 20,
> > though those are just educated guesses. So the maximum is set arbitrarily as
> > 100.
> 
> Not a fan of arbitrary constants. ISTM this should just have a maximum of
> MaxHeapTuplesPerPage.
> 
> I'm not really happy with having this parameter at all. It's not something a
> DBA can understand or have any hope of setting intelligently. I assume this is
> a temporary measure until we have a better understanding of what real-world
> factors affect the right values for this knob?

Yes, its a guess at what sort of control we'll need.

> > Temp buffers are never dirtied by hint bit setting. Most temp tables are
> > written in a single command, so that re-accessing clog for temp tuples
> > is seldom costly. This also changes current behaviour.
> 
> I'm not sure I agree with this logic and it doesn't seem like temporary tables
> are an important enough case to start coming up with special cases which may
> help or may hurt. Most people use temporary tables the way you describe but
> I'm sure there's someone out there using temporary tables in a radically
> different fashion.

Thanks for your comments. The patch splits into two parts:
* the machinery to *not* dirty a page when we set hints
* behaviour modifications now that we can tell the difference between
dirty and hinted pages

Nobody has yet come up with any comments about the first half, which is
good. The second part is clearly where much debate will occur. I'm going
to literally split the patch into two, so we can get the machinery into
CVS and then fiddle and argue over the second part over next few months.

> I'm also a bit concerned that *how many hint bits* isn't enough information to
> determine how important it is to write out the page. What about how old the
> oldest transaction is which was hinted? Or how many *unhinted* xmin/xmax
> values were found? If HTSV can hint xmin for a tuple but finds xmax still in
> progress perhaps that's a good sign it's not worth dirtying the page?

Sounds interesting. We can track anything and everything really, but we
do need to come to a firm dirty/not decision at some point.

If you can develop those ideas a bit more by Monday, I'll try to put
them in the patch. (I'm away until then now).

-- 
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support