On Wed, Oct 9, 2013 at 10:30 AM, Bruce Momjian <br...@momjian.us> wrote: > Josh Berkus suggested here that work_mem and maintenance_work_mem could > be auto-tuned like effective_cache_size: > > http://www.postgresql.org/message-id/50eccf93.3060...@agliodbs.com
I think that this is unlikely to work out well. effective_cache_size is a relatively unimportant parameter and the main thing that is important is not to set it egregiously too low. The formula we've committed is probably inaccurate in a large number of case, but it doesn't really matter, because it doesn't do that much in the first place. The same cannot be said for work_mem. Setting it too low cripples performance; setting it too high risks bringing the whole system down. Putting an auto-tuning formula in place that depends on the values for multiple other GUCs is just asking for trouble. Just to give a few example, suppose that a user increases shared_buffers. Magically, work_mem also increases, and everything works great until a load spike causes the system to start swapping, effectively dead in the water. Or suppose the user increases max_connections; all of their query plans change, probably getting worse. The value of the auto-tuning has got to be weighed against the risk of unintended consequences and user confusion, which IMHO is pretty high in this case. And quite frankly I don't think I really believe the auto-tuning formula has much chance of being right in the first place. It's generally true that you're going to need to increase work_mem if you have more memory and decrease it work_mem if you have more connections, but it also depends on a lot of other things, like the complexity of the queries being run, whether all of the connection slots are actually routinely used, and whether you've really set shared_buffers to 25% of your system's total memory, which many people do not, especially on Windows. I think we're just going to create the false impression that we know what the optimal value is when, in reality, that's far from true. I think what is really needed is not so much to auto-tune work_mem as to provide a more sensible default. Why not just change the default to 4MB and be done with it? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
