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: Tom Lane on 14 Apr 2010 18:20 Robert Haas <robertmhaas(a)gmail.com> writes: > 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. Looks good to me too, although perhaps more than the single use of "dedicated" is needed to remind people that these numbers are only appropriate if the machine is not doing anything else than running (one instance of) Postgres. Should we expend a whole sentence on that? regards, tom lane -- 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 18:53 On Wed, Apr 14, 2010 at 6:20 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas(a)gmail.com> writes: >> 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. > > Looks good to me too, although perhaps more than the single use of > "dedicated" is needed to remind people that these numbers are only > appropriate if the machine is not doing anything else than running > (one instance of) Postgres. Should we expend a whole sentence > on that? IMHO that would be overkill, but that's just MHO. Other opinions? ....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: Robert Haas on 16 Apr 2010 17:56 On Wed, Apr 14, 2010 at 4:18 PM, Greg Smith <greg(a)2ndquadrant.com> wrote: > 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. I've incorporated most of this text, with some further editing, into the documentation. I'm halfway tempted to backpatch it to 8.4 and maybe even 8.3, but have refrained from so doing for now. From reading this and other threads, I think I generally understand that the perils of setting shared_buffers too high: memory is needed for other things, like work_mem, a problem which is exacerbated by the fact that there is some double buffering going on. Also, if the buffer cache gets too large, checkpoints can involve writing out enormous amounts of dirty data, which can be bad. It seems intuitive to me that setting shared_buffers too small will also cause a performance problem, especially for write-heavy workloads, but I'm less sure I can clearly explain why. And I'm curious why the correct setting is different on Windows than it is on other platforms. Can anyone shed some light on this? ....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: Greg Smith on 16 Apr 2010 19:24 Robert Haas wrote: > It seems intuitive to me that setting shared_buffers too small will > also cause a performance problem, especially for write-heavy > workloads, but I'm less sure I can clearly explain why. More text to add: When the server needs to allocate more space for reading or writing blocks, and the next available space available is a block that's been modified but not used recently, that block will be written out to the operating system. With large settings for shared_buffers, that prefers evicting blocks that are used infrequently from the cache. The main downside to tuning in that direction is that all recently modified blocks not already written must be flushed to disk during each checkpoint, which can cause large amounts of disk writes grouped together. But if shared_buffers is set too low instead, and therefore only a portion of the active working set can be kept in the buffer cache at once, that can cause the same block to be written out more frequently than is optimal. > And I'm curious why the correct setting is different on Windows than it is on > other platforms. Can anyone shed some light on this? > No one has ever come up with a good explanation for why this is other than "Windows doesn't seem to like large amounts of shared memory". But we've seen it show up in too many benchmarks to dismiss. Dave and Greg Stark did benchmarks focused on this: http://archives.postgresql.org/pgsql-hackers/2008-12/msg00003.php that Magnus concurred with last time I tried to dig for more info about this specific subject. And the last time I remember this caming up it was with someone who suggested 8MB (!) worked best on their Windows system: http://archives.postgresql.org/pgsql-general/2009-12/msg00475.php -- 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 16 Apr 2010 20:37
On Fri, Apr 16, 2010 at 7:24 PM, Greg Smith <greg(a)2ndquadrant.com> wrote: > Robert Haas wrote: >> It seems intuitive to me that setting shared_buffers too small will >> also cause a performance problem, especially for write-heavy >> workloads, but I'm less sure I can clearly explain why. > > More text to add: > > When the server needs to allocate more space for reading or writing blocks, > and the next available space available is a block that's been modified but > not used recently, that block will be written out to the operating system. > With large settings for shared_buffers, that prefers evicting blocks that > are used infrequently from the cache. The main downside to tuning in that > direction is that all recently modified blocks not already written must be > flushed to disk during each checkpoint, which can cause large amounts of > disk writes grouped together. But if shared_buffers is set too low instead, > and therefore only a portion of the active working set can be kept in the > buffer cache at once, that can cause the same block to be written out more > frequently than is optimal. Well, why can't they just hang out as dirty buffers in the OS cache, which is also designed to solve this problem? >> And I'm curious why the correct setting is different on Windows than it is >> on >> other platforms. Can anyone shed some light on this? >> > > No one has ever come up with a good explanation for why this is other than > "Windows doesn't seem to like large amounts of shared memory". But we've > seen it show up in too many benchmarks to dismiss. Dave and Greg Stark > did benchmarks focused on this: > http://archives.postgresql.org/pgsql-hackers/2008-12/msg00003.php that > Magnus concurred with last time I tried to dig for more info about this > specific subject. And the last time I remember this caming up it was with > someone who suggested 8MB (!) worked best on their Windows system: > http://archives.postgresql.org/pgsql-general/2009-12/msg00475.php I guess the obvious question is whether Windows "doesn't need" more shared memory than that, or whether it "can't effectively use" more memory than that. ....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 |