Re: Proposal: two new role attributes and/or capabilities?

On 1/28/15 7:45 PM, Stephen Frost wrote:
> Jim,
>
> * Jim Nasby (Jim.Nasby@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
thanexpected table that's now essentially impossible to shrink. This could be caused by a single long-running
transactionthat happens to be in play when autovac kicks off, or for other reasons. Even once you manage to get all the
tuplesoff the end of the heap it can still be extremely difficult to grab the lock you need to truncate it. Running a
vacuumevery minute from cron seems to help control this. Sadly, your indexes still get bloated, so occasionally you
wantto 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.

AFAICT, in master, there is no difference in truncation between auto and manual vacuum. What we do is attempt to
acquirethe truncation lock for up to 5 seconds, giving up after that. Once we do have the lock, we check to see how
manypages we can actually truncate. During that check, we test every ~20ms or so to see if someone else is waiting on
ourexclusive lock; if they are we stop counting and will only truncate the relation up to that point.
 

So what this boils down to is that it's very hard to truncate a busy relation and your best bet of doing so is by
repeatedlytrying to.
 

>> - Preemptively vacuuming during off-hours
>>
>> Many sites have either nightly or weekend periods of reduced load. Such sites can gain a great benefit from
schedulingpreemptive vacuums to reduce the odds of disruptive vacuuming activity during heavy activity periods. This is
especiallytrue when it comes to a scan_all vacuum of a large table; having autovac do one of those at a peak period can
reallyhose 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.

Yeah, I was mostly just providing some use cases. I'm not opposed to a separate vacuum/analyze permission, but don't
seea huge need for it either. Typically I set this stuff up as a cron on the server itself, utilizing an account that
doesident authentication. I figure if someone manages to compromise that then they probably have root on the box
anyway,which is obviously game over.
 
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com