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: Jaime Casanova on 20 Apr 2010 15:45 On Tue, Apr 20, 2010 at 12:07 PM, Jim Nasby <decibel(a)decibel.org> wrote: > On Apr 16, 2010, at 4:56 PM, Robert Haas wrote: >> 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. > > 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. > i have heard this before, sadly enough i don't have a machine for that kind of tests and can't use my customer's production servers for such things :) so, i always set shared buffers lower than 8Gb even if i have ram for more... someone can confirm the lock contention theory? this should be noticeable at checkpoint time right? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL AsesorÃa y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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 17:36 Robert Haas wrote: > On Mon, Apr 19, 2010 at 10:21 AM, Kevin Grittner > <Kevin.Grittner(a)wicourts.gov> wrote: > > Robert Haas <robertmhaas(a)gmail.com> wrote: > > > >> 2. Reading the section on checkpoint_segments reminds me, again, > >> that the current value seems extremely conservative on modern > >> hardware. ?It's quite easy to hit this when doing large bulk data > >> loads or even a big ol' CTAS. ?I think we should consider raising > >> this for 9.1. > > > > 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. > > I don't actually know what's best. I'm just concerned that we have a > default in postgresql.conf and a tuning guide that says "don't do > that". Maybe the tuning guide needs to be more nuanced, or maybe > postgresql.conf needs to be changed, but it makes no sense to have > them saying contradictory things. The good news about checkpoint_segments is that you get a log file warning message if the value should be increased, i.e. you are checkpointing often than 30 seconds. -- 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 17:48 On Mon, Apr 19, 2010 at 5:36 PM, Bruce Momjian <bruce(a)momjian.us> wrote: >> I don't actually know what's best. I'm just concerned that we have a >> default in postgresql.conf and a tuning guide that says "don't do >> that". Maybe the tuning guide needs to be more nuanced, or maybe >> postgresql.conf needs to be changed, but it makes no sense to have >> them saying contradictory things. > > The good news about checkpoint_segments is that you get a log file > warning message if the value should be increased, i.e. you are > checkpointing often than 30 seconds. Yeah. I get that warning frequently when I'm creating test tables of dummy data for PG devel purposes. That's actually the main thing that makes me think the default may be too low. ....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: Bruce Momjian on 19 Apr 2010 18:06 Robert Haas wrote: > On Mon, Apr 19, 2010 at 5:36 PM, Bruce Momjian <bruce(a)momjian.us> wrote: > >> I don't actually know what's best. ?I'm just concerned that we have a > >> default in postgresql.conf and a tuning guide that says "don't do > >> that". ?Maybe the tuning guide needs to be more nuanced, or maybe > >> postgresql.conf needs to be changed, but it makes no sense to have > >> them saying contradictory things. > > > > The good news about checkpoint_segments is that you get a log file > > warning message if the value should be increased, i.e. you are > > checkpointing often than 30 seconds. > > Yeah. I get that warning frequently when I'm creating test tables of > dummy data for PG devel purposes. That's actually the main thing that > makes me think the default may be too low. 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. However, this is the first time I am hearing that battery-backed cache favors the default value. -- 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 18:15
On Mon, Apr 19, 2010 at 6:06 PM, Bruce Momjian <bruce(a)momjian.us> wrote: > Robert Haas wrote: >> On Mon, Apr 19, 2010 at 5:36 PM, Bruce Momjian <bruce(a)momjian.us> wrote: >> >> I don't actually know what's best. ?I'm just concerned that we have a >> >> default in postgresql.conf and a tuning guide that says "don't do >> >> that". ?Maybe the tuning guide needs to be more nuanced, or maybe >> >> postgresql.conf needs to be changed, but it makes no sense to have >> >> them saying contradictory things. >> > >> > The good news about checkpoint_segments is that you get a log file >> > warning message if the value should be increased, i.e. you are >> > checkpointing often than 30 seconds. >> >> Yeah. I get that warning frequently when I'm creating test tables of >> dummy data for PG devel purposes. That's actually the main thing that >> makes me think the default may be too low. > > 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. ....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 |