To: "Simon Riggs" <simon@2ndquadrant.com>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>,
	"pgsql-hackers" <pgsql-hackers@postgresql.org>,
	"List pgsql-patches" <pgsql-patches@postgresql.org>
Subject: Re: [HACKERS] Hint Bits and Write I/O
In-Reply-To: <1213797209.9468.126.camel@ebony.site> (Simon Riggs's message of
	"Wed\, 18 Jun 2008 14\:53\:29 +0100")
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux)
References: <1211916951.4489.232.camel@ebony.site>
	<15901.1211931165@sss.pgh.pa.us>
	<1213797209.9468.126.camel@ebony.site>
From: Gregory Stark <stark@enterprisedb.com>
Organization: EnterpriseDB
Date: Fri, 27 Jun 2008 15:36:02 +0100
Message-ID: <87hcbfq71p.fsf@oxford.xeocode.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

"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?

> 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.

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?

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!