Re: Increased shared_buffer setting = lower hit ratio ? - Mailing list pgsql-performance

From Tomas Vondra
Subject Re: Increased shared_buffer setting = lower hit ratio ?
Date
Msg-id 5465402F.9030509@fuzzy.cz
Whole thread Raw
In response to Re: Increased shared_buffer setting = lower hit ratio ?  (CS DBA <cs_dba@consistentstate.com>)
List pgsql-performance
Hi,

On 14.11.2014 00:16, CS DBA wrote:
> This is on a CentOS 6.5 box running PostgreSQL 9.2
>
>
> On 11/13/14 4:09 PM, CS DBA wrote:
>> All;
>>
>> We have a large db server with 128GB of ram running complex
>> functions.
>>
>> with the server set to have the following we were seeing a
>> somewhat low hit ratio and lots of temp buffers
>>
>> shared_buffers = 18GB
>> work_mem = 75MB
>> effective_cache_size = 105GB
>> checkpoint_segments = 128
>>
>>
>> when we increased the values to these not only did the hit ratio
>> drop but query times are now longer as well:
>>
>>
>> shared_buffers = 28GB
>> work_mem = 150MB
>> effective_cache_size = 105GB
>> checkpoint_segments = 256
>>
>> This does not seem to make sense to me, anyone have any thoughts
>> on why more memory resources would cause worse performance?

what exactly do you mean by hit ratio - is that the page cache hit ratio
(filesystem cache), or shared buffers hit ratio (measured e.g. using
pg_buffercache)?

Regarding the unexpected decrease of performance after increasing
shared_buffers - that's actually quite common behavior. First, the
management of shared buffers is not free, and the more pieces you need
to manage the more expensive it is. Also, by using larger shared buffers
you make that memory unusable for page cache etc. There are also other
negative consequences - double buffering, accumulating more changes for
a checkpoint etc.

The common wisdom (which some claim to be obsolete) is not to set shared
buffers over ~10GB of RAM. It's however very workload-dependent so your
mileage may vary.

To get some basic idea of the shared_buffers utilization, it's possible
to compute stats using pg_buffercache. Also pg_stat_bgwriter contains
useful data.

BTW, it's difficult to say why a query is slow - can you post explain
analyze of the query with both shared_buffers settings?

And just to check - what kind of hardware/kernel version is this? Do you
have numa / transparent huge pages or similar trouble-indicing issues?

Tomas


pgsql-performance by date:

Previous
From: CS DBA
Date:
Subject: Re: Increased shared_buffer setting = lower hit ratio ?
Next
From: Jeff Janes
Date:
Subject: Re: Increased shared_buffer setting = lower hit ratio ?