Обсуждение: Increase default effective_cache_size?
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
>> 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/
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......)
* 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
> 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/
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
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