Thread

1. More vacuum stats

   Magnus Hagander <magnus@hagander.net> — 2010-08-22T11:36:51Z

   I noticed that we were already tracking the information about when an
   autovacuum worker was last started in a database, but this information
   was not exposed. The attached patch puts this column in
   pg_stat_database.
   
   Was there any particular reason why this wasn't exposed before that
   I've missed, making this a bad addition? :-)
   
   Oh, and this time, I *have* included updates to the regression tests.
   
   -- 
    Magnus Hagander
    Me: http://www.hagander.net/
    Work: http://www.redpill-linpro.com/
   

2. Re: More vacuum stats

   Euler Taveira de Oliveira <euler@timbira.com> — 2010-08-22T14:22:13Z

   Magnus Hagander escreveu:
   > Was there any particular reason why this wasn't exposed before that
   > I've missed, making this a bad addition? :-)
   > 
   Not that I know of. Good catch. ;)
   
   
   -- 
     Euler Taveira de Oliveira
     http://www.timbira.com/
   
   

3. Re: More vacuum stats

   Tom Lane <tgl@sss.pgh.pa.us> — 2010-08-22T15:29:33Z

   Magnus Hagander <magnus@hagander.net> writes:
   > I noticed that we were already tracking the information about when an
   > autovacuum worker was last started in a database, but this information
   > was not exposed. The attached patch puts this column in
   > pg_stat_database.
   
   > Was there any particular reason why this wasn't exposed before that
   > I've missed, making this a bad addition? :-)
   
   I think that's an implementation detail.  If we expose it then we'll
   be forced to track it forevermore, regardless of whether the AV launcher
   actually needs it in the future.  (In particular, the assumption that
   this is tracked per-database and not per-something-else seems like an
   artifact of the current AV launching algorithm.)
   
   So I'd like to see a positive argument why this is important for users
   to know, rather than merely "we should expose every conceivable detail
   by default".  Why wouldn't a user care more about last AV time for a
   specific table, which we already do expose?
   
   			regards, tom lane
   
   

4. Re: More vacuum stats

   Magnus Hagander <magnus@hagander.net> — 2010-08-22T16:01:48Z

   On Sun, Aug 22, 2010 at 17:29, Tom Lane <tgl@sss.pgh.pa.us> wrote:
   > Magnus Hagander <magnus@hagander.net> writes:
   >> I noticed that we were already tracking the information about when an
   >> autovacuum worker was last started in a database, but this information
   >> was not exposed. The attached patch puts this column in
   >> pg_stat_database.
   >
   >> Was there any particular reason why this wasn't exposed before that
   >> I've missed, making this a bad addition? :-)
   >
   > I think that's an implementation detail.  If we expose it then we'll
   > be forced to track it forevermore, regardless of whether the AV launcher
   > actually needs it in the future.  (In particular, the assumption that
   > this is tracked per-database and not per-something-else seems like an
   > artifact of the current AV launching algorithm.)
   
   That's a good point. OTOH, if we removed the feature, it seems it
   would be reasonable to remove the column from the statistics view as
   well. That *could* happen in other stats views as well.
   
   > So I'd like to see a positive argument why this is important for users
   > to know, rather than merely "we should expose every conceivable detail
   > by default".  Why wouldn't a user care more about last AV time for a
   > specific table, which we already do expose?
   
   You need to connect to every database to do that. If you have many
   databases, that's a lot of overhead particularly if you're doing tihs
   for regular monitoring. Plus, those views will only track when
   autovacuum actually *did* something.
   
   Being able to see that autovacuum hasn't even touched a database for
   too long would be an early-indicator that you have some issues with
   it.
   
   -- 
    Magnus Hagander
    Me: http://www.hagander.net/
    Work: http://www.redpill-linpro.com/
   
   

