From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Ogden <lists@darkstatic.com>
To: Tomas Vondra <tv@fuzzy.cz>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance
Date: Wed, 13 Apr 2011 09:32:24 -0500
Message-ID: <4DA56DA8020000250003C783@gw.wicourts.gov> (raw)
In-Reply-To: <A0B9339E-9D58-4842-A206-050B773360B6@darkstatic.com>
References: <DF2D5436-117C-4D02-9B3C-A55723B7DDE1@darkstatic.com>
<20110412171855.GA14292@tux>
<FC3A3A2B-3ECB-41BA-8F94-356D6FED3695@darkstatic.com>
<4DA496FA.3070908@fuzzy.cz>
<8F22D592-23C1-4A3C-94A5-48363332ADD3@darkstatic.com>
<4DA4BFA2.5060601@fuzzy.cz>
<B93DFBBB-DA56-4044-A508-0B7E4A2CFD28@darkstatic.com>
<4DA4D40A.4010200@fuzzy.cz>
<A0B9339E-9D58-4842-A206-050B773360B6@darkstatic.com>
Ogden <lists@darkstatic.com> wrote:
> In conclusion, I should keep my random_page_cost (3.0) to a value
> more than seq_page_cost (1.0)? Is this bad practice or will this
> suffice for my setup (where the database is much bigger than the
> RAM in the system)?
The idea is to adjust the costing factors to model the actual
relative costs of the various actions in your environment with your
workload. The best way to determine whether your settings are good
is to gauge how happy the those using the database are with
performance. :-)
The degree of caching has a large effect on the page costs. We've
managed to keep the active portion of our databases cached to a
degree that we have always benefited by reducing the
random_page_cost to 2 or less. Where the entire database is cached,
we get the best plans with seq_page_cost and random_page_cost set to
equal values in the 0.1 to 0.05 range. We've occasionally needed to
bump the cpu_tuple_cost up a bit relative to other cpu costs, too.
On the other hand, I've seen reports of people who have found it
necessary to increase random_page_cost to get good plans. These
have been people with large databases where the entire database is
"active" (versus our databases where recent, active data is accessed
much more heavily than, say, 20 year old data).
If you model the costing to reflect the reality on your server, good
plans will be chosen.
-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, lists@darkstatic.com, tv@fuzzy.cz
Subject: Re: Performance
In-Reply-To: <4DA56DA8020000250003C783@gw.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