On 1/28/15 7:45 PM, Stephen Frost wrote:
Jim,
* Jim Nasby (jim.na...@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 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