5. Re: More vacuum stats

   Tom Lane <tgl@sss.pgh.pa.us> — 2010-08-22T16:17:31Z

   Magnus Hagander <magnus@hagander.net> writes:
   > On Sun, Aug 22, 2010 at 17:29, Tom Lane <tgl@sss.pgh.pa.us> wrote:
   >> So I'd like to see a positive argument why this is important for users
   >> to know, rather than merely "we should expose every conceivable detail
   >> by default". Why wouldn't a user care more about last AV time for a
   >> specific table, which we already do expose?
   
   > You need to connect to every database to do that. If you have many
   > databases, that's a lot of overhead particularly if you're doing tihs
   > for regular monitoring. Plus, those views will only track when
   > autovacuum actually *did* something.
   
   Well, the last-launch-time doesn't prove that autovacuum actually *did*
   something ;-).
   
   > Being able to see that autovacuum hasn't even touched a database for
   > too long would be an early-indicator that you have some issues with
   > it.
   
   With the current AV launch algorithm, unless you have very serious
   system-wide issues there will be a worker launched into each database
   approximately every autovacuum_naptime seconds.  AFAICS this does not
   tell you anything interesting about whether AV is getting its work done.
   
   			regards, tom lane
   
   

6. Re: More vacuum stats

   Magnus Hagander <magnus@hagander.net> — 2010-08-23T08:55:06Z

   On Sun, Aug 22, 2010 at 18:17, Tom Lane <tgl@sss.pgh.pa.us> wrote:
   > Magnus Hagander <magnus@hagander.net> writes:
   >> On Sun, Aug 22, 2010 at 17:29, Tom Lane <tgl@sss.pgh.pa.us> wrote:
   >>> So I'd like to see a positive argument why this is important for users
   >>> to know, rather than merely "we should expose every conceivable detail
   >>> by default".  Why wouldn't a user care more about last AV time for a
   >>> specific table, which we already do expose?
   >
   >> You need to connect to every database to do that. If you have many
   >> databases, that's a lot of overhead particularly if you're doing tihs
   >> for regular monitoring. Plus, those views will only track when
   >> autovacuum actually *did* something.
   >
   > Well, the last-launch-time doesn't prove that autovacuum actually *did*
   > something ;-).
   
   Well, it would tell you it considered doing something ;)
   
   
   >> Being able to see that autovacuum hasn't even touched a database for
   >> too long would be an early-indicator that you have some issues with
   >> it.
   >
   > With the current AV launch algorithm, unless you have very serious
   > system-wide issues there will be a worker launched into each database
   > approximately every autovacuum_naptime seconds.  AFAICS this does not
   > tell you anything interesting about whether AV is getting its work done.
   
   Well, if you have all your autovacuum workers tied up with vacuuming
   large tables, then it wouldn't AFAIK. I'm not sure if that counts as
   your "very serious system-wide issues", but it's certainly a case
   that's interesting for the admin to know about.
   
   But thinking more about that, you ca nfigure that out with a SELECT
   count(*) FROM pg_stat_activity WHERE current_query LIKE 'autovacuum:
   %' if I'm not mistaken.
   
   It can also be used to find out if the launcher is somehoiw stuck, but
   that would be a bug and we don't generally put counters in the stats
   views to expose possible bugs, only to track interesting statistics.
   
   -- 
    Magnus Hagander
    Me: http://www.hagander.net/
    Work: http://www.redpill-linpro.com/
   
   

