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

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  <br...@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to