Re: More vacuum stats

On Sun, Aug 22, 2010 at 18:17, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> On Sun, Aug 22, 2010 at 17:29, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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/