Re: Autoanalyze of the autovacuum daemon ...

On 01/11/12 00:25, Baptiste LHOSTE wrote:
> Hi All,
>
> We are using postgreSQL since 2007 (now we use postgreSQL 8.4) and until recently we used to perform vacuum and analyze tasks by ourself. Nevertheless we reached a point where these tasks are taking so much time that why we decide to use the autovacuum daemon.
>
> But we have some difficulties to understand how work the trigger of the autovacuum daemon for autoanalyze tasks.
>
> Let me explain our database structure. We have two kind of tables :
> - first one on which we perform a complete truncate and a copy to fill each (partitioned tables - 288 partitions, one per 5mn re-used every day)
> - second one on which we insert some new data every five minutes (avg~200 rows) and delete old data about every 1 hour (avg~1000 rows).
> For complete understanding, we need up-to-date stats for the second one because the recurrent deletion might take a long time, (~1mn because planer uses seq scan instead of index scan).
>
> The autovacuum perform autoanalyze tasks on first kind as soon as the process (truncate + copy) is done.
>
> But the autoanalyze is not that effective for second kind.
>
> We tried to reduce autovacuum_analyze_threshold (50 => 10) and autovacuum_analyze_scale_factor (0.1 => 0.005) for the second kind of tables (ALTER TABLE ... SET PARAMETERS ...) without any conclusive effect.
>
> We take a look at the relfrozenxid but our tables do not have a big value of relfrozenxid (< 10000000).
>
> Most of the time there is no autovacuum analyze query in the pg_stat_activity, althought we set the autovacuum_naptime to 15s to try to start new analyze task more often.
>
> We do not understand why we can't obtain some improvments with previous changes. Did we do something wrong ?
>
> Thank you all for your kind advices,
>

I wonder if you might need to amend the corresponding autoacuum-vacuum*
parameters too. In my experience these are at least or more important
than the pure analyze ones (i.e all well and good getting accurate
planner stats for a query - but even better if it does not have to work
around lots of dead tuples in the estimates)...

Regards

Mark