On Wed, Oct 9, 2013 at 2:15 PM, Bruce Momjian <br...@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'). 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers