Обсуждение: shared_buffers 8GB maximum

Hi All,

I've seen the shared_buffers 8GB maximum recommendation repeated many 
times. I have several questions in this regard.

- Is this recommendation still true for recent versions of postgres? 
(e.g. wasn't it the case only for really old versions where the locks on 
shared buffers worked much less efficiently)

- I'm not a huge Linux expert, but I've heard someone saying that 
reading from the filesystem cache requires a context switch. I suspect 
that such reads are slightly more expensive now after the 
Meltdown/Spectre patch in the kernel. 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?


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.

PPS. I know any possible answer might be just a recommendation, and 
parameter values should be tested for each specific case, but still 
wanted to hear your opinion. Thanks.

Regards,
Vitaliy

On Sat, 17 Feb 2018 00:36:57 +0200, Vitaliy Garnashevich
<vgarnashevich@gmail.com> wrote:


>- I'm not a huge Linux expert, but I've heard someone saying that
>reading from the filesystem cache requires a context switch.

Yes.

>I suspect >that such reads are slightly more expensive now after the
>Meltdown/Spectre patch in the kernel.

Not necessarily - it depends on exactly what was changed ... which
unfortunately I don't know for certain.

Any filesystem call is a kernel transition.  That's a Meltdown issue.
Meltdown can be avoided by using trampoline functions to call the
(real) kernel functions and isolating each trampoline so that no other
code immediately follows it.  This wastes some memory but there is
very little added time cost.


Spectre is about snooping within the user space of a single process -
it has nothing to do with kernel calls.  The issues with Spectre are
things like untrusted code breaking out of "sandboxes", snooping on
password handling or encryption, etc.

Fixing Spectre requires purposefully limiting speculative execution of
code and can significantly affect performance.  But the effects are
situation dependent.


>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.

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.

Another reason may be that the server is not dedicated to PG but does
other things as well.  Dramatically increasing PG's memory use may
negatively impact something else.


>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.


>PPS. I know any possible answer might be just a recommendation, and
>parameter values should be tested for each specific case, but still
>wanted to hear your opinion. Thanks.
>
>Regards,
>Vitaliy

George

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

> Not necessarily - it depends on exactly what was changed ... which
> unfortunately I don't know for certain.
>
> Any filesystem call is a kernel transition.  That's a Meltdown issue.
> Meltdown can be avoided by using trampoline functions to call the
> (real) kernel functions and isolating each trampoline so that no other
> code immediately follows it.  This wastes some memory but there is
> very little added time cost.
>
>
> Spectre is about snooping within the user space of a single process -
> it has nothing to do with kernel calls.  The issues with Spectre are
> things like untrusted code breaking out of "sandboxes", snooping on
> password handling or encryption, etc.
>
> Fixing Spectre requires purposefully limiting speculative execution of
> code and can significantly affect performance.  But the effects are
> situation dependent.
>

I don't know the details either. But one of proposed fixes was to flush 
CPU caches after doing system calls. That's the reason why I'm asking.


> 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.


That's what we're going to do. Thanks!

Regards,
Vitaliy

> 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.

Double buffering is often mentioned in context of tuning shared buffers. 
Is there a tool to actually measure the amount of double buffering 
happening in the system?

> 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.

In the case when shared_buffers cover most of RAM, most of writes should 
happen by checkpointer, and cache hit ratio should be high. So a 
hypothetical question: Could shared_buffers=200GB on a 250 GB RAM server 
ever be a reasonable setting? (assuming there are no other applications 
running except postgres, and 50GB is enough for allocating 
work_mem/maintenance_work_mem and for serving queries)

> 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.
>
> 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.

Thanks! While increasing shared_buffers we'll be looking at changes in 
cache hit ratio too.

Regards,
Vitaliy

On 02/18/2018 02:41 PM, Vitaliy Garnashevich wrote:
> 
>> 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.
> 
> Double buffering is often mentioned in context of tuning shared
> buffers. Is there a tool to actually measure the amount of double
> buffering happening in the system?
> 

