Re: Tuning for mid-size server

From what I know, there is a cache-row-set functionality that doesn't
exist with the newer postgres...

Concurrent users will start from 1 to a high of 5000 or more, and could
ramp up rapidly. So far, with increased users, we have gone up to
starting the JVM (resin startup) with 1024megs min and max (recommended
by Sun) - on the app side.

Thanks,
Anjan

-----Original Message-----
From: Richard Huxton [mailto:dev(at)archonet(dot)com]
Sent: Tuesday, October 21, 2003 11:57 AM
To: Anjan Dave; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Tuning for mid-size server

On Tuesday 21 October 2003 15:28, Anjan Dave wrote:
> Hi,
>
> Pretty soon, a PowerEdge 6650 with 4 x 2Ghz XEONs, and 8GB Memory,
> with internal drives on RAID5 will be delivered. Postgres will be from

> RH8.0.

You'll want to upgrade PG to v7.3.4

> I am planning for these values for the postgres configuration - to
> begin
> with:
>
> Shared_buffers (25% of RAM / 8KB)) = 8589934592 * .25 / 8192 = 262144
>
> Sort_mem (4% of RAM / 1KB) = 335544. We'll take about half of that -
> 167772
>
> Effective_cache_size = 262144 (same as shared_buffers - 25%)

My instincts would be to lower the first two substantially, and increase
the
effective cache once you know load levels. I'd probably start with
something
like the values below and work up:
shared_buffers = 8,000 - 10,000 (PG is happier letting the OS do the
cacheing) sort_mem = 4,000 - 8,000 (don't forget this is for each sort)

You'll find the annotated postgresql.conf and performance tuning
articles
useful: http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php

> In a generic sense, these are recommended values I found in some
> documents. The database will be small in size and will gradually grow
> over time from few thousands to a few million records, or more. The
> activity will be mostly of select statements from a few tables with
> joins, orderby, groupby clauses. The web application is based on
> Apache/Resin and hotspot JVM 1.4.0.

You'll need to figure out how many concurrent users you'll have and how
much
memory will be required by apache/java. If your database grows
radically,
you'll probably want to re-tune as it grows.

--
Richard Huxton
Archonet Ltd