Re: slow queries, possibly disk io

От: Simon Riggs
Тема: Re: slow queries, possibly disk io
Дата: ,
Msg-id: 1117616779.3844.903.camel@localhost.localdomain
(см: обсуждение, исходный текст)
Ответ на: Re: slow queries, possibly disk io  (Josh Close)
Список: pgsql-performance

Скрыть дерево обсуждения

slow queries, possibly disk io  (Josh Close, )
 Re: slow queries, possibly disk io  (John Arbash Meinel, )
  Re: slow queries, possibly disk io  (Josh Close, )
   Re: slow queries, possibly disk io  (Simon Riggs, )
    TIP 9: the planner will ignore... & datatypes  ("Marc Mamin", )
     Re: TIP 9: the planner will ignore... & datatypes  (Alvaro Herrera, )
 Re: slow queries, possibly disk io  (Dawid Kuroczko, )
 Re: slow queries, possibly disk io  (Christopher Kings-Lynne, )
  Re: slow queries, possibly disk io  (Josh Close, )
 slow queries, possibly disk io  (Josh Close, )
  Re: slow queries, possibly disk io  (Tom Lane, )
   Re: slow queries, possibly disk io  (Josh Close, )
   Re: slow queries, possibly disk io  (Josh Close, )
   Re: slow queries, possibly disk io  (Rudi Starcevic, )
    Re: slow queries, possibly disk io  (Josh Close, )
     Re: slow queries, possibly disk io  (Tom Lane, )
     Re: slow queries, possibly disk io  (Josh Close, )
      Re: slow queries, possibly disk io  (Manfred Koizar, )

On Fri, 2005-05-27 at 07:52 -0500, Josh Close wrote:
> > Setting shared buffers above something like 10-30% of memory is counter
> > productive.
>
> What is the reason behind it being counter productive? If shared
> buffers are at 30%, should effective cache size be at 70%? How do
> those two relate?

They don't relate.

shared_buffers = 50000 is enough. More than that will give bgwriter
issues.

effective_cache_size changes whether indexes are selected or not. Higher
settings favour indexed access.

> >
> > Increasing sort_mem can help with various activities, but increasing it
> > too much can cause you to swap, which kills performance. The caution is
> > that you will likely use at least 1 sort_mem per connection, and can
> > likely use more than one if the query is complicated.
>
> I have a max of 100 connections and 2 gigs of mem. Right now the sort
> mem is a 4 megs. How much higher could I put that?
>

Please post your server hardware config all in one go. You have more
than 2 CPUs, yes?

Also, mention what bgwriter settings are. You may need to turn those
down a bit.

Best Regards, Simon Riggs



В списке pgsql-performance по дате сообщения:

От: Tom Lane
Дата:
Сообщение: Re: How to avoid database bloat
От: "Keith Worthington"
Дата:
Сообщение: Moving pg_xlog