From: | Vitaliy Garnashevich <vgarnashevich(at)gmail(dot)com> |
---|---|
To: | Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: shared_buffers 8GB maximum |
Date: | 2018-02-18 13:41:23 |
Message-ID: | 688b6a68-bcaf-5658-8b94-fa5720c74d7c@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Rich Shepard | 2018-02-18 13:41:32 | Re: Need to fix one more glitch in upgrade to -10.2 |
Previous Message | David Rowley | 2018-02-18 05:37:41 | Re: query performance |