Re: [HACKERS] Increase default effective_cache_size?
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: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Increase default effective_cache_size?
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Increase default effective_cache_size?
Andrew Dunstan [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Increase default effective_cache_size?
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 [EMAIL PROTECTED] 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 solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Increase default effective_cache_size?
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..) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Increase default effective_cache_size?
* Tom Lane ([EMAIL PROTECTED]) wrote: Russ Brown [EMAIL PROTECTED] 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 signature.asc Description: Digital signature
