On 12/23/14 12:52 PM, Stephen Frost wrote:
> * José Luis Tallón (jltallon@adv-solutions.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
expectedtable that's now essentially impossible to shrink. This could be caused by a single long-running transaction
thathappens to be in play when autovac kicks off, or for other reasons. Even once you manage to get all the tuples off
theend of the heap it can still be extremely difficult to grab the lock you need to truncate it. Running a vacuum every
minutefrom cron seems to help control this. Sadly, your indexes still get bloated, so occasionally you want to
re-clustertoo.
- 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
preemptivevacuums to reduce the odds of disruptive vacuuming activity during heavy activity periods. This is especially
truewhen it comes to a scan_all vacuum of a large table; having autovac do one of those at a peak period can really
hosethings.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com