Message-Id: <46ED4520.EE98.0025.0@wicourts.gov>
Date: Sun, 16 Sep 2007 15:00:48 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: <pgsql-performance@postgresql.org>,
	"Carlo Stonebanks" <stonec.register@sympatico.ca>
Subject: Re: random_page_costs - are defaults of 4.0 realistic for SCSI RAID 1
References: <fc45mi$1tj9$1@news.hub.org>
In-Reply-To: <fc45mi$1tj9$1@news.hub.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

>>> On Mon, Sep 10, 2007 at  2:25 PM, in message <fc45mi$1tj9$1@news.hub.or=
g>,
"Carlo Stonebanks" <stonec.register@sympatico.ca> wrote:=20
=20
> is the default setting of 4.0 realistic or could it be lower?
=20
Wow, such a simple, innocent question.
=20
As you may have inferred, it can't be answered in isolation.  Make sure =
that
you have reviewed all of your memory settings, then try adjusting this and
seeing what the results are.  With accurate effective_cache_size and a =
fairly
generous work_mem setting, we have found that these settings work best for =
us
with our actual production loads:
=20
(1)  Cache well below database size (for example 6 GB or 12 GB RAM on a =
box
running a 210 GB database):
=20
#seq_page_cost =3D 1.0
random_page_cost =3D 2.0
=20
(2)  On a database which is entirely contained within cache:
=20
seq_page_cost =3D 0.1
random_page_cost =3D 0.1
=20
(3)  Where caching is very significant, but not complete, we have to test
to see where performance is best.  One example that significantly beat =
both
of the above in production on a particular box:
=20
seq_page_cost =3D 0.3
random_page_cost =3D 0.5
=20
So, the short answer to your question is that the default might be =
realistic
in some environments; the best choice will be lower in many environments;
the best choice will be higher in some environments; only testing your
actual applications in your actual environment can tell you which is the
case for you.
=20
My approach is to pick one of the first two, depending on whether the
database will be fully cached, then monitor for performance problems.  =
When
the queries with unacceptable response time have been identified, I look
for ways to improve them.  One of the things I may try, where a bad plan
seems to have been chosen, is to adjust the random page cost.  If I do
change that in production, then I closely monitor for regression in other
queries.
=20
-Kevin
=20