"Guillaume Smet" <[EMAIL PROTECTED]> writes: > On Wed, Dec 3, 2008 at 10:49 AM, Magnus Hagander <[EMAIL PROTECTED]> 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! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
