Re: maintenance memory vs autovac

"Guillaume Smet" <guillaume(dot)smet(at)gmail(dot)com> writes:

> On Wed, Dec 3, 2008 at 10:49 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
>> It's probably worthwhile to add a note about the effects of
>> autovacuum around the documentation of maintenance_work_mem, though.
>
> +1
> A lot of people set maintenance_work_mem quite high because of the old
> behaviour.

The high level view is that an admin will expect to be able to allocate all
the memory on his machine (at least all the memory he expects Postgres to use)
as something like:

shared_buffers and sundry shared mem
+ max_connections*work_mem
+ maintenance_work_mem
+ filesystem cache

(Yes, "max_connections" isn't quite right there but that's the general idea)

If you have 1G of ram and allocate 200M of shared buffers, 1M of work_mem of
which you don't expect more than a hundred concurrent allocations, and want
about half your ram set aside for filesystem cache you would be quite
reasonable to expect to have about 256M to play with for maintenance_work_me
-- which in my experience is a nice value (lower than that is noticeably
slower and greater has little effect on sorting data sets I've seen).

But if you set things up that way you could end up with three autovacuum
daemons running with 256M allocated each on a 1G machine. That's pretty
frightening, especially with a 200M shared buffers.

We definitely need at the very least a prominent warning in the
maintenance_work_mem documentation. Users can always raise it for manually run
commands if they're sure they're only running one at a time.

But all of this isn't a new issue is it? I thought we've had multiple
autovacuum workers since 8.3. Have there been any complaints?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!