Re: Never-idle autovacuum, and does (auto)vacuuming fill the OS cache ?

On Thu, Oct 06, 2011 at 03:39:02PM +0200, Vincent de Phily wrote:
> In the case of vacuuming however, I think there's a point to be made about
> finishing fast when all vacuum workers are constantly busy : say the vacuum
> daemon notices that there are 10 tables that need vacuuming now. It allocates
> 3 workers, but while they do their intentionally-slow work, the other 7 tables > keep creating more vacuumable tuples, so it'll be more work overall because
> they're "late" in their "vacuum schedule". Does that make sense (I'm not sure
> id does) ?

Yes, that's exactly the issue. You need to balance the resource
you're depriving the "real" database transactions (i.e. the user ones)
against the cost of waiting, which waiting will probably cost those
user transactions in performance. The reason there's no magic
solution is because much of this depends on your use patterns.

> Anyway, my particular issue is solved for now : I realized those tables were
> terribly bloated (often more than 99% slack), so I vacuum-fulled them and now
> the autovacuums run very fast and the disk is 90% idle again. That slack
> probably appeared at table initialization time because the fsm was not big
> enough. I since raised the fsm, but I think it's big enough during normal
> (non-init) usage anyway.

This is what you want to keep an eye on, then. Why do you think it
came from "initialization time", though? VACUUM only has work to do
when dead tuples show up (e.g. from DELETE or UPDATE), and normally
when you first populate a table you do a COPY, which isn't going to
create dead tuples.

> I'm still interested in more opinions about my two questions :
> * When does it make sense to make autovacuum more aggressive on IO, and by
> how much ?

At bottom, you don't want your tables to get so bloated that they
exhibit the problem you just saw, but you also don't want vacuum to be
taking so much I/O that your other tasks can't get done. That's the
general principle; how it applies to your case depends rather on use
patters. For instance, if you know that there will be at most 10%
churn on every table every day, but all transactions happen between
9:00 and 17:00 local time, then it's probably safe to allow that to
happen: as long as your FSM can keep track, it can all be recovered
every day after 17:00, so you might as well allow the work to build
up, & let the vacuums happen when they're not stealing any I/O from
user queries. If, on the other hand, you get 100% churn on 50% of the
tables every day between 09:00 and 11:00, and the rest of the day is
mostly read-only traffic, with read-only traffic during all 24 hours
(don't scoff -- I had exactly this problem once) then you want to be
quite aggressive with the autovacuum settings, because keeping that
100% bloat down is going to pay off in a big way on the read-only
traffic.

> * Does vacuuming fill the OS's disk cache, and is it an issue if it does ?

Well, it _affects_ the OS's disk cache. Whether it fills it is
controlled by the cache algorithms and the amount of memory you have
devoted to cache. Every time you touch the disk, you potentially
alter the cache in favour of what you just saw.

In the above artificial examples, the vacuums that run "after everyone
went home" will almost certainly end up taking over the cache, because
there's no other activity to keep other things in the disk cache. In
the second example, though, with a lot of read-only activity all the
time, the things that are most popular are likely to remain in a
(modern) disk cache most of the time because they're called so often
that the vacuumed page doesn't end up being enough traffic to cause an
eviction (or, anyway, to evict for any significant time).

A

--
Andrew Sullivan
ajs(at)crankycanuck(dot)ca