Re: Auto-tuning work_mem and maintenance_work_mem

On Wed, Oct  9, 2013 at 03:04:24PM -0700, Peter Geoghegan wrote:
> On Wed, Oct 9, 2013 at 2:15 PM, Bruce Momjian <bruce@momjian.us> wrote:
> > I did like Josh's idea about using autovacuum_max_workers for
> > maintenance_work_mem, though I used the shared_buffers/4 calculation.
> 
> I don't like that idea myself, because I wouldn't like to link
> maintenance_work_mem to autovacuum_max_workers.
> 
> As you yourself said, maintenance_work_mem exists because maintenance
> operations are generally much less common than operations that service
> queries.
> 
> Couldn't you make the case that autovacuum also services queries?
> Certainly, autovacuum can be expected to use multiple large
> allocations of memory, once per worker. From the DBA's perspective,
> this could be happening at any time, to any extent, much like with
> work_mem allocations (though we at least bound these special
> maintenance_work_mem allocations to at most autovacuum_max_workers).
> So I really think the case is strong for a vacuum_work_mem (with a
> default of -1 that means 'use maintenace_work_mem').

Splitting out vacuum_work_mem from maintenance_work_mem is a separate
issue.  I assume they were combined because the memory used for vacuum
index scans is similar to creating an index.  I am not sure if having
two settings makes something more likely to be set --- I would think the
opposite.

> > Here are the defaults for two configurations;  first, for the 128MB
> > default shared_buffers:
> 
> I am certainly supportive of the idea of improving our defaults here.
> The bar is so incredibly low that anything is likely to be a big
> improvement. What you've suggested here looks not unreasonable to me.
> Have you thought about clamping the value too? I'm thinking of very
> small shared_buffers sizings. After all, 128MB isn't the default in
> the same way 1MB is presently the default work_mem setting.
> 
> It is certainly true that shared_buffers size is a poor proxy for an
> appropriate work_mem size, but does that really matter?

Right, the bar is low, so almost anything is an improvement.  I figure I
will just keep tweeking the algorithm until no one complains.

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