7. Re: More vacuum stats

   Tom Lane <tgl@sss.pgh.pa.us> — 2010-08-23T14:04:14Z

   Magnus Hagander <magnus@hagander.net> writes:
   > On Sun, Aug 22, 2010 at 18:17, Tom Lane <tgl@sss.pgh.pa.us> wrote:
   >> With the current AV launch algorithm, unless you have very serious
   >> system-wide issues there will be a worker launched into each database
   >> approximately every autovacuum_naptime seconds. AFAICS this does not
   >> tell you anything interesting about whether AV is getting its work done.
   
   > Well, if you have all your autovacuum workers tied up with vacuuming
   > large tables, then it wouldn't AFAIK. I'm not sure if that counts as
   > your "very serious system-wide issues", but it's certainly a case
   > that's interesting for the admin to know about.
   
   > But thinking more about that, you ca nfigure that out with a SELECT
   > count(*) FROM pg_stat_activity WHERE current_query LIKE 'autovacuum:
   > %' if I'm not mistaken.
   
   > It can also be used to find out if the launcher is somehoiw stuck, but
   > that would be a bug and we don't generally put counters in the stats
   > views to expose possible bugs, only to track interesting statistics.
   
   Yeah.  Given the current worker-launch algorithm, these times just don't
   strike me as all that interesting in practice.  If we were to change to
   a different algorithm, it's possible that it'd become worthwhile to
   expose them --- but it's equally possible that some other data would be
   useful instead.  So my feeling remains that we should leave well enough
   alone.
   
   			regards, tom lane
   
   

8. Re: More vacuum stats

   Greg Smith <greg@2ndquadrant.com> — 2010-08-23T14:28:28Z

   Tom Lane wrote:
   > So I'd like to see a positive argument why this is important for users
   > to know, rather than merely "we should expose every conceivable detail
   > by default".  Why wouldn't a user care more about last AV time for a
   > specific table, which we already do expose?
   >   
   
   What I actually want here is for the time that the last table autovacuum 
   started, adding to the finish time currently exposed by 
   pg_stat_user_tables.  "How long did the last {auto}vacuum on <x> take to 
   run?" is a FAQ on busy systems here.  If I could compute that from a 
   pair of columns, it's a major step toward answering even more 
   interesting questions like "how does this set of cost delay parameters 
   turn into an approximate MB/s worth of processing rate on my tables?".  
   This is too important of a difficult tuning exercise to leave to log 
   scraping forever.
   
   I'd rather have that and look at for "SELECT max(last_autovacuum_start) 
   FROM pg_stat_user_tables" to diagnose the sort of problems this patch 
   seems to aim at helping.
   
   -- 
   Greg Smith  2ndQuadrant US  Baltimore, MD
   PostgreSQL Training, Services and Support
   greg@2ndQuadrant.com   www.2ndQuadrant.us
   
   
   

9. Re: More vacuum stats

   Magnus Hagander <magnus@hagander.net> — 2010-08-23T14:32:21Z

   On Mon, Aug 23, 2010 at 16:28, Greg Smith <greg@2ndquadrant.com> wrote:
   > Tom Lane wrote:
   >>
   >> So I'd like to see a positive argument why this is important for users
   >> to know, rather than merely "we should expose every conceivable detail
   >> by default".  Why wouldn't a user care more about last AV time for a
   >> specific table, which we already do expose?
   >>
   >
   > What I actually want here is for the time that the last table autovacuum
   > started, adding to the finish time currently exposed by pg_stat_user_tables.
   >  "How long did the last {auto}vacuum on <x> take to run?" is a FAQ on busy
   > systems here.  If I could compute that from a pair of columns, it's a major
   > step toward answering even more interesting questions like "how does this
   > set of cost delay parameters turn into an approximate MB/s worth of
   > processing rate on my tables?".  This is too important of a difficult tuning
   > exercise to leave to log scraping forever.
   
   Now, that would be quite useful. That'd require another stats message,
   since we don't send anything on autovacuum start, but I don't think
   the overhead of that is anything we need to worry about - in
   comparison to an actual vacuum...
   
   Do we want that for both vacuum and autovacuum? vacuum and analyze?
   
   We could also store last_autovacuum_vacuum_duration - is that better
   or worse than start and end time?
   
   
   > I'd rather have that and look at for "SELECT max(last_autovacuum_start) FROM
   > pg_stat_user_tables" to diagnose the sort of problems this patch seems to
   > aim at helping.
   
   Agreed. Consider this patch withdrawn.
   
   -- 
    Magnus Hagander
    Me: http://www.hagander.net/
    Work: http://www.redpill-linpro.com/
   
   

