Re: Displaying accumulated autovacuum cost

Em 17-08-2011 18:04, Greg Smith escreveu:
> Attached is a patch that tracks and displays the accumulated cost when
> autovacuum is running. Code by Noah Misch and myself. I hope this idea
> will bring a formal process to vacuum tuning, which is currently too
> hard to do. I was about to add "without..." to that, but I then realized
> it needs no disclaimer; it's just too hard, period. Vacuum issues are
> enemy #1 at all the terabyte scale customer sites I've been fighting
> with lately.
>
Interesting patch. I drafted a similar idea but didn't have a chance to
publish it. It is a complement to the idea about autovacuum tuning [1]. Hope I
will have time to post something for the next CF. And, of course, I will
review this patch.

> The patch updates the command string just before the workers sleep to
> show how much work they've done so far. And at the end, it adds a few
> new lines to the information written to the logs, when the autovacuum is
> notable enough to be logged at all. The overhead it adds is at most a
> few integer operations per buffer processed and a slower title string
> update once per sleep. It's trivial compared to both the vacuum itself,
> and to the instrumentation's value to sites with vacuum issues.
>
I don't like exposing this information only on title processes. It would be
difficult for client apps (for example, PGAdmin) to track this kind of
information and it is restricted to local access. I'm not objecting to display
this information in process title; I'm just saying that that information
should be exposed in functions (say pg_stat_get_vacuum_[hit|miss|dirty]) too.
I'm not sure about adding this information to incremental counters but that
would be useful to trace a vacuum work pattern.

[1] http://archives.postgresql.org/pgsql-hackers/2011-06/msg00678.php

--
Euler Taveira de Oliveira - Timbira http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento