Autoanalyze of the autovacuum daemon ...

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,

Regards, Baptiste.

---
Baptiste LHOSTE
blhoste(at)alaloop(dot)com

ALALOOP S.A.S. - Technopole Izarbel - 64210 Bidart
Téléphone : +33 (0) 5 59 41 51 10
www.alaloop.com