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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers