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

On 1/28/15 7:45 PM, Stephen Frost wrote:
> Jim,
>
> * Jim Nasby (Jim(dot)Nasby(at)BlueTreble(dot)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.

AFAICT, in master, there is no difference in truncation between auto and manual vacuum. What we do is attempt to acquire the truncation lock for up to 5 seconds, giving up after that. Once we do have the lock, we check to see how many pages we can actually truncate. During that check, we test every ~20ms or so to see if someone else is waiting on our exclusive 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 repeatedly trying 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 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.

Yeah, I was mostly just providing some use cases. I'm not opposed to a separate vacuum/analyze permission, but don't see a huge need for it either. Typically I set this stuff up as a cron on the server itself, utilizing an account that does ident 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