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

2011/10/7 Vincent de Phily <vincent(dot)dephily(at)mobile-devices(dot)fr>:
> On Thursday 06 October 2011 12:09:30 Andrew Sullivan wrote:
>> 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.
>
> Ok, I'm glad my reasoning wasn't completely flawed :)
>
>> > 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.
>
> Yup, watching that.
>
>> 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.
>
> Those tables are a hand-made trigger-maintained "materialized view" created
> about 2 months ago. Initializing them meant doing a full seqscan of the
> reference table and doing one insert and 1-2 updates for each row in the MV
> table. And the work was split in thousands of transactions with a load-
> dependent sleep between them, in order to not impact user queries. Those
> updates (and some inserts) still hapen during normal usage, but at a much
> slower pace which autovacuum should have no trouble keeping up with.
>
>
>> > 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.
>
> Interesting. Although if you have such well-defined churn times, it might be
> better to vacuum from cron instead of from autovacuum ? You also don't want to
> autovacuum now if you know your churn will be over in 15 min. Looks like it's
> going to be hard to extract general rules.
>
> One of my motivations to make autovaccum more aggresive was that my fsm was
> too small and I didn't want a PG restart to take the new value into account
> yet. So "finish this vacuum faster and get on to the next one" was a way to do
> that "next one" before the fsm overflowed. But I now realize it's a very bad
> kludge, and I should just have my fsm sized right (or sized automatically;
> have I already said that I long to upgrade ? :p)
>
>> >  * 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).
>
> Ok, so say my churn happens only in the last 10 minutes of data and readonly
> queries only look at the last 24 hours of data, if vacuuming is triggered
> every 48 hours, that's 24 hours of data that will potentially get back into
> the cache with no benefit (or if I'm not mistaken, with PG =< 8.3 it's much
> more than 24 hours). Pity. Is there a counter-example where there is a caching
> benefit to the current behaviour ? If not, that might be a low-hanging fruit
> to improve postgres performance.

Not a direct answer but some items after reading the thread:

* 8.4 come with visibility map and it is nice to reduce IO usage
(without trouble anymore with FSM_ GUC)
* postgresql cache got its own logic, DB oriented.
* operating system cache is proud enough to not waste all of your
cache when reading one file sequentialy.
* you may increase the number of autovacuum workers too, (depend of
your IO and CPU)
* it is better to change the autovacuum settings via cron than running vacuum.

If you wonder, you can use pgfincore to track your OS cache usage per
table&index and the PostgreSQL cache with pg_buffercache.
Note that pgfincore is lock free, but pg_buffercache may impact your
performance (it is still interesting to use it to check how your
shared buffers are used and it can helps optimising your shared_memory
size)

--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation