Message-ID: <5465402F.9030509@fuzzy.cz>
Date: Fri, 14 Nov 2014 00:35:11 +0100
From: Tomas Vondra <tv@fuzzy.cz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.2.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Increased shared_buffer setting = lower hit ratio ?
References: <54653A1E.3050805@consistentstate.com>
 <54653BBB.5060508@consistentstate.com>
In-Reply-To: <54653BBB.5060508@consistentstate.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance