Re: pg_stat_get_last_vacuum_time(): why non-FULL?

2013/3/28 CR Lender <crlender(at)gmail(dot)com>:
> On 2013-03-28 13:11, Martín Marqués wrote:
>> 2013/3/27 CR Lender <crlender(at)gmail(dot)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