[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: [PERFORM] Increased shared_buffer setting = lower hit ratio ?
From:       Jeff Janes <jeff.janes () gmail ! com>
Date:       2014-11-14 0:01:59
Message-ID: CAMkU=1xUHVnsSx36hMiM2z1Cy4xmKtPc0Vy+XZ9Or_BmwGXeHw () mail ! gmail ! com
[Download RAW message or body]

On Thu, Nov 13, 2014 at 3:09 PM, CS DBA <cs_dba@consistentstate.com> 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?
>

You should try changing those things separately, there isn't much reason
that shared_buffers and work_mem should be changed together.

There are many reasons the hit ratio and the performance could have gotten
worse, without more info we can just speculate.  I'd guess it is just as
likely as not that the two observations actually have different causes,
rather than both being caused by the same thing.  Can you figure out which
specific queries changed performance?  Barring that, which objects changed
hit ratios the most?  And how did the actual buffer hit statistics change?
Looking at just the ratio obscures more than it enlightens.

Large sorts are often slower when given more memory.  If you give it so
much more memory that it becomes an in-memory sort, it will get faster.
But if you change it from (for example) a 12-way merge of X sized runs to a
6-way merge of X*2 size runs it could very well be slower because you are
making poor use of the CPU cache and spending more time waiting on main
memory while building those runs. But that shouldn't show up hit ratios,
just in performance.

A higher work_mem might also prompt a plan to read an entire table and hash
it, rather than do a nested loop probing its index.  If the index was
well-cached in shared buffers but the whole table is not, this could make
the buffer hit ratio look worse.

Cheers,

Jeff

[Attachment #3 (text/html)]

<div dir="ltr"><div class="gmail_extra"><div class="gmail_quote">On Thu, Nov 13, 2014 \
at 3:09 PM, CS DBA <span dir="ltr">&lt;<a href="mailto:cs_dba@consistentstate.com" \
target="_blank">cs_dba@consistentstate.com</a>&gt;</span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex">All;<br> <br>
We have a large db server with 128GB of ram running complex functions.<br>
<br>
with the server set to have the following we were seeing a somewhat low hit ratio and \
lots of temp buffers<br> <br>
shared_buffers = 18GB<br>
work_mem = 75MB<br>
effective_cache_size = 105GB<br>
checkpoint_segments = 128<br>
<br>
<br>
when we increased the values to these not only did the hit ratio drop but query times \
are now longer as well:<br> <br>
<br>
shared_buffers = 28GB<br>
work_mem = 150MB<br>
effective_cache_size = 105GB<br>
checkpoint_segments = 256<br>
<br>
This does not seem to make sense to me, anyone have any thoughts on why more memory \
resources would cause worse performance?<br></blockquote><div><br></div><div>You \
should try changing those things separately, there isn&#39;t much reason that \
shared_buffers and work_mem should be changed \
together.</div><div><br></div><div>There are many reasons the hit ratio and the \
performance could have gotten worse, without more info we can just speculate.   \
I&#39;d guess it is just as likely as not that the two observations actually have \
different causes, rather than both being caused by the same thing.   Can you figure \
out which specific queries changed performance?   Barring that, which objects changed \
hit ratios the most?   And how did the actual buffer hit statistics change?   Looking \
at just the ratio obscures more than it enlightens.</div><div><br></div><div>Large \
sorts are often slower when given more memory.   If you give it so much more memory \
that it becomes an in-memory sort, it will get faster.   But if you change it from \
(for example) a 12-way merge of X sized runs to a 6-way merge of X*2 size runs it \
could very well be slower because you are making poor use of the CPU cache and \
spending more time waiting on main memory while building those runs. But that \
shouldn&#39;t show up hit ratios, just in performance.</div><div><br></div><div>A \
higher work_mem might also prompt a plan to read an entire table and hash it, rather \
than do a nested loop probing its index.   If the index was well-cached in shared \
buffers but the whole table is not, this could make the buffer hit ratio look \
worse.</div><div><br></div><div>Cheers,</div><div><br></div><div>Jeff</div></div></div></div>




[prev in list] [next in list] [prev in thread] [next in thread]