Thread: 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
tasksby ourself. Nevertheless we reached a point where these tasks are taking so much time that why we decide to use
theautovacuum 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
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 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 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

Hello

this list is for PostgreSQL development purpose, better try at psql-general or
-admin.

> But the autoanalyze is not that effective for second kind.

if it is not running at all it is very probably because of threshold +
scale_factor and the size of the second kind of tables.

> 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.

it will allow to check more often, not to start earlier.

> We do not understand why we can't obtain some improvments with previous
> changes. Did we do something wrong ?

Can you share the size of the tables ? (size , number of tuples, dead/live,
...) ? (please create a new thread in one of the other mailling-list)

--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation

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
analyzetasks by ourself. Nevertheless we reached a point where these tasks are taking so much time that why we decide
touse 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
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~1000 rows).
 
> For complete understanding, we need up-to-date stats for the second one because the recurrent deletion might take a
longtime, (~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
secondkind 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_naptimeto 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