10. Re: More vacuum stats

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-08-23T14:38:44Z

    Magnus Hagander <magnus@hagander.net> writes:
    > On Mon, Aug 23, 2010 at 16:28, Greg Smith <greg@2ndquadrant.com> wrote:
    >> What I actually want here is for the time that the last table autovacuum
    >> started, adding to the finish time currently exposed by pg_stat_user_tables.
    
    > Now, that would be quite useful. That'd require another stats message,
    > since we don't send anything on autovacuum start, but I don't think
    > the overhead of that is anything we need to worry about - in
    > comparison to an actual vacuum...
    
    No, you wouldn't really need an extra message, you could just send both
    start and finish times in the completion message.  I'm not sure that
    having last start time update before last end time would be a good idea
    anyway.
    
    But in any case it's true that an extra message wouldn't be a
    significant cost.  What I'd be more concerned about is the stats table
    bloat from adding yet another per-table field.  That could be a lot of
    space on an installation with lots of tables.
    
    > We could also store last_autovacuum_vacuum_duration - is that better
    > or worse than start and end time?
    
    No, I think you want to know the actual time not only the duration.
    
    			regards, tom lane
    
    

11. Re: More vacuum stats

    Magnus Hagander <magnus@hagander.net> — 2010-08-23T14:40:46Z

    On Mon, Aug 23, 2010 at 16:38, Tom Lane <tgl@sss.pgh.pa.us> wrote:
    > Magnus Hagander <magnus@hagander.net> writes:
    >> On Mon, Aug 23, 2010 at 16:28, Greg Smith <greg@2ndquadrant.com> wrote:
    >>> What I actually want here is for the time that the last table autovacuum
    >>> started, adding to the finish time currently exposed by pg_stat_user_tables.
    >
    >> Now, that would be quite useful. That'd require another stats message,
    >> since we don't send anything on autovacuum start, but I don't think
    >> the overhead of that is anything we need to worry about - in
    >> comparison to an actual vacuum...
    >
    > No, you wouldn't really need an extra message, you could just send both
    > start and finish times in the completion message.  I'm not sure that
    > having last start time update before last end time would be a good idea
    > anyway.
    
    Hmm, good point. We'd just need an extra field in that message.
    
    
    > But in any case it's true that an extra message wouldn't be a
    > significant cost.  What I'd be more concerned about is the stats table
    > bloat from adding yet another per-table field.  That could be a lot of
    > space on an installation with lots of tables.
    >
    >> We could also store last_autovacuum_vacuum_duration - is that better
    >> or worse than start and end time?
    >
    > No, I think you want to know the actual time not only the duration.
    
    Well, you could calculate one from the other - especially if one takes
    less size, per your comment above.
    
    
    -- 
     Magnus Hagander
     Me: http://www.hagander.net/
     Work: http://www.redpill-linpro.com/
    
    

12. Re: More vacuum stats

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-08-23T14:46:15Z

    Magnus Hagander <magnus@hagander.net> writes:
    > On Mon, Aug 23, 2010 at 16:38, Tom Lane <tgl@sss.pgh.pa.us> wrote:
    >> Magnus Hagander <magnus@hagander.net> writes:
    >>> We could also store last_autovacuum_vacuum_duration - is that better
    >>> or worse than start and end time?
    >> 
    >> No, I think you want to know the actual time not only the duration.
    
    > Well, you could calculate one from the other - especially if one takes
    > less size, per your comment above.
    
    With alignment considerations, adding a field is going to cost 8 bytes;
    whether it's a timestamp or a duration isn't going to matter.  I'd be
    inclined to store the timestamp, it just seems more like the base datum.
    
    			regards, tom lane