Re: Performance on 8CPU's and 32GB of RAM

On 9/4/07, Carlo Stonebanks <stonec(dot)register(at)sympatico(dot)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.