On 12/23/14 12:52 PM, Stephen Frost wrote:
* José Luis Tallón (jltal...@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 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