I'm not aware of such tool. But I suppose it could be done by
integrating information from pg_buffercache and pgfincore [1].

[1] https://github.com/klando/pgfincore

>> 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.
> 
> In the case when shared_buffers cover most of RAM, most of writes
> should happen by checkpointer, and cache hit ratio should be high. So
> a hypothetical question: Could shared_buffers=200GB on a 250 GB RAM
> server ever be a reasonable setting? (assuming there are no other
> applications running except postgres, and 50GB is enough for
> allocating work_mem/maintenance_work_mem and for serving queries)
> 

It depends on how large is the active part of the data set is. If it
fits into 200GB but not to smaller shared buffers (say, 100GB), then
using 200GB may be a win.

If the active set is much larger than RAM, smaller shared_buffer values
work better in my experience.


regards

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

Yes.  I don't know the exact reason, but reading a buffer from OS cache is quite a bit more expensive than just pinning a buffer already in the buffer_pool, about 5 times more expensive the last time I tested it, which was before Meltdown.  (And just pinning a buffer which is already in the cache is already pretty expensive--about 15 times as expensive as reading the next tuple from an already-pinned buffer).

Thanks for the numbers. Just out of curiosity, do you happen to know how much more expensive compared to that a read from disk is? And also, how much the pinning can be slowed down, when having to iterate using the clock-sweep method over large shared_buffers?

I don't think that there is any reason to think that buffers_clean > buffers_checkpoint is a problem.  In fact, you could argue that it is the way it was designed to work.  Although the background writer does need to tell the checkpointer about every file it dirties, so it can be fsynced at the end of the checkpoint.  The overhead of this was minimal in my testing.

The reason why I mentioned buffers_clean is because I was assuming that under "healthy" conditions, most writes should be done by checkpointer, because, as it was already mentioned, that's the most efficient way of writing (no duplicate writes of the same buffer, write optimizations etc.). I was thinking about bgwriter as a way of reducing latency by avoiding the case when a backend has to write buffers by itself. So that would mean that big numbers in buffers_clean and buffers_backend compared to buffers_checkpoint, would mean that a lot of writes are done not by checkpointer, and thus probably less efficiently than they could be. That might have resulted in IO writes being more random, and more IO writes done in general, because same buffer can be written multiple times between checkpoints.

But buffers_backend > buffers_checkpoint could be a problem, especially if they are also much larger than buffers_clean.  But the wrinkle here is that if you do bulk inserts or bulk updates (what about vacuums?), the backends by design write their own dirty buffers.  So if you do those kinds of things, buffers_backend being large doesn't indicate much.  There was a patch someplace a while ago to separate the counters of backend-intentional writes from backend-no-choice writes, but it never went anywhere. 

We do daily manual vacuuming. Knowing what part of total writes is accounted for them indeed would be nice.

When looking at buffers_checkpoint/buffers_clean/buffers_backend, I was saving the numbers with several hours interval, knowing that there are no vacuums running at that time, and calculated the difference.

It is not clear to me that this is the best way to measure health.  Did your response time go down?  Did your throughput go up?

We have mixed type of DB usage. There is OLTP-like part with many small read/write transactions. Predictable latency does not matter in that case, but throughput does, because that is basically a background data loading job. Then there is an OLAP-like part when heavier report queries are being run. Then there are more background jobs which are a combination of both, which at first run long queries and then do lots of small inserts, thus pre-calculating some data for bigger reports.

After increasing shared_buffers 8GB -> 64GB, there was 7% improvement in run time of the background pre-calculating job (measured by running several times in a row, and caches are hot).

When we configured hugepages for the bigger shared_buffers, the additional improvement was around 3%.

Regards,
Vitaliy

When we did calculation of some analytic tasks, then increasing shared_buffers had negative impact on speed. Probably hit ration was too low after change, but the maintenance of shared buffers (searching free blocks) was slower.

What was the size of shared buffers when slowdown happened (approximately)?

Regards,
Vitaliy