Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 1B6809FA435 for ; Mon, 4 Dec 2006 15:23:07 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 61578-03 for ; Mon, 4 Dec 2006 15:22:59 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from exchange.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by postgresql.org (Postfix) with ESMTP id 5793F9FA192 for ; Mon, 4 Dec 2006 15:22:59 -0400 (AST) Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com ([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ; Mon, 4 Dec 2006 19:22:56 +0000 Received: from state.g2switchworks.com by mail.g2switchworks.com; 04 Dec 2006 13:23:04 -0600 Subject: Re: Configuration settings for 32GB RAM server From: Scott Marlowe To: Joshua Marsh Cc: Mark Lonsdale , pgsql-performance@postgresql.org In-Reply-To: <38242de90612041057j63672f7qafa6c6059e9901dc@mail.gmail.com> References: <673ECBB77DA91F47AD4902A5FD73BCE4D5ED15@mail3> <38242de90612041057j63672f7qafa6c6059e9901dc@mail.gmail.com> Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Message-Id: <1165260184.14565.349.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Mon, 04 Dec 2006 13:23:04 -0600 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/61 X-Sequence-Number: 22117 On Mon, 2006-12-04 at 12:57, Joshua Marsh wrote: >=20 > On 12/4/06, Mark Lonsdale wrote: > Maintenance_work_mem =3D 1048576 =E2=80=93 Figured Id allocate 1G= B for > this. > =20 > =20 >=20 > Do you know how often and when you will be creating indexes or > clustering? We set ours to 2GB because of the performance gains.=20 > We've also thought about testing it at 4GB. We can do this because we > know during the middle of the night our server load drops to nearly > zero. If you know you have windows like that, then I would definitely > suggest increasing your maintenance_work_mem. It's halved the time > for io intesive tasks like cluster.=20 >=20 Also, remember that most of those settings (work_mem, maintenance_work_mem) can be changed for an individual session. So, you can leave work_mem at something conservative, like 8 meg, and for a session that is going to run at 2am and iterate over billions of rows, you can throw several gigabytes at it and not worry about that one setting blowing out all the other processes on the machine.