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

Hi list,

I've got a heavily-updated table, and about 30 customers on the same system
each with his own version of the table. The 3 configured autovacuum workers
take turns vacuuming the table in each customer db; autovacuum is never idle
and takes a large part of the available IO.

Fearing that vacuuming might accumulate lateness and hoping to see the system
idle every now and then, I increased autovacuum_vacuum_cost_limit to 500 and
decreased autovacuum_vacuum_cost_delay to 10. First question : is it an
intelligent thing to do or am I better off ignoring the constant vacuuming and
trusting that things will get done in time ? With the new settings, autovacuum
is still constant (even though each one takes less time), but I'm wary of
making autovacuum even less "io-nice".

Second thing : the vacuumed tables+indexes taken together are bigger than the
available OS disk cache. Does vacuuming them fill the cache, or is there some
kind of O_DIRECT in use ? I have a feeling (very un-verified) that this is not
the most usefull data I could have in my cache.

This is all on PG 8.3. I know upgrading would improve things (particularly
since a large percentage of the table remains static between vacuums), but
we're still too busy for that right now (unless you tell me I'm going to see a
night-and-day difference regarding this particular issue).


Thanks.
--
Vincent de Phily

On Thu, Oct 06, 2011 at 11:58:15AM +0200, Vincent de Phily wrote:
> Fearing that vacuuming might accumulate lateness and hoping to see the system
> idle every now and then,

Why is your goal to see the system idle every now and then?  It's not
going to get tired if it keeps working, and if you have a lot of work
and can spin out that work so that the system always has a little bit
of work to do, then you use your resources more efficiently.

Normally, one likes to see some idle time because it is evidence of
"headroom" -- that you have more capacity than you actually need.  If
that's the reason you want to see the idle times, then surely you
don't want to tune the system with the goal of causing idleness.  You
want to tune the system so that the work gets done in as smooth and
fast a way possible.  So I would aim for maximum throughput (including
but not exclusively complete table maintenance) and then check whether
you're getting any idle time.  Off the cuff, though, it sounds to me
like you need more capacity than you have.

> This is all on PG 8.3. I know upgrading would improve things
> (particularly since a large percentage of the table remains static
> between vacuums), but we're still too busy for that right now
> (unless you tell me I'm going to see a night-and-day difference
> regarding this particular issue).

I think it might be more "dusk and day", but I have had very
impressive performance from 9.0.  Haven't tried 9.1.

A

--
Andrew Sullivan
ajs@crankycanuck.ca

On Thursday 06 October 2011 07:00:20 Andrew Sullivan wrote:
> On Thu, Oct 06, 2011 at 11:58:15AM +0200, Vincent de Phily wrote:
> > Fearing that vacuuming might accumulate lateness and hoping to see the
> > system idle every now and then,
>
> Why is your goal to see the system idle every now and then?  It's not
> going to get tired if it keeps working, and if you have a lot of work
> and can spin out that work so that the system always has a little bit
> of work to do, then you use your resources more efficiently.
>
> Normally, one likes to see some idle time because it is evidence of
> "headroom" -- that you have more capacity than you actually need.  If
> that's the reason you want to see the idle times, then surely you
> don't want to tune the system with the goal of causing idleness.  You
> want to tune the system so that the work gets done in as smooth and
> fast a way possible.  So I would aim for maximum throughput (including
> but not exclusively complete table maintenance) and then check whether
> you're getting any idle time.  Off the cuff, though, it sounds to me
> like you need more capacity than you have.

I agree idleness per se is not the goal, and whatever work needs to be done
might as well be spread smoothly over time. Idleness *is* however a measure of
the ressource headroom available, and that headroom was incomfortably small
(for IO) in this case. I was just groping for more performance out of the
system.

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) ?


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.

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 ?
 * Does vacuuming fill the OS's disk cache, and is it an issue if it does ?


Cheers.

--
Vincent de Phily

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@crankycanuck.ca

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.



Thanks for your interesting replies.

--
Vincent de Phily

2011/10/7 Vincent de Phily <vincent.dephily@mobile-devices.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