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 <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: http://www.postgresql.org/mailpref/pgsql-hackers
