Re: Auto-tuning work_mem and maintenance_work_mem

On Wed, Oct 9, 2013 at 2:15 PM, Bruce Momjian <bruce(at)momjian(dot)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').

Even for someone who is very experienced, it may not occur to them to
increase maintenance_work_mem when they go to create an index or
something. I know that I deal with servers that have hugely variable
main memory sizes, and it might not even be immediately obvious what
to increase maintenance_work_mem to. Even though the issues may be
understood well in a minority of cases, the DBA still has to have the
presence of mind to specially increase maintenance_work_mem in a psql
session, prior to creating the index. I really mean "presence of
mind", because creating an index can be an emergency operation - I had
to deal with a customer issue where creating an index relieved a
sudden serious production performance issue just last week.

> 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?

--
Peter Geoghegan