Hi All,
We are using postgreSQL since 2007 (now we use postgreSQL 8.4) and until recently we used to perform deletion of old
datathen vacuum and analyze tasks by ourself (every night at 0am CET).
Nevertheless we reached a point where these tasks are taking so much time that why we decide to perform deletion of old
dataevery hour and let the autovacuum daemon manage vacuum and analyze tasks.
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
per5mn 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~1000rows).
For complete understanding, we need up-to-date stats for the second one because the recurrent deletion might take a
longtime, (~1mn for less than 1000 deleted rows 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
secondkind of tables (ALTER TABLE ... SET PARAMETERS ...) without any conclusive effect.
We can not find where is stored the total number of tuples inserted or updated since the last ANALYZE. Could someone
giveus the answer ?
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
to15s 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@alaloop.com
ALALOOP S.A.S. - Technopole Izarbel - 64210 Bidart
Téléphone : +33 (0) 5 59 41 51 10
www.alaloop.com