Re: pg_stat_get_last_vacuum_time(): why non-FULL?

2013/3/28 CR Lender <crlender@gmail.com>:
> On 2013-03-28 13:11, Martín Marqués wrote:
>> 2013/3/27 CR Lender <crlender@gmail.com>:
>>> In this case I was only trying to make sense of an existing database
>>> (8.3). The statistics in pg_stats were way off for some tables, so I
>>> wanted to see if (auto)vacuum and (auto)analyze were being run.
>>> pg_stat_all_tables() showed last_autoanalyze at >400 days for some of
>>> the larger tables. There used to be a weekly cron job with VACUUM FULL
>>> ANALYZE, and I was trying to find out if that cron job was still active.
>>
>> What's your autovacuum configuration? autovacuum_vacuum_threshold?
>> autovacuum_analyze_threshold? autovacuum_vacuum_scale_factor?
>> autovacuum_analyze_scale_factor?
>
>  autovacuum                      | on
>  autovacuum_analyze_scale_factor | 0.1
>  autovacuum_analyze_threshold    | 50
>  autovacuum_freeze_max_age       | 200000000
>  autovacuum_max_workers          | 3
>  autovacuum_naptime              | 1min
>  autovacuum_vacuum_cost_delay    | 20ms
>  autovacuum_vacuum_cost_limit    | -1
>  autovacuum_vacuum_scale_factor  | 0.2
>  autovacuum_vacuum_threshold     | 50
>
> The database is running on PostgreSQL 8.3.6.

What you have to look at is autovacuum_analyze_scale_factor which in
your case is 10% of the total tuples of the relation. So when 50
tuples (the threshold) over the 10% of the total tuples are
analyzable, autovacuum analyzes the relation.

Same thing with vacuum, but with 20% in that case.

If you want autovacuum to really clean and analize more, you will have
to lower autovacuum_analyze_scale_factor and
autovacuum_vacuum_scale_factor.

--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador