shared_buffers documentation
in [PgSql]
|
Prev: [HACKERS] standbycheck was:(Re: [HACKERS] testing hot standby
Next: [BUGS] BUG #5412: test case produced, possible race condition.
From: "Kevin Grittner" on 14 Apr 2010 11:15 Robert Haas <robertmhaas(a)gmail.com> wrote: > I think this advice is badly outdated. Yeah. > s/tens/hundreds/ might be a good idea at a minimum, +1 > but I'm thinking we might want to also mention the > one-quarter-of-system-memory heuristic. Given how many people seem to find that a good guideline, it seems like we should. I wonder if we should add any hints telling people what they might see as problems if they are too far one way or the other. (Or does that go beyond the scope of what makes sense in TFM?) -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 14 Apr 2010 13:36 On Wed, Apr 14, 2010 at 11:15 AM, Kevin Grittner <Kevin.Grittner(a)wicourts.gov> wrote: > Robert Haas <robertmhaas(a)gmail.com> wrote: > >> I think this advice is badly outdated. > > Yeah. > >> s/tens/hundreds/ might be a good idea at a minimum, > > +1 > >> but I'm thinking we might want to also mention the >> one-quarter-of-system-memory heuristic. > > Given how many people seem to find that a good guideline, it seems > like we should. I wonder if we should add any hints telling people > what they might see as problems if they are too far one way or the > other. (Or does that go beyond the scope of what makes sense in > TFM?) No, I think that would be reasonable provided someone can come up with some appropriate wording. My understanding is that if you have a really small system then you might need >25% and if you have a really big system you might need <25%, but I'm not sure where the edges are. ....Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: "Kevin Grittner" on 14 Apr 2010 14:04 Robert Haas <robertmhaas(a)gmail.com> wrote: > I think that would be reasonable provided someone can come up > with some appropriate wording. My understanding is that if you > have a really small system then you might need >25% and if you > have a really big system you might need <25%, but I'm not sure > where the edges are. Yeah, I remember a study which showed 40% as optimal, but I think that was on a server with 2GB RAM, which is smaller than my desktop workstation. (Heck, digital cameras with that much aren't that rare.) We might want to advise that if there are periods of irregular response time, particularly if they are related to the checkpoint cycle, if adjustments to the checkpoint and background writer settings don't completely resolve it, they might want to try reducing shared_buffers. The improvements to the checkpoint and background writer areas in 8.3 helped a lot with this issue, but I don't believe it's been totally eliminated (yet). Perhaps that's now infrequent enough that it's not necessary to mention it. Dunno. I guess I'd be interested to hear Greg Smith weigh in on this one. I've generally stopped tweaking when our web support folks say I've got it to the point where we're not getting any timeouts against our 20 second limit for queries which normally run in less than 1 ms. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Greg Smith on 14 Apr 2010 16:18 Kevin Grittner wrote: > I wonder if we should add any hints telling people > what they might see as problems if they are too far one way or the > other. (Or does that go beyond the scope of what makes sense in TFM?) > It's hard to figure that out. One of the talks I'm doing at PGCon next month is focusing on how to monitor things when increasing shared_buffers and the related checkpoint parameters, so that you don't make things worse. It's going to take a solid 45 minutes to cover that, and a section of the manual covering this bit of trivial would be a few pages long and hard to follow. Maybe I'll get that in shape to insert into TFM eventually, but it's a bit bleeding edge to put into there now. Trying to explain it live to other people a couple of times should make it clearer how to describe what I do. As for updating the size recommendations, the text at http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server has been beaten into the status quo by a number of people. Here's what might make sense from there to insert into the docs, removing the bits referring to older versions, rewriting a bit for manual tone, and noting the checkpoint issues: If you have a system with 1GB or more of RAM, a reasonable starting value for shared_buffers on a dedicated database server is 25% of the memory in your system. If you have less RAM, you'll have to account more carefully for how much memory the operating system is taking up, allocating a fraction of the free memory instead. There are some workloads where even larger settings for shared_buffers are effective. But given the way PostgreSQL also relies on the operating system cache, it's unlikely you'll find using more than 40% of RAM to work better than a smaller amount. On Windows, large values for shared_buffers aren't as effective. You may find better results keeping the setting relatively low and using the OS cache more instead. The useful size range for shared_buffers on Windows systems is generally from 64MB to 512MB of RAM. Larger settings for shared_buffers usually require a corresponding increase in checkpoint_segments, in order to spread out writing large quantities of changed or new data in the cache over a longer period of time. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg(a)2ndQuadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 14 Apr 2010 16:23
On Wed, Apr 14, 2010 at 4:18 PM, Greg Smith <greg(a)2ndquadrant.com> wrote: > As for updating the size recommendations, the text at > http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server has been > beaten into the status quo by a number of people. Here's what might make > sense from there to insert into the docs, removing the bits referring to > older versions, rewriting a bit for manual tone, and noting the checkpoint > issues: This is good text. I will incorporate it with slight copy editing if no one objects. ....Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |