Re: Auto-tuning work_mem and maintenance_work_mem

> More generally, Josh has made repeated comments that various proposed
> value/formulas for work_mem are too low, but obviously the people who
> suggested them didn't think so. So I'm a bit concerned that we don't
> all agree on what the end goal of this activity looks like.

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.

The core issue here is that there aren't good "generic" values for these
settings for all users -- that's why we have the settings in the first
place. Following a formula isn't going to change that.

If we're serious about autotuning, then we should look at:

a) admissions control for non-shared resources (e.g. work_mem)

b) auto-feedback tuning loops (ala Heikki's checkpoint_segments and the
bgwriter).

We could certainly create an autofeedback tuning loop for work_mem.
Just watch the database, record the amount of data spilled to disk for
work (pg_stat_sorts), and record the total RAM pinned by backends.
*Then* apply a formula and maybe bump up work_mem a bit depending on
what comes out of it. And keep monitoring and keep readjusting.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com