Re: Auto-tuning work_mem and maintenance_work_mem

On Wed, Oct 9, 2013 at 09:34:16PM -0400, Robert Haas wrote:
> But your auto-tuned value can easily be too low or too high, too.
> Consider someone with a system that has 64GB of RAM. EnterpriseDB
> has had customers who have found that with, say, a 40GB database, it's
> best to set shared_buffers to 40GB so that the database remains fully
> cached. Your latest formula will auto-tune work_mem to roughly 100MB.
> On the other hand, if the same customer has a 400GB database, which
> can't be fully cached no matter what, a much lower setting for
> shared_buffers, like maybe 8GB, is apt to perform better. Your
> formula will auto-tune shared_buffers to roughly 20MB.
>
> In other words, when there's only 24GB of memory available for
> everything-except-shared-buffers, your formula sets work_mem five
> times higher than when there's 48GB of memory available for
> everything-except-shared-buffers. That surely can't be right.

Let me walk through the idea of adding an available_mem setting, that
Josh suggested, and which I think addresses Robert's concern about
larger shared_buffers and Windows servers.

The idea is that initdb would allow you to specify an available_mem
parameter, which would set a corresponding value in postgresql.conf.
This could be later changed by the user. (See my other email about why
we shouldn't do the tuning in initdb.)

shared_buffers would auto-tune to 25% of that, except on Windows, and
perhaps capped at 8GB, Here is another case where not tuning
directly on shared_buffers is a win.

All other calculations would be based on available_mem - shared_buffers,
so if shared_buffers is manually or auto-tuned high or low, other tuning
would still be accurate.

work_mem would tune to (available_mem - shared_buffers) / 16 /
max_connections, so even if you used all max_connections, and 3x of
work_mem in each, you would still only match the size of shared_buffers.
maintenance_work_mem would key on autovacuum_max_workers.

effective_cache_size would be available_mem minus all of the values
above.

Now, how to handle changes? available_mem could only be changed by a
server restart, because shared_buffers is based on it, and the rest of
the parameters are based on available_mem - shared_buffers. Though
users can change work_mem in postgresql.conf and per-session,
auto-tuning would not be affected by these changes. Calculating only
with available_mem - shared_buffers would give stability and
predicability to the auto-tuning system.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +