Message-ID: <4DBB2FA3.8010104@2ndquadrant.com>
Date: Fri, 29 Apr 2011 17:37:39 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: James Mansion <james@mansionfamily.plus.com>
CC: Robert Haas <robertmhaas@gmail.com>,
	Claudio Freire <klaussfreire@gmail.com>,
	Tomas Vondra <tv@fuzzy.cz>, pgsql-performance@postgresql.org
Subject: Re: Performance
References: <DF2D5436-117C-4D02-9B3C-A55723B7DDE1@darkstatic.com>
	<20110412171855.GA14292@tux>
	<FC3A3A2B-3ECB-41BA-8F94-356D6FED3695@darkstatic.com>
	<4DA496FA.3070908@fuzzy.cz>
	<8F22D592-23C1-4A3C-94A5-48363332ADD3@darkstatic.com>
	<4DA4BFA2.5060601@fuzzy.cz>
	<B93DFBBB-DA56-4044-A508-0B7E4A2CFD28@darkstatic.com>
	<4DA4D40A.4010200@fuzzy.cz>
	<A0B9339E-9D58-4842-A206-050B773360B6@darkstatic.com>
	<4DA56DA8020000250003C783@gw.wicourts.gov>
	<BANLkTinJqXqErAtQg--1MOaw2LJNFZmwOg@mail.gmail.com>
	<BANLkTimyWkoX8Dj=4CKAjhY82ibru-An7g@mail.gmail.com>
	<4DA6216E.9020907@fuzzy.cz>
	<BANLkTim_m-oz3UMXEKUdFjWJ6uuAza79cw@mail.gmail.com>
	<4DA63125.5070106@fuzzy.cz>
	<BANLkTikYtnzTfS8Yjc-3ap9kysXLLpJwmg@mail.gmail.com>
	<D2F0DB51-7D42-4573-99CA-B72E28440B16@gmail.com>
	<4DBA75DC.6070506@mansionfamily.plus.com>
	<4DBB09B5.80108@2ndquadrant.com>
	<4DBB1F29.6010407@mansionfamily.plus.com>
In-Reply-To: <4DBB1F29.6010407@mansionfamily.plus.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

James Mansion wrote:
> I thought I was clear that it should present some stats to the DBA, 
> not that it would try to auto-tune?

You were.  But people are bound to make decisions about how to retune 
their database based on that information.  The situation when doing 
manual tuning isn't that much different, it just occurs more slowly, and 
with the potential to not react at all if the data is incoherent.  That 
might be better, but you have to assume that a naive person will just 
follow suggestions on how to re-tune based on that the same way an 
auto-tune process would.

I don't like this whole approach because it takes something the database 
and DBA have no control over (read timing) and makes it a primary input 
to the tuning model.  Plus, the overhead of collecting this data is big 
relative to its potential value.

Anyway, how to collect this data is a separate problem from what should 
be done with it in the optimizer.  I don't actually care about the 
collection part very much; there are a bunch of approaches with various 
trade-offs.  Deciding how to tell the optimizer about what's cached 
already is the more important problem that needs to be solved before any 
of this takes you somewhere useful, and focusing on the collection part 
doesn't move that forward.  Trying to map the real world into the 
currently exposed parameter set isn't a solvable problem.  We really 
need cached_page_cost and random_page_cost, plus a way to model the 
cached state per relation that doesn't fall easily into feedback loops.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books