On Thu, Oct 10, 2013 at 10:20:36PM -0700, Josh Berkus wrote: > Robert, > > >> The counter-proposal to "auto-tuning" is just to raise the default for > >> work_mem to 4MB or 8MB. Given that Bruce's current formula sets it at > >> 6MB for a server with 8GB RAM, I don't really see the benefit of going > >> to a whole lot of code and formulas in order to end up at a figure only > >> incrementally different from a new static default. > > > > Agreed. But what do you think the value SHOULD be on such a system? > > That's the problem: It Depends. > > One thing in particular which is an issue with calculating against > max_connections is that users who don't need 100 connections seldom > *reduce* max_connections. So that developer laptop which only needs 3 > connections is still going to have a max_connections of 100, just like > the DW server where m_c should probably be 30. > > > I guess the point I'm making here is that raising the default value is > > not mutually exclusive with auto-tuning. We could quadruple the > > current defaults for work_mem and maintenance_work_mem and be better > > off right now, today. Then, we could improve things further in the > > future if and when we agree on an approach to auto-tuning. And people > > who don't use the auto-tuning will still have a better default. > > Seems fine to me.
I think we are nearing a conclusion on these issues, and I thank everyone for the vigorous discussion. When Josh showed disappointment at the small increases in work_mem and maintenance_work_mem from autotuning, I realized the complexity of autotuning just wasn't warranted here. Andrew's concern about the risks of having a work_mem too high was also sobering. Effective_cache_size has neither of these issues, and hence was logical for auto-tuning. I know Robert originally suggested just improving the work_mem default --- I now agree with him, and am sorry it took me so long to realize he was right. One other problem with auto-tuning is that it really relies not only on allocated_memory, but also on max_connections and autovacuum_max_workers, which are going to be rather arbitrary and hard for a user to set good enough to help auto-tuning. Josh might be right that auto-tuning of work_mem has to be more dynamic, perhaps based on the number of _active_ backends or number of backends who have allocate or are currently using work_mem. Our new dynamic shared memory allocation routines might help here in allocationg memory that can be easily purged from the process address space. I am now seeing a pattern that per-backend allocations really need run-time tuning, rather than being based on fixed GUC values. In summary, I think we need to: * decide on new defaults for work_mem and maintenance_work_mem * add an initdb flag to allow users/packagers to set shared_bufffers? * add an autovacuum_work_mem setting? * change the default for temp_buffers? I will try to think some more about work_mem dynamic/runtime tuning and return to it later. I know Kevin has also thought about it. I am also interesting in working on a server-side function that will make configuration suggestions or use ALTER SYSTEM to set values. I could do it in PL/pgSQL, but PL/Perl would allow me to run operating system commands to probe for OS information. The function could look at statistics and pg_buffercache output, and would be run during a typical workload. -- 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
