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