Re: [HACKERS] More vacuum stats

2010-08-23 Thread Magnus Hagander
On Sun, Aug 22, 2010 at 18:17, Tom Lane t...@sss.pgh.pa.us wrote:
 Magnus Hagander mag...@hagander.net writes:
 On Sun, Aug 22, 2010 at 17:29, Tom Lane t...@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/

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] More vacuum stats

2010-08-23 Thread Tom Lane
Magnus Hagander mag...@hagander.net writes:
 On Sun, Aug 22, 2010 at 18:17, Tom Lane t...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] More vacuum stats

2010-08-23 Thread Greg Smith

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
g...@2ndquadrant.com   www.2ndQuadrant.us


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] More vacuum stats

2010-08-23 Thread Magnus Hagander
On Mon, Aug 23, 2010 at 16:28, Greg Smith g...@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/

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] More vacuum stats

2010-08-23 Thread Tom Lane
Magnus Hagander mag...@hagander.net writes:
 On Mon, Aug 23, 2010 at 16:28, Greg Smith g...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] More vacuum stats

2010-08-23 Thread Magnus Hagander
On Mon, Aug 23, 2010 at 16:38, Tom Lane t...@sss.pgh.pa.us wrote:
 Magnus Hagander mag...@hagander.net writes:
 On Mon, Aug 23, 2010 at 16:28, Greg Smith g...@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/

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] More vacuum stats

2010-08-23 Thread Tom Lane
Magnus Hagander mag...@hagander.net writes:
 On Mon, Aug 23, 2010 at 16:38, Tom Lane t...@sss.pgh.pa.us wrote:
 Magnus Hagander mag...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] More vacuum stats

2010-08-22 Thread Magnus Hagander
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/


vac_db.patch
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] More vacuum stats

2010-08-22 Thread Euler Taveira de Oliveira
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/

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] More vacuum stats

2010-08-22 Thread Tom Lane
Magnus Hagander mag...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] More vacuum stats

2010-08-22 Thread Magnus Hagander
On Sun, Aug 22, 2010 at 17:29, Tom Lane t...@sss.pgh.pa.us wrote:
 Magnus Hagander mag...@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/

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] More vacuum stats

2010-08-22 Thread Tom Lane
Magnus Hagander mag...@hagander.net writes:
 On Sun, Aug 22, 2010 at 17:29, Tom Lane t...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers