On Wed, Oct 9, 2013 at 04:38:01PM +0200, Andres Freund wrote: > On 2013-10-09 10:35:28 -0400, Bruce Momjian wrote: > > On Wed, Oct 9, 2013 at 04:32:44PM +0200, Andres Freund wrote: > > > On 2013-10-09 10:30:46 -0400, Bruce Momjian 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 > > > > > > > > The attached patch implements this, closely matching the default values > > > > for the default shared_buffers value: > > > > > > There imo is no correlation between correct values for shared_buffers > > > and work_mem at all. They really are much more workload dependant than > > > anything. > > > > Well, that is true, but the more shared_buffers you allocate, the more > > work_mem you _probably_ want to use. This is only a change of the > > default. > > Not at all. There's lots of OLTP workloads where huge shared buffers are > beneficial but you definitely don't want a huge work_mem. > > > Effectively, if every session uses one full work_mem, you end up with > > total work_mem usage equal to shared_buffers. > > But that's not how work_mem works. It's limiting memory, per node in the > query. So a complex query can use it several dozen times.
True, but again, odds are all sessions are not going to use the full work_mem allocation, so I figured assuming each session uses one full work_mem was probably an over-estimate. You are saying that auto-tuning work_mem for typical workloads is not a win? I don't understand how that can be true. You can always change the default for atypical workloads. As a data point, users often set shared_buffers to 2GB, but the default work_mem of 1MB would mean you would use perhaps 100MB for all sorting --- that seems kind of small as a default. -- 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
