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 19 Apr 2010 18:35 Robert Haas <robertmhaas(a)gmail.com> wrote: >> However, this is the first time I am hearing that >> battery-backed cache favors the default value. Well, it was discussed on the lists during a CommitFest. > ...if that's as bad as it gets, I'm still not sure we shouldn't > increase the default. Most people will not have their first > experience of PG on a server with a battery-backed RAID > controller, I'm thinking. And people who do have battery-backed > RAID controllers can tune the value down if need be. I have never > yet heard anyone justify why all the values in postgresql.conf > should be defined as "the lowest value that works best for at > least 1 user". > > Then again, I don't really know what I'm talking about. I think > we should be listening very carefully to people who have spent a > lot of time tuning this and taking their advice on how it should > be set by default. I'm not sure we shouldn't change the default either. There seems to be a wealth of experience showing where a bigger value can help, and a fairly narrow use case where (much to my surprise) the lower value helped. Perhaps this just fits under the "be sure to test and tune your own environment" heading, although it is a direction people might not even think to try without some hint that it can help. FWIW, we use very different configurations for bulk loading (like pg_dump piped to psql) than we do for production usage afterward. This has become part of my bag of tricks for bulk loads, but we still use a larger number after the load. Also, I haven't heard of any independent confirmation -- it could be a quirk of our hardware and configuration? Has anyone else benchmarked this to see the impact on bulk loads with BBU cache? -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 19 Apr 2010 18:36 Kevin Grittner wrote: > Perhaps, but be aware the current default benchmarked better > than a larger setting in bulk loads. > > http://archives.postgresql.org/pgsql-hackers/2009-06/msg01382.php > > The apparent reason is that when there were fewer of them the WAL > files were re-used before the RAID controller flushed them from BBU > cache, causing an overall reduction in disk writes. I have little > doubt that *without* a good BBU cached controller a larger setting > is better, but it's not universally true that bigger is better on > this one After running some tests, I believe what you observed is more universal than that, because I've been able to replicate a performance drop from a checkpoint_segments increase on a system without a BBWC (laptop with write caching turned off) where I really expected it to help. My working theory is that are a broader set of situations where limiting the working set of WAL files to a small number in order to decrease cache disruption applies than just when you've got hardware caching involved. However, I believe the guidelines to increasing this parameter along with shared_buffers still applies. The real case for wins with more segments is when you also have a large buffer cache, because that's where the write savings from postponed database writes to often used blocks becomes easy to measure. I've found it difficult today to demonstrate a slam-dunk bulk loading improvement through checkpoint_segments increase when shared_buffers is fixed at its default of ~32MB. If that keeps up, I might soon have enough data to bust the idea that it alone improves bulk loading performance when you haven't touched anything else in the default config, which was unexpected to me. Will report back once I've got a full handle on it. Thanks for reminding me about this counter example, it slipped by in that broader thread before and I didn't try doing that myself until today, to see that you're onto something there. -- 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: Bruce Momjian on 19 Apr 2010 21:23 Robert Haas wrote: > > Well, the point is that you are getting it for _unusual_ circumstances. > > Seems it is only when you are getting it for typical workloads that it > > should be increased. > > I guess. I am not sure we should consider "doing a large CTAS" to be > an unusual workload, though. Sure, most of us don't do that every > day, but what do we get out of having it be slow when we do decide to > do it? Up until today, I had never heard anyone say that there was > any possible performance trade-off, and... > > > However, this is the first time I am hearing that > > battery-backed cache favors the default value. > > ...if that's as bad as it gets, I'm still not sure we shouldn't > increase the default. Most people will not have their first > experience of PG on a server with a battery-backed RAID controller, > I'm thinking. And people who do have battery-backed RAID controllers > can tune the value down if need be. I have never yet heard anyone > justify why all the values in postgresql.conf should be defined as > "the lowest value that works best for at least 1 user". > > Then again, I don't really know what I'm talking about. I think we > should be listening very carefully to people who have spent a lot of > time tuning this and taking their advice on how it should be set by > default. The current default was just chosen to reduce the PG disk footprint. It probably should be increased, unless we find that the smaller working set is a win in many cases. -- Bruce Momjian <bruce(a)momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com -- 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 19 Apr 2010 21:28 On Mon, Apr 19, 2010 at 9:23 PM, Bruce Momjian <bruce(a)momjian.us> wrote: > Robert Haas wrote: >> > Well, the point is that you are getting it for _unusual_ circumstances. >> > Seems it is only when you are getting it for typical workloads that it >> > should be increased. >> >> I guess. I am not sure we should consider "doing a large CTAS" to be >> an unusual workload, though. Sure, most of us don't do that every >> day, but what do we get out of having it be slow when we do decide to >> do it? Up until today, I had never heard anyone say that there was >> any possible performance trade-off, and... >> >> > However, this is the first time I am hearing that >> > battery-backed cache favors the default value. >> >> ...if that's as bad as it gets, I'm still not sure we shouldn't >> increase the default. Most people will not have their first >> experience of PG on a server with a battery-backed RAID controller, >> I'm thinking. And people who do have battery-backed RAID controllers >> can tune the value down if need be. I have never yet heard anyone >> justify why all the values in postgresql.conf should be defined as >> "the lowest value that works best for at least 1 user". >> >> Then again, I don't really know what I'm talking about. I think we >> should be listening very carefully to people who have spent a lot of >> time tuning this and taking their advice on how it should be set by >> default. > > The current default was just chosen to reduce the PG disk footprint. It > probably should be increased, unless we find that the smaller working > set is a win in many cases. Yeah. 48MB is not much these days. ....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 21 Apr 2010 02:54
Jim Nasby wrote: > I've also seen large shared buffer settings perform poorly outside of IO issues, presumably due to some kind of internal lock contention. I tried running 8.3 with 24G for a while, but dropped it back down to our default of 8G after noticing some performance problems. Unfortunately I don't remember the exact details, let alone having a repeatable test case We got a report for Jignesh at Sun once that he had a benchmark workload where there was a clear performance wall at around 10GB of shared_buffers. At http://blogs.sun.com/jkshah/entry/postgresql_east_2008_talk_best he says: "Shared Bufferpool getting better in 8.2, worth to increase it to 3GB (for 32-bit PostgreSQL) but still not great to increase it more than 10GB (for 64-bit PostgreSQL)" So you running into the same wall around the same amount just fuels the existing idea there's an underlying scalablity issue in there. Nobody with that right hardware has put it under the light of a profiler yet as far as I know. -- 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 |