From: =?utf-8?q?C=C3=A9dric_Villemain?= <cedric@2ndquadrant.com>
Reply-To: cedric@2ndquadrant.com
Organization: 2ndQuadrant
To: pgsql-admin@postgresql.org
Subject: Re: Autoanalyze of the autovacuum daemon ...
Date: Mon, 5 Nov 2012 11:32:46 +0100
User-Agent: KMail/1.13.7 (Linux/3.2.0-4-amd64; KDE/4.8.4; x86_64; ; )
Cc: Baptiste LHOSTE <blhoste@alaloop.com>,
	Sylvain CAILLET <scaillet@alaloop.com>
References: <626919622.7634700.1351695913466.JavaMail.root@alaloop.com>
In-Reply-To: <626919622.7634700.1351695913466.JavaMail.root@alaloop.com>
MIME-Version: 1.0
Content-Type: multipart/signed; boundary="nextPart7707499.k2cxXxMWMZ";
	protocol="application/pgp-signature"; micalg=pgp-sha1
Content-Transfer-Encoding: 7bit
Message-Id: <201211051132.50591.cedric@2ndquadrant.com>

--nextPart7707499.k2cxXxMWMZ
Content-Type: Text/Plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable

Hi,

> 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 for less than 1000
> deleted rows because planer uses seq scan instead of index scan).
>=20
> The autovacuum perform autoanalyze tasks on first kind as soon as the
> process (truncate + copy) is done.
>=20
> But the autoanalyze is not that effective for second kind.
>=20
> 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
> updated since the last ANALYZE. Could someone give us the answer ?

in pg_stat_user_tables, not since the last time ANALYZE run, but you have t=
he=20
number of reltuples from pg_class that is used to calculate the ratio.

> We take a look at the relfrozenxid but our tables do not have a big value
> of relfrozenxid (< 10000000).
>=20
> 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.
>=20
> We do not understand why we can't obtain some improvments with previous
> changes. Did we do something wrong ?

Everything is relative to the size of the table, what is the content of=20
pg_class for the second kind of tables ? (relpages/reltuples)

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

--nextPart7707499.k2cxXxMWMZ
Content-Type: application/pgp-signature; name=signature.asc 
Content-Description: This is a digitally signed message part.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.12 (GNU/Linux)

iQEcBAABAgAGBQJQl5XOAAoJENsgH0yzVBebg/YH/2C7idRhK5fJ30PjS1tz2hgo
22T0PieixDlwQeUGvQ5G3erCh96k5ZJ9rKxvx11Q4q9RNFZbeqqgXCKvFH/W+4j1
erQnnJUjqXJNLRcpeRzHPKXJR9roO9ZlcJp39ZNXs4Hy5jCKb1dBfQqqdKNAZWAa
gHU8kPp4N3aXxsze/bUz6nPDfZm2gY4++lz5xTL+qTpPet1nTbv+S9VSQ0hUOoxL
FTrZ8IWir+nUu7kFn3AxVO3apX9IbkZ5fu5XY5Ficn4PVnr+peP0h0QgO1tVLtuF
Y9fXA4Kbl3Ym1CWauvFpWAbs5XNsPOY/VIc8AmD9t6EcuXpf5lFLCXgRh0d6rio=
=cWSq
-----END PGP SIGNATURE-----

--nextPart7707499.k2cxXxMWMZ--