From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: pgsql-performance@postgresql.org
To: Carlo Stonebanks <stonec.register@sympatico.ca>
Subject: Re: random_page_costs - are defaults of 4.0 realistic for SCSI RAID 1
Date: Sun, 16 Sep 2007 15:00:48 -0500
Message-ID: <46ED4520.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <fc45mi$1tj9$1@news.hub.org>
References: <fc45mi$1tj9$1@news.hub.org>
>>> On Mon, Sep 10, 2007 at 2:25 PM, in message <fc45mi$1tj9$1@news.hub.org>,
"Carlo Stonebanks" <stonec.register@sympatico.ca> wrote:
> is the default setting of 4.0 realistic or could it be lower?
Wow, such a simple, innocent question.
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:
(1) Cache well below database size (for example 6 GB or 12 GB RAM on a box
running a 210 GB database):
#seq_page_cost = 1.0
random_page_cost = 2.0
(2) On a database which is entirely contained within cache:
seq_page_cost = 0.1
random_page_cost = 0.1
(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:
seq_page_cost = 0.3
random_page_cost = 0.5
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.
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.
-Kevin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, stonec.register@sympatico.ca
Subject: Re: random_page_costs - are defaults of 4.0 realistic for SCSI RAID 1
In-Reply-To: <46ED4520.EE98.0025.0@wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox