Date: Tue, 16 Aug 2005 13:30:47 -0400
From: Michael Stone <mstone+postgres@mathom.us>
Subject: Re: [SPAM?] Re: PG8 Tuning
In-reply-to: <200508160912.32263.josh@agliodbs.com>
To: pgsql-performance@postgresql.org
Mail-Followup-To: pgsql-performance@postgresql.org
Message-id: <20050816173047.GC19080@mathom.us>
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii; format=flowed
Content-disposition: inline
References: <1536.217.45.209.171.1123763001.squirrel@www.gradwell.com>
	<1123836428.19976.26.camel@amd64-laptop-spoe>
	<1123779524.6664.1.camel@toonses.gghcwest.com>
	<200508160912.32263.josh@agliodbs.com>
User-Agent: Mutt/1.5.9i

On Tue, Aug 16, 2005 at 09:12:31AM -0700, Josh Berkus wrote:
>However, you are absolutely correct in that it's *relative* advice, not 
>absolute advice.   If, for example, you're using a $100,000 EMC SAN as your 
>storage you'll probably be better off giving it everything and letting its 
>controller and cache handle disk allocation etc.   

Well, you don't have to spend *quite* that much to get a decent storage
array. :) 

>On the other hand, if you're dealing with the 5 drives in a single Dell
>6650, I've yet to encounter a case where a separate xlog disk did not
>benefit an OLTP application.

IIRC, that's an older raid controller that tops out at 128MB write
cache, and 5 spindles ain't a lot--so it makes sense that it would
benefit from a seperate spindle for xlog. Also note that I said the
write cache advice goes out the window if you have a workload that
involves constant writing (or if your xlog writes come in faster than
your write cache can drain) because at that point you essentially drop
back to raw disk speed; I assume the OLTP apps you mention are fairly
write-intensive.  OTOH, in a reasonably safe configuration I suppose
you'd end up with a 3 disk raid5 / 2 disk raid1 or 2 raid 1 pairs on
that dell 6650; is that how you test? Once you're down to that small a
data set I'd expect the system's ram cache to be a much larger
percentage of the working set, which would tend to make the xlog just
about the *only* latency-critical i/o. That's a different creature from
a data mining app that might really benefit from having additional
spindles to accelerate read performance from indices much larger than
RAM. At any rate, this just underscores the need for testing a
particular workload on particular hardware. Things like the disk speed,
raid configuration, write cache size, transaction size, data set size,
working set size, concurrent transactions, read vs write emphasis, etc.,
are each going to have a fairly large impact on performance. 

>For Solaris, the advantage of using a separate disk or partition is that the 
>mount options you want for the xlog (including forcedirectio) are 
>considerably different from what you'd use with the main database.

Yeah, having a seperate partition is often good even if you do have
everything on the same disks.

Mike Stone