Re: CPU spikes and transactions

On Tue, Oct 15, 2013 at 12:26 PM, Julien Cigar <jcigar(at)ulb(dot)ac(dot)be> 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(at)fuzzy(dot)cz> 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