Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Re: Volunteer to build a configuration tool
Date: Thu, 21 Jun 2007 11:32:22 -0500
Message-ID: <A3AC4FA47DC0B1458C3E5396E685E63302395E74@SAB-DC1.sab.uiuc.edu>
In-Reply-To: <Pine.GSO.4.64.0706210244340.29867@westnet.com>
Thread-Topic: [PERFORM] Volunteer to build a configuration tool
thread-index: Acez1Ewn7SDhE36rTQOTT+gzdXycrAATK68g
References: <A3AC4FA47DC0B1458C3E5396E685E63302395E53@SAB-DC1.sab.uiuc.edu>
	<1182298719.4482.133.camel@ipso.snappymail.ca>
	<Pine.GSO.4.64.0706200040001.7594@westnet.com>
	<7621.1182318230@sss.pgh.pa.us>
	<Pine.GSO.4.64.0706200224430.16657@westnet.com>
	<9001.1182323187@sss.pgh.pa.us> <op.tt7mn0m9cigqcu@apollo13>
	<Pine.GSO.4.64.0706201044100.9089@westnet.com>
	<A3AC4FA47DC0B1458C3E5396E685E63302395E6C@SAB-DC1.sab.uiuc.edu>
	<Pine.GSO.4.64.0706210244340.29867@westnet.com>
From: "Campbell, Lance" <lance@uiuc.edu>
To: "Greg Smith" <gsmith@gregsmith.com>,
	<pgsql-performance@postgresql.org>

Greg,
I have a PostgreSQL database that runs on a dedicated server.  The
server has 24Gig of memory.  What would be the max size I would ever
want to set the shared_buffers to if I where to relying on the OS for
disk caching approach?  It seems that no matter how big your dedicated
server is there would be a top limit to the size of shared_buffers.
=20
Thanks,

Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu
=20
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Greg Smith
Sent: Thursday, June 21, 2007 2:15 AM
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Volunteer to build a configuration tool

On Wed, 20 Jun 2007, Campbell, Lance wrote:

> If everything I said is correct then I agree "Why have=20
> effective_cache_size?"  Why not just go down the approach that Oracle=20
> has taken and require people to rely more on shared_buffers and the=20
> general memory driven approach?  Why rely on the disk caching of the
OS?

First off, it may help explain the dynamics here if you know that until=20
fairly recent releases, the PostgreSQL shared_buffers cache had some=20
performance issues that made it impractical to make it too large.  It=20
hasn't been that long that relying more heavily on the Postgres cache
was=20
technically feasible.  I think the user community at large is still=20
assimilating all the implications of that shift, and as such some of the

territory with making the Postgres memory really large is still being=20
mapped out.

There are also still some issues left in that area.  For example, the=20
bigger your shared_buffers cache is, the worse the potential is for
having=20
a checkpoint take a really long time and disrupt operations.  There are
OS=20
tunables that can help work around that issue; similar ones for the=20
PostgreSQL buffer cache won't be available until the 8.3 release.

In addition to all that, there are still several reasons to keep relying

on the OS cache:

1) The OS cache memory is shared with other applications, so relying on
it=20
lowers the average memory footprint of PostgreSQL.  The database doesn't

have to be a pig that constantly eats all the memory up, while still=20
utilizing it when necessary.

2) The OS knows a lot more about the disk layout and similar low-level=20
details and can do optimizations a platform-independant program like=20
Postgres can't assume are available.

3) There are more people working on optimizing the caching algorithms in

modern operating systems than are coding on this project.  Using that=20
sophisticated cache leverages their work.

"The Oracle Way" presumes that you've got such a massive development
staff=20
that you can solve these problems better yourself than the community at=20
large, and then support that solution on every platform.  This is why
they=20
ended up with solutions like raw partitions, where they just put their
own=20
filesystem on the disk and figure out how to make that work well=20
everywhere.  If you look at trends in this area, at this point the=20
underlying operating systems have gotten good enough that tricks like
that=20
are becoming marginal.  Pushing more work toward the OS is a completely=20
viable design choice that strengthens every year.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend