Skip site navigation (1) Skip section navigation (2)

Re: Autoanalyze of the autovacuum daemon ...

From: Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Autoanalyze of the autovacuum daemon ...
Date: 2012-11-01 10:08:09
Message-ID: 50924A09.3030009@catalyst.net.nz (view raw or flat)
Thread:
Lists: pgsql-hackers
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


In response to

pgsql-hackers by date

Next:From: Kevin GrittnerDate: 2012-11-01 13:45:18
Subject: Re: Estimation of HashJoin Cost
Previous:From: Qi HuangDate: 2012-11-01 08:46:10
Subject: Estimation of HashJoin Cost

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group