Re: Auto-tuning work_mem and maintenance_work_mem

On Wed, Oct  9, 2013 at 08:15:44PM -0700, Peter Geoghegan wrote:
> On Wed, Oct 9, 2013 at 8:02 PM, Bruce Momjian <bruce@momjian.us> wrote:
> > I think the simplest solution would be to have a parameter to initdb
> > which specifies how much memory you want to use, and set a new variable
> > available_mem from that, and have things auto-tune based on that value
> > in the backend.
> 
> Have you tried pgtune?
> 
> http://pgfoundry.org/projects/pgtune/
> 
> It's an extremely simple program - about 500 lines of python. It asks
> a few simple questions, and does as good a job of configuring Postgres
> as most installations will ever need. Importantly, it asks the user to
> characterize their workload as one of the following: DW, OLTP, Web,
> Mixed and Desktop. Why can't we just do something along those lines?
> 
> I know that I constantly find myself rattling off folk wisdom about
> how to set the most important GUCs. I'm not alone here [1]. Isn't it
> about time we just automated this?

I am not sure that having that external to the backend really makes
sense because I am concerned people will not use it.  We can certainly
add it to change our defaults, of course.  Also consider many installs
are automated.

The bottom line is that our defaults need improvement.  A tools would be
nice in addition to that.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +