On Tue, Oct 15, 2013 at 12:26 PM, Julien Cigar <jcigar@xxxxxxxxx> wrote: > On Tue, Oct 15, 2013 at 08:59:08AM -0700, Tony Kay wrote: >> On Mon, Oct 14, 2013 at 4:42 PM, Tomas Vondra <tv@xxxxxxxx> wrote: >> >> > On 15.10.2013 01:00, Tony Kay wrote: >> > > Hi, >> > > >> > > I'm running 9.1.6 w/22GB shared buffers, and 32GB overall RAM on a >> > > 16 Opteron 6276 CPU box. We limit connections to roughly 120, but >> > > our webapp is configured to allocate a thread-local connection, so >> > > those connections are rarely doing anything more than half the time. >> > >> > Lower your shared buffers to about 20% of your RAM, unless you've tested >> > it's actually helping in your particular case. It's unlikely you'll get >> > better performance by using more than that, especially on older >> > versions, so it's wiser to leave the rest for page cache. >> > >> > It might even be one of the causes of the performance issue you're >> > seeing, as shared buffers are not exactly overhead-free. >> > >> > See this for more details on tuning: >> > >> > http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server >> >> >> I had followed the general directions from several sources years ago, which >> indicate up to 40% of RAM. We've been running very large shared buffers for > > in general it's best to start with 10-15% of the RAM and no more then > 2-4 GB > >> 4 years now, but it is difficult to generate a good real load without >> testing against users, so we have not felt the need to move it around. In >> general, I don't tend to tinker with a setting that has been fine for this >> long without good reason. I've been wanting to upgrade to the newer >> mmap-based versions of pgsql, but was waiting to re-tune this when I did so. >> >> Why do you suspect that shared_buffers would cause the behavior I'm seeing? >> > > for two reasons: > > - some of the overhead of bgwriter and checkpoints is more or less linear > in the size of shared_buffers, for example it could be possible that a > large quantity of data could be dirty when a checkpoint occurs). > > - the OS cache is also being used for reads and writes, the larger > shared_buffers is, the more you risk double buffering (same blocks > in the OS cache and in the database buffer cache). That's good reasoning but is not related to the problem faced by the OP. The real reason why I recommend to keep shared buffers at max 2GB, always, is because we have major contention issues which we presume are in the buffer area (either in the mapping or in the clock sweep) but could be something else entirely. These issues tend to show up on fast machines in all- or mostly- read workloads. We are desperate for profiles demonstrating the problem in production workloads. If OP is willing to install and run perf in production (which is not a bad idea anyways), then my advice is to change nothing until we have a chance to grab a profile. These types of problems are notoriously difficult to reproduce in test environments. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
