Date: Tue, 17 Jul 2007 10:48:00 -0500
From: "Jim C. Nasby" <decibel@decibel.org>
To: Marc Mamin <M.Mamin@intershop.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres configuration for 64 CPUs, 128 GB RAM...
Message-ID: <20070717154759.GB39272@nasby.net>
References: 
 <CA896D7906BF224F8A6D74A1B7E54AB301750B12@JENMAIL01.ad.intershop.net>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="Vx/N56bIaYnO6ICL"
Content-Disposition: inline
In-Reply-To: 
 <CA896D7906BF224F8A6D74A1B7E54AB301750B12@JENMAIL01.ad.intershop.net>
User-Agent: Mutt/1.5.13 (2006-08-11)


--Vx/N56bIaYnO6ICL
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Tue, Jul 17, 2007 at 04:10:30PM +0200, Marc Mamin wrote:
> shared_buffers=3D 262143
=20
You should at least try some runs with this set far, far larger. At
least 10% of memory, but it'd be nice to see what happens with this set
to 50% or higher as well (though don't set it larger than the database
since it'd be a waste).

How big is the database, anyway?

> # on our current best production server with 4GB RAM (not dedicated to
> Postgres), work_mem is set to 600 MB
> # this limitation is probably the bottleneck for our application as the
> files in pgsql_tmp grows up to 15 GB=20
> # during large aggregations (we have a locking mechanismus to avoid
> parallel processing of such transactions)

Keep in mind that a good filesystem will be caching most of pgsql_tmp if
it can.

> max_prepared_transaction =3D 100
=20
Are you using 2PC? If not, there's no reason to touch this (could could
just set it to 0).

> # I use the default for the bgwriter as I couldnt find recommendation on
> those
>=20
> #bgwriter_delay =3D 200ms                 # 10-10000ms between rounds
> #bgwriter_lru_percent =3D 1.0             # 0-100% of LRU buffers
> scanned/round
> #bgwriter_lru_maxpages =3D 5              # 0-1000 buffers max
> written/round
> #bgwriter_all_percent =3D 0.333           # 0-100% of all buffers
> scanned/round
> #bgwriter_all_maxpages =3D 5              # 0-1000 buffers max
> written/round
=20
You'll probably want to increase both maxpages parameters substantially,
assuming that you've got good IO hardware.
=20
> #CHECKPOINT
>=20
> # xlog will be  on a separate disk
> checkpoint_segments=3D256
>=20
> checkpoint_timeout =3D 5min

The further apart your checkpoints, the better. Might want to look at 10
minutes. I'd also set checkpoint_warning to just a bit below
checkpoint_timeout and watch for warnings to make sure you're not
checkpointing a lot more frequently than you're expecting.
--=20
Jim Nasby                                      decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

--Vx/N56bIaYnO6ICL
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.3 (FreeBSD)

iD8DBQFGnOSvdO30qud8SkgRAgWMAKDDCmGkA6yGLrCGrOBzAQ/D4d+XYACgnAIO
AMtiwm37KahfaT8uSMWfc+M=
=UpB2
-----END PGP SIGNATURE-----

--Vx/N56bIaYnO6ICL--