Обсуждение: Increase default effective_cache_size?

Поиск
Список
Период
Сортировка

Increase default effective_cache_size?

От
Tom Lane
Дата:
Russ Brown <pickscrape@gmail.com> writes on pgsql-general:
> On Thu, 2006-09-21 at 23:39 -0400, Jim Nasby wrote:
>> Also make sure that you've set effective_cache_size  
>> correctly (I generally set it to total memory - 1G, assuming the  
>> server has at least 4G in it).

> Thank you: the problem was the effective_cache_size (which I hadn't
> changed from the default of 1000). This machine doesn't have loads of
> RAM, but I knocked it up to 65536 and now the query uses the index,
> without having to change the statistics.

Considering recent discussion about how 8.2 is probably noticeably more
sensitive to effective_cache_size than prior releases, I wonder whether
it's not time to adopt a larger default value for that setting.  The
current default of 1000 pages (8Mb) seems really pretty silly for modern
machines; we could certainly set it to 10 times that without problems,
and maybe much more.  Thoughts?
        regards, tom lane


Re: Increase default effective_cache_size?

От
"Joshua D. Drake"
Дата:
>> Thank you: the problem was the effective_cache_size (which I hadn't
>> changed from the default of 1000). This machine doesn't have loads of
>> RAM, but I knocked it up to 65536 and now the query uses the index,
>> without having to change the statistics.
> 
> Considering recent discussion about how 8.2 is probably noticeably more
> sensitive to effective_cache_size than prior releases, I wonder whether
> it's not time to adopt a larger default value for that setting.  The
> current default of 1000 pages (8Mb) seems really pretty silly for modern
> machines; we could certainly set it to 10 times that without problems,
> and maybe much more.  Thoughts?

I think that 128 megs is probably a reasonable starting point. I know 
plenty of people that run postgresql on 512 megs of ram. If you take 
into account shared buffers and work mem, that seems like a reasonable 
starting point.

Joshua D. Drake
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
> 


-- 
   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240   Providing the most comprehensive  PostgreSQL
solutionssince 1997             http://www.commandprompt.com/
 




Re: Increase default effective_cache_size?

От
Gevik Babakhani
Дата:
On Sat, 2006-09-23 at 17:14 -0700, Joshua D. Drake wrote:
> >> Thank you: the problem was the effective_cache_size (which I hadn't
> >> changed from the default of 1000). This machine doesn't have loads of
> >> RAM, but I knocked it up to 65536 and now the query uses the index,
> >> without having to change the statistics.
> > 
> > Considering recent discussion about how 8.2 is probably noticeably more
> > sensitive to effective_cache_size than prior releases, I wonder whether
> > it's not time to adopt a larger default value for that setting.  The
> > current default of 1000 pages (8Mb) seems really pretty silly for modern
> > machines; we could certainly set it to 10 times that without problems,
> > and maybe much more.  Thoughts?
> 
> I think that 128 megs is probably a reasonable starting point. I know 
> plenty of people that run postgresql on 512 megs of ram. If you take 
> into account shared buffers and work mem, that seems like a reasonable 
> starting point.
> 

I agree, Adopting a higher effective_cache_size seems to be a good thing
to do. 


(hmmm.... I must be dreaming again.... But I cannot stop wondering how
it would be to have a smart "agent" that configures these values by
analyzing the machine power and statistical values gathered from
database usage......)  



Re: Increase default effective_cache_size?

От
Stephen Frost
Дата:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Russ Brown <pickscrape@gmail.com> writes on pgsql-general:
> > Thank you: the problem was the effective_cache_size (which I hadn't
> > changed from the default of 1000). This machine doesn't have loads of
> > RAM, but I knocked it up to 65536 and now the query uses the index,
> > without having to change the statistics.
>
> Considering recent discussion about how 8.2 is probably noticeably more
> sensitive to effective_cache_size than prior releases, I wonder whether
> it's not time to adopt a larger default value for that setting.  The
> current default of 1000 pages (8Mb) seems really pretty silly for modern
> machines; we could certainly set it to 10 times that without problems,
> and maybe much more.  Thoughts?

I'd have to agree 100% with this.  Though don't we now have something
automated for shared_buffers?  I'd think effective_cache_size would
definitely be a candidate for automation (say, half or 1/4th the ram in
the box...).

Barring the ability to do something along those lines- yes, I'd
recommend up'ing it to at least 128M or 256M.
Thanks,
    Stephen

Re: Increase default effective_cache_size?

От
Teodor Sigaev
Дата:
> current default of 1000 pages (8Mb) seems really pretty silly for modern
> machines; we could certainly set it to 10 times that without problems,
> and maybe much more.  Thoughts?

May be, set by default effective_cache_size equal to number of shared buffers?
If pgsql is configured to  use quarter  or half of total memory for shared 
buffer, then effective_cache_size will have good approximation...



-- 
Teodor Sigaev                                   E-mail: teodor@sigaev.ru
  WWW: http://www.sigaev.ru/
 


Re: Increase default effective_cache_size?

От
Andrew Dunstan
Дата:

Teodor Sigaev wrote:
>> current default of 1000 pages (8Mb) seems really pretty silly for modern
>> machines; we could certainly set it to 10 times that without problems,
>> and maybe much more.  Thoughts?
>
> May be, set by default effective_cache_size equal to number of shared 
> buffers?
> If pgsql is configured to  use quarter  or half of total memory for 
> shared buffer, then effective_cache_size will have good approximation...
>
>


Initdb does not currently make any attempt to discover the extent of 
physical or virtual memory, it simply tries to start postgres with 
certain shared_buffer settings, starting at 4000, and going down until 
we get a success.

max_fsm_pages is now fixed proportionally with shared_buffers, and I 
guess we could do something similar with effective_cache_size, but since 
IIRC this doesn't involve shared memory I'm inclined to agree with Tom 
that it should just be fixed at some substantially higher level.

cheers

andrew




Re: Increase default effective_cache_size?

От
Tom Lane
Дата:
Andrew Dunstan <andrew@dunslane.net> writes:
> Initdb does not currently make any attempt to discover the extent of 
> physical or virtual memory, it simply tries to start postgres with 
> certain shared_buffer settings, starting at 4000, and going down until 
> we get a success.

> max_fsm_pages is now fixed proportionally with shared_buffers, and I 
> guess we could do something similar with effective_cache_size, but since 
> IIRC this doesn't involve shared memory I'm inclined to agree with Tom 
> that it should just be fixed at some substantially higher level.

Right, the default shared_buffers doesn't have much of anything to do
with actual RAM size.  If the user has altered it, then it might (or
might not) ... but that doesn't help us for setting a default
effective_cache_size.

Barring objections, I'll change it to Josh Drake's suggestion of ~ 128Mb
(versus current 8Mb).
        regards, tom lane