Re: shared_buffers 8GB maximum

On 02/17/2018 02:56 AM, George Neuner wrote:
> On Sat, 17 Feb 2018 00:36:57 +0200, Vitaliy Garnashevich
> <vgarnashevich@gmail.com> wrote:
>
...
>
>> Could that be a reason for increasing the value of shared_buffers?
>>
>> - Could shared_buffers=128GB or more on a 250 GB RAM server be a 
>> reasonable setting? What downsides could there be?
> 
> It depends. 8GB is pretty small for such a large server, but taking 
> 1/2 the RAM is not necessarily the right thing either.
> 

I certainly wouldn't recommend using 1/2 of RAM right away. There's a
good chance it would be a waste of memory - for example due to double
buffering, which effectively reduces "total" cache hit ratio.

Start with lower value, increment it gradually and monitor behavior of
the server.

> The size of shared buffers affects log size and the time to complete 
> checkpoints. If a large(ish) percentage of your workload is writes, 
> having a very large shared space could be bad for performance, or
> bad for space on the log device.
> 

The size of shared_buffers has pretty much no impact on the size of the
WAL - that's flat out wrong.

It also does not affect the time needed to perform a checkpoint. It may
mean that the checkpoint has to write more dirty buffers, but that is
actually a good thing because a checkpoint is about the most efficient
way to do writes. By using smaller shared buffers you're making it more
likely the database has to evict (dirty) buffers from shared buffers to
make space for other buffers needed by queries/vacuum/whatever.

Those evictions are performed either by backends or bgwriter, both of
which are less efficient than checkpointer. Not only can checkpointer
perform various optimizations (e.g. sorting buffers to make the writes
more sequential), but it also writes each dirty buffer just once. With
smaller shared_buffers the page may have be written multiple times.

What actually *does* matter is the active part of the data set, i.e. the
part of the data that is actually accessed regularly. In other words,
your goal is to achieve good cache hit ratio - say, 95% or more. This
also helps reducing the number of syscalls (when reading data from page
cache).

What is the right shared_buffers size? I have no idea, as it's very
dependent on the application. It might be 1GB or 100GB, hard to say.

The best thing you can do is set shared buffers to some conservative
value (say, 4-8GB), let the system run for a day or two, compute the
cache hit ratio using metrics in pg_stat_database, and then decide if
you need to resize shared buffers.


>> PS. Some background. We had shared_buffers=8GB initially. In 
>> pg_stat_bgwriter we saw that dirty buffers were written to disk more 
>> frequently by backends than during checkpoints (buffers_clean > 
>> buffers_checkpoint, and buffers_backend > buffers_checkpoint). According 
>> to pg_buffercache extension, there was very small percentage of dirty 
>> pages in shared buffers. The percentage of pages with usagecount >= 3 
>> was also low. Some of our more frequently used tables and indexes are 
>> more than 10 GB in size. This all suggested that probably the bigger 
>> tables and indexes, whenever scanned, are constantly flushing pages from 
>> the shared buffers area. After increasing shared_buffers to 32GB, the 
>> picture started looking healthier. There were 1GB+ of dirty pages in 
>> shared buffers (vs 40-200MB before), 30-60% of pages with usagecount >= 
>> 3 (vs 10-40% before), buffers_checkpoint started to grow faster than 
>> buffers_clean or buffers_backend. There is still not all frequently used 
>> data fits in shared_buffers, so we're considering to increase the 
>> parameter more. I wanted to have some idea about how big it could 
>> reasonably be.
> 
> So now you know that 32GB is better for your workload than 8GB.  But
> that is not necessarily a reason immediately to go crazy with it.  Try
> increasing it gradually - e.g., adding 16GB at a time - and see if the
> additional shared space provides any real benefit.
> 

Gradual increases are a good approach in general. And yes, having

    buffers_checkpoint > buffers_clean > buffers_backend

is a good idea too. Together with the cache hit ratio it's probably a
more sensible metric than looking at usagecount directly.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services