DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references;
	b=MFcENDISEsG5UTqKzxz7Ljb/X4xB2H1q/7LOXbnJD5cMpHQALLWTwbO4qwJJQigsan
	d1WkAVi4L/+i8Fgp1hQ7bSx8wYD+vV9Rwzk3RI51uYkKExeD6RB535+L7ryTfvGXV+A3
	9TWeLLKtOdQTz8j0i6I9O5gDsnQN0BtzbCIuo=
Message-ID: <dcc563d10812090921l90fd2eci8e1d163258d49ce0@mail.gmail.com>
Date: Tue, 9 Dec 2008 10:21:39 -0700
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: "Scott Carey" <scott@richrelevance.com>
Subject: Re: Need help with 8.4 Performance Testing
Cc: "Jean-David Beyer" <jeandavid8@verizon.net>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
In-Reply-To: 
 <BDFBB77C9E07BE4A984DAAE981D19F961ACA17D9FC@EXVMBX018-1.exch018.msoutlookonline.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <200812071138.01215.josh@agliodbs.com>
	<dcc563d10812072140q1cb257cbs8140b390759cb1a5@mail.gmail.com>
	<Pine.GSO.4.64.0812081442410.5242@westnet.com>
	<dcc563d10812081231y6c71601am677ba828ff0b515e@mail.gmail.com>
	<Pine.GSO.4.64.0812081731510.26019@westnet.com>
	<b42b73150812081723qdf7f203tcba3c3660023890f@mail.gmail.com>
	<Pine.GSO.4.64.0812082225470.13669@westnet.com>
	<493E6DBE.2040605@verizon.net>
	<BDFBB77C9E07BE4A984DAAE981D19F961ACA17D9FC@EXVMBX018-1.exch018.msoutlookonline.net>

On Tue, Dec 9, 2008 at 9:37 AM, Scott Carey <scott@richrelevance.com> wrote:

> As for tipping points and pg_bench -- It doesn't seem to reflect the kind of workload we use postgres for at all, though my workload does a lot of big hashes and seqscans, and I'm curious how much improved those may be due to the hash improvements.  32GB RAM and 3TB data (about 250GB scanned regularly) here.  And yes, we are almost completely CPU bound now except for a few tasks.  Iostat only reports above 65% disk utilization for about 5% of the workload duty-cycle, and is regularly < 20%.  COPY doesn't get anywhere near platter speeds, on indexless bulk transfer.  The highest disk usage spikes occur when some of our radom-access data/indexes get shoved out of cache.  These aren't too large, but high enough seqscan load will cause postgres and the OS to dump them from cache.  If we put these on some SSD's the disk utilization % would drop a lot further.

It definitely reflects our usage pattern, which is very random and
involves tiny bits of data scattered throughout the database.  Our
current database is about 20-25 Gig, which means it's quickly reaching
the point where it will not fit in our 32G of ram, and it's likely to
grow too big for 64Gig before a year or two is out.

> I feel confident in saying that in about a year, I could spec out a medium sized budget for hardware ($25k) for almost any postgres setup and make it almost pure CPU bound.
> SSDs and hybrid tech such as ZFS L2ARC make this possible with easy access to 10k+ iops, and it it will take no more than 12 SATA drives in raid 10 next year (and a good controller or software raid) to get 1GB/sec sequential reads.

Lucky you, having needs that are fulfilled by sequential reads.  :)

I wonder how many hard drives it would take to be CPU bound on random
access patterns?  About 40 to 60?  And probably 15k / SAS drives to
boot.  Cause that's what we're looking at in the next few years where
I work.