Date: Wed, 31 Oct 2012 16:05:13 +0100 (CET)
From: Baptiste LHOSTE <blhoste@alaloop.com>
To: pgsql-admin@postgresql.org
Cc: Sylvain CAILLET <scaillet@alaloop.com>
Message-ID: <626919622.7634700.1351695913466.JavaMail.root@alaloop.com>
In-Reply-To: <1442070268.7614953.1351693260184.JavaMail.root@alaloop.com>
Subject: Autoanalyze of the autovacuum daemon ...
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

Hi All,=20

We are using postgreSQL since 2007 (now we use postgreSQL 8.4) and until re=
cently we used to perform deletion of old data then vacuum and analyze task=
s by ourself (every night at 0am CET).
Nevertheless we reached a point where these tasks are taking so much time t=
hat why we decide to perform deletion of old data every hour and let the au=
tovacuum daemon manage vacuum and analyze tasks.

But we have some difficulties to understand how work the trigger of the aut=
ovacuum daemon for autoanalyze tasks.=20

Let me explain our database structure. We have two kind of tables :=20
- 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)=20
- second one on which we insert some new data every five minutes (avg~200 r=
ows) and delete old data about every 1 hour (avg~1000 rows).=20
For complete understanding, we need up-to-date stats for the second one bec=
ause the recurrent deletion might take a long time, (~1mn for less than 100=
0 deleted rows because planer uses seq scan instead of index scan).=20

The autovacuum perform autoanalyze tasks on first kind as soon as the proce=
ss (truncate + copy) is done.=20

But the autoanalyze is not that effective for second kind.

We tried to reduce autovacuum_analyze_threshold (50 =3D> 10) and autovacuum=
_analyze_scale_factor (0.1 =3D> 0.005) for the second kind of tables (ALTER=
 TABLE ... SET PARAMETERS ...) without any conclusive effect.=20

We can not find where is stored the total number of tuples inserted or upda=
ted since the last ANALYZE. Could someone give us the answer ?

We take a look at the relfrozenxid but our tables do not have a big value o=
f relfrozenxid (< 10000000).=20

Most of the time there is no autovacuum analyze query in the pg_stat_activi=
ty, althought we set the autovacuum_naptime to 15s to try to start new anal=
yze task more often.

We do not understand why we can't obtain some improvments with previous cha=
nges. Did we do something wrong ?=20

Thank you all for your kind advices,=20

Regards, Baptiste.

---=20
Baptiste LHOSTE=20
blhoste@alaloop.com=20

ALALOOP S.A.S. - Technopole Izarbel - 64210 Bidart=20
T=C3=A9l=C3=A9phone : +33 (0) 5 59 41 51 10=20
www.alaloop.com=20