DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=KpQW2Ud9MJ2PtpLeTsk/emc9taYwyGvsZYEbiZjXhy8p9DWKbnXH9H+4KuDlgwGSQtmp2S8+9rTYlUu2gxdhqz2H7K2rVb9ojtivLw7vAoToQSz8CtWlxwOJiU3UFOLYdh3MQckG5ay9nmE0RGUZoCQsDoDub210kgj9rrPZXis=
Message-ID: <dcc563d10709041603m3f894053ve02d8de15722ffcd@mail.gmail.com>
Date: Tue, 4 Sep 2007 18:03:11 -0500
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: "Carlo Stonebanks" <stonec.register@sympatico.ca>
Subject: Re: Performance on 8CPU's and 32GB of RAM
Cc: pgsql-performance@postgresql.org
In-Reply-To: <fbkn5d$cfq$1@news.hub.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <fbkn5d$cfq$1@news.hub.org>

On 9/4/07, Carlo Stonebanks <stonec.register@sympatico.ca> wrote:
> A client is moving their postgresql db to a brand new Windows 2003 x64
> server with 2 quad cores and 32GB of RAM. It is a dedicated server to run
> 8.2.4.

And what does the drive subsystem look like?  All that horsepower
isn't going to help if all your data is sitting on an inferior drive
subsystem.

> The server typically will have less than 10 users. The primary use of this
> server is to host a database that is continuously being updated by data
> consolidation and matching software software that hits the server very hard.
> There are typically eight such processes running at any one time. The
> software extensively exploits postgresql native fuzzy string for data
> matching. The SQL is dynamically generated by the software and consists of
> large, complex joins. (the structure of the joins change as the software
> adapts its matching strategies).
>
> I would like to favour the needs of the data matching software, and the
> server is almost exclusivly dedicated to PostgreSQL.
>
> I have made some tentative modifications to the default postgres.config file
> (see below), but I don't think I've scratched the surface of what this new
> system is capable of. Can I ask - given my client's needs and this new,
> powerful server and the fact that the server typically has a small number of
> extremely busy processes, what numbers they would change, and what the
> recommendations would be?
>
> Thanks!
>
> Carlo
>
> max_connections = 100
> shared_buffers = 100000
> work_mem = 1000000

Even with only 10 users, 1 gig work_mem is extremely high.  (without a
unit, work_mem is set in k on 8.2.x) 10000 would be much more
reasonable.

OTOH, shared_buffers, at 100000 is only setting it to 100 meg.  that's
pretty small on a machine with 32 gig.  Also, I recommend setting
values more readable, like 500MB in postgresql.conf.  Much easier to
read than 100000...

> effective_cache_size = 375000

This seems low by an order of magnitude or two.

But the most important thing is what you've left out.  What kind of
I/O does this machine have.  It's really important for something that
sounds like an OLAP server.