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

On 12/23/14 12:52 PM, Stephen Frost wrote:
> * José Luis Tallón (jltallon(at)adv-solutions(dot)net) wrote:
>> >On 12/23/2014 05:29 PM, Stephen Frost wrote:
>>>> > >> The "capabilities" would be:
>>>> > >>* MAINTENANCE --- Ability to run
>>>> > >> VACUUM [ANALYZE | FREEZE] (but not VACUUM FULL),
>>>> > >> ANALYZE (including SET LOCAL statistics_target TO 10000),
>>> > >There's likely to be discussion about these from the perspective that
>>> > >you really shouldn't need to run them all that much. Why isn't
>>> > >autovacuum able to handle this?
>> >
>> >For some (arguably, ill-devised) use cases of INSERT - SELECT
>> >aggregate - DELETE (third party, closed-source app, massive insert
>> >rate) at the very least, autovacuum can't possibly cope with the
>> >change rate in some tables, given that there are quite many other
>> >interactive queries running.
>> >
>> >Manually performing VACUUM / VACUUM ANALYZE on the (few) affected
>> >tables every 12h or so fixes the performance problem for the
>> >particular queries without impacting the other users too much ---
>> >the tables and indexes in question have been moved to a separate
>> >tablespace/disk volume of their own.
> 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.

- 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.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com