From: Scott Marlowe <scott.marlowe@gmail.com>
To: Carlo Stonebanks <stonec.register@sympatico.ca>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance on 8CPU's and 32GB of RAM
Date: Tue, 4 Sep 2007 18:03:11 -0500
Message-ID: <dcc563d10709041603m3f894053ve02d8de15722ffcd@mail.gmail.com> (raw)
In-Reply-To: <fbkn5d$cfq$1@news.hub.org>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scott.marlowe@gmail.com, stonec.register@sympatico.ca
Subject: Re: Performance on 8CPU's and 32GB of RAM
In-Reply-To: <dcc563d10709041603m3f894053ve02d8de15722ffcd@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox