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,
Suporte24x7 e Treinamento