Message-ID: <4B4FFA5B.8000901@2ndquadrant.com>
Date: Fri, 15 Jan 2010 00:17:15 -0500
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Carlo Stonebanks <stonec.register@sympatico.ca>
CC: pgsql-performance@postgresql.org
Subject: Re: New server to improve performance on our large and
	busy DB - advice?
References: <hi41os$eiv$1@news.hub.org>
	<b1b9fac61001071305vf182f3ajff6827f92c943c68@mail.gmail.com>
	<hi5kpr$184c$1@news.hub.org>
	<dcc563d11001071740q572cdae2re410788fe790dbf6@mail.gmail.com>
	<hinqih$1o1l$1@news.hub.org>
In-Reply-To: <hinqih$1o1l$1@news.hub.org>
Content-Type: text/plain; charset=iso-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Carlo Stonebanks wrote:
> 1) Which RAID level would you recommend

It looks like you stepped over a critical step, which is "will the 
server have a good performing RAID card?".  Your whole upgrade could 
underperform if you make a bad mistake on that part.  It's really 
important to nail that down, and to benchmark to prove you got what you 
expected from your hardware vendor.

> 3) If we were to port to a *NIX flavour, which would you recommend? 
> (which support trouble-free PG builds/makes please!)

The only platform I consider close to trouble free as far as the PG 
builds working without issues are RHEL/CentOS, due to the maturity of 
the PGDG yum repository and how up to date it's kept.  Every time I 
wander onto another platform I find the lag and care taken in packaging 
PostgreSQL to be at least a small step down from there.

> 4) Is this the right PG version for our needs?

8.4 removes the FSM, which takes away a common source for unexpected 
performance issues when you overflow max_fsm_pages one day.  If you're 
going to deploy 8.3, you need to be more careful to monitor the whole 
VACUUM process; it's easier to ignore in 8.4 and still get by OK.  As 
far as general code stability goes, I think it's a wash at this point.  
You might discover a bug in 8.4 that causes a regression, but I think 
you're just as likely to run into a situation that 8.3 handles badly 
that's improved in 8.4.  Hard to say which will work out better in a 
really general way.

> . We believe our requirements are exceptional, and we would benefit 
> immensely from setting up the PG planner to always favour 
> index-oriented decisions - which seems to contradict everything that 
> PG advice suggests as best practice.

Pretty much everyone thinks their requirements are exceptional.  It's 
funny how infrequently that's actually true.  The techniques that favor 
index-use aren't that unique:  collect better stats, set basic 
parameters correctly, adjust random_page_cost, investigate plans that 
don't do what you want to figure out why.  It's easy to say there's 
something special about your data rather than follow fundamentals here; 
I'd urge you to avoid doing that.  The odds that the real issue is that 
you're feeding the optimizer bad data is more likely than most people 
think, which brings us to:

> Current non-default conf settings are:

I don't see effective_cache_size listed there.  If that's at the 
default, I wouldn't be surprised that you're seeing sequential scans 
instead of indexed ones far too often.


> max_connections = 200
> work_mem = 512MB

This is a frightening combination by the way.

-- 
Greg Smith    2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com  www.2ndQuadrant.com