Jim, * 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.
Thanks,
Stephen
signature.asc
Description: Digital signature
