Re: Problem with database performance, Debian 4gb ram ?

Поиск
Список
Период
Сортировка
От Kevin Grittner
Тема Re: Problem with database performance, Debian 4gb ram ?
Дата
Msg-id 4AF019E6020000250002C1D2@gw.wicourts.gov
обсуждение исходный текст
Ответ на Re: Problem with database performance, Debian 4gb ram ?  (Jeff Janes <jeff.janes@gmail.com>)
Список pgsql-performance
Jeff Janes <jeff.janes@gmail.com> wrote:
> On Tue, Nov 3, 2009 at 7:13 AM, Kevin Grittner
> <Kevin.Grittner@wicourts.gov> wrote:
>> Grant Masan <grant.massan@gmail.com> wrote:
>>
>>
>>> cpu_tuple_cost = 0.0030
>>> cpu_index_tuple_cost = 0.0010
>>> cpu_operator_cost = 0.0005
>>
>> Why did you make these adjustments?  I usually have to change the
>> ratio between page and cpu costs toward the other direction.
>
> Is that because the database is mostly cached in memory?  If I take
> the documented descriptions of the costs parameters at face value, I
> find that cpu_tuple_cost should be even lower yet.

Right, the optimizer doesn't model caching effects very well, so I
find that in practice I have to fudge these from their putative
meanings to allow for typical caching.  Even with only a small
fraction of the database cached, the heavily accessed indexes tend to
be fairly well cached, so overall performance improves markedly by
dropping random_page_cost to about 2, even in our lowest-percentage-
cached databases.

I've occasionally tried using the defaults for that GUC, which has
always resulted in user complaints about unacceptable performance of
important queries.  While I tend to reduce the random_page_cost and
seq_page_cost to tweak things, raising the cpu_*_cost settings would
accomplish the same thing, so reducing them as show above would tend
to push things into sequential scans where indexed access might be
faster.

-Kevin

В списке pgsql-performance по дате отправления:

Предыдущее
От: David Kerr
Дата:
Сообщение: Optimizer + bind variables
Следующее
От: Jesper Krogh
Дата:
Сообщение: Re: Queryplan within FTS/GIN index -search.