Re: tuning autovacuum

On Jun 9, 2011 12:01 AM, "Euler Taveira de Oliveira" <euler(at)timbira(dot)com>
wrote:
>
> Hi,
>
> There are some releases that autovacuum was enabled by default and, up to
now there is an easy way to estimate the number of autovacuum workers. I
tune it observing if the number of slots are saturated for a period of time.
I'm having a hard time trying to do this. I want to add a LOG message such
as
>
> LOG: maximum number of autovacuum workers reached
> HINT: Consider increasing autovacuum_max_workers (currently 5).

That would be very useful.

> And also a view (say pg_stat_autovacuum) to expose some autovacuum
information such as (i) number of autovacuum workers (ii) number of tables
that needs analyze/vacuum and are scheduled to (iii) number of

Part of that is on my personal todo already, so I'd be happy to review that
:)

> autovacuum count (iv) number of autoanalyze count. While I am in this
topic, it would be nice to expose the analyze/vacuum count and threshold per
table. This information should go to pg_stat_*_tables but it already has too
much fields. Maybe it is time to split autovacuum information into another
statistic view?

That is configuration information and not statistics, so IMHO it does not
belong in pg_stat_*.

And if relation parameters are to be exposed more than they are now it
should be done for all, not just autovacuum.

/Magnus