* Jim Nasby (jim.na...@bluetreble.com) wrote:
> On 12/23/14 12:52 PM, Stephen Frost wrote:
> >Autovacuum can certainly run vacuum/analyze on a few tables every 12
> >hours, so I'm not really following where you see autovacuum being unable
> >to cope.  I agree that there*are*  such cases, but getting more
> >information about those cases and exactly what solution*does*  work
> >would really help us improve autovacuum to address those use-cases.
> (going through some old email...)
> The two cases I've dealt with recently are:
> - Tables with a fair update/delete rate that should always stay small
> The problem with these tables is if anything happens to upset vacuuming you 
> can end up with a significantly larger than expected table that's now 
> essentially impossible to shrink. This could be caused by a single 
> long-running transaction that happens to be in play when autovac kicks off, 
> or for other reasons. Even once you manage to get all the tuples off the end 
> of the heap it can still be extremely difficult to grab the lock you need to 
> truncate it. Running a vacuum every minute from cron seems to help control 
> this. Sadly, your indexes still get bloated, so occasionally you want to 
> re-cluster too.

The difference between the autovacuum-run vacuum and the cron-run vacuum
is that the one running out of cron will just keep holding the lock
until it's actually able to truncate the end of the relation, no?  I
recall discussion previously that we need a way to either support that
in autovacuum for (a configurable set of) regular relations or come up
with a solution that doesn't require that lock.

> - Preemptively vacuuming during off-hours
> Many sites have either nightly or weekend periods of reduced load. Such sites 
> can gain a great benefit from scheduling preemptive vacuums to reduce the 
> odds of disruptive vacuuming activity during heavy activity periods. This is 
> especially true when it comes to a scan_all vacuum of a large table; having 
> autovac do one of those at a peak period can really hose things.

Having preferrable times for autovacuum to run vacuums would certainly
be nice to support this use-case.

All that said, I'm not against a role attribute which allows the user to
vacuum/analyze anything.  I do think that's a bit different from the
existing effort to reduce the activities which require superuser as with
the vacuum/analyze case you *could* have a single role that's a member
of every role that owns the relations which you want to vacuum/analyze.
I grant that it's a bit awkward though.



Attachment: signature.asc
Description: Digital signature

Reply via